SQL Status: HY000, Error code: 1000 on SELECT query

[Marco3366]

Edit: Split from [Solved] Syntax Error in SQL Expression? because that topic is solved so you need your own. If you had the same problem you could use the same solution. Please do not post in another's topic unless you are helping to solve their problem for them. -- MrProgrammer, forum moderator

Good afternoon/ morning everyone, recently I have encountered an issue regarding queries in Open office 4 for mac.

When I try creating a query (with wizard) with my main table I get this error:
SQL Status: HY000
Error code: 1000

SELECT "Main"."ID" AS "ID", "Main"."Name" AS "Name", "Main"."Section" AS "Section", "Main"."Nationality " AS "Nationality ", "Main"."Favorite Food" AS "Favorite Food", "Main"."Choice of Canteen food" AS "Choice of Canteen food", "Main"."Availability of choice" AS "Availability of choice", "Main"."Allergies" AS "Allergies" FROM "Main" "Main" WHERE (Favorite Food LIKE 'Pizza')

Does someone know why this is happening? and how I can fix this issue?

I am not an expert on Open office

Thank you very much


Have a great day

[Sliderule]

This is your query as displayed it in the post above, I added line breaks to make it easier to read / diagnosis:

Code: Select all

SELECT 
   "Main"."ID" AS "ID",
   "Main"."Name" AS "Name",
   "Main"."Section" AS "Section",
   "Main"."Nationality " AS "Nationality ",
   "Main"."Favorite Food" AS "Favorite Food",
   "Main"."Choice of Canteen food" AS "Choice of Canteen food",
   "Main"."Availability of choice" AS "Availability of choice",
   "Main"."Allergies" AS "Allergies"
FROM "Main" "Main"
WHERE (Favorite Food LIKE 'Pizza')

In the WHERE clause, you have NOT placed Favorite Food within double quotes, which is required since you have elected to use Mixed Case rather than UPPER CASE to indicate the column name. Furthermore, you have elected to use a SPACE as a part of the column name rather than something like an ( underscore ) _ such as FAVORITE_FOOD.

Therefore, change WHERE (Favorite Food LIKE 'Pizza') to WHERE ("Favorite Food" LIKE 'Pizza') .

Additionally, when you wish to use a LIKE clause, means you want the database backend to find something in the column, BUT you are not using any special characters to tell the database you want it to START with 'Pizza', end with 'Pizza', or something else. Therefore, you on the Criterion line you could have simply entered Pizza withOUT a LIKE clause.

You could try, any of the following, depending in exactly what you want . . . after stating your Base document:

1. Click on the Queries icon on the left
2. Under Tasks
3. Click on the Create Query in SQL View...
4. Copy and paste any of the Queries below, depending on what you want to try

1. This indicates you only want to find the literal Pizza
   

   Code: Select all

   SELECT 
      "Main"."ID" AS "ID",
      "Main"."Name" AS "Name",
      "Main"."Section" AS "Section",
      "Main"."Nationality " AS "Nationality ",
      "Main"."Favorite Food" AS "Favorite Food",
      "Main"."Choice of Canteen food" AS "Choice of Canteen food",
      "Main"."Availability of choice" AS "Availability of choice",
      "Main"."Allergies" AS "Allergies"
   FROM "Main" "Main"
   WHERE "Main"."Favorite Food" = 'Pizza' 
   

2. This indicates it starts with the literal Pizza
   

   Code: Select all

   SELECT 
      "Main"."ID" AS "ID",
      "Main"."Name" AS "Name",
      "Main"."Section" AS "Section",
      "Main"."Nationality " AS "Nationality ",
      "Main"."Favorite Food" AS "Favorite Food",
      "Main"."Choice of Canteen food" AS "Choice of Canteen food",
      "Main"."Availability of choice" AS "Availability of choice",
      "Main"."Allergies" AS "Allergies"
   FROM "Main" "Main"
   WHERE ("Main"."Favorite Food" LIKE 'Pizza' || '%') 
   

3. This indicates it ends with the literal Pizza
   

   Code: Select all

   SELECT 
      "Main"."ID" AS "ID",
      "Main"."Name" AS "Name",
      "Main"."Section" AS "Section",
      "Main"."Nationality " AS "Nationality ",
      "Main"."Favorite Food" AS "Favorite Food",
      "Main"."Choice of Canteen food" AS "Choice of Canteen food",
      "Main"."Availability of choice" AS "Availability of choice",
      "Main"."Allergies" AS "Allergies"
   FROM "Main" "Main"
   WHERE ("Main"."Favorite Food" LIKE  '%' || 'Pizza' )  
   

4. This indicates the literal Pizza may be any place, beginning, middle, end
   

   Code: Select all

   SELECT 
      "Main"."ID" AS "ID",
      "Main"."Name" AS "Name",
      "Main"."Section" AS "Section",
      "Main"."Nationality " AS "Nationality ",
      "Main"."Favorite Food" AS "Favorite Food",
      "Main"."Choice of Canteen food" AS "Choice of Canteen food",
      "Main"."Availability of choice" AS "Availability of choice",
      "Main"."Allergies" AS "Allergies"
   FROM "Main" "Main"
   WHERE ("Main"."Favorite Food" LIKE  '%' || 'Pizza' || '%' )
   

5. This indicates search may be any place, beginning, middle, end AND Prompt for desired search string
   

   Code: Select all

   SELECT 
      "Main"."ID" AS "ID",
      "Main"."Name" AS "Name",
      "Main"."Section" AS "Section",
      "Main"."Nationality " AS "Nationality ",
      "Main"."Favorite Food" AS "Favorite Food",
      "Main"."Choice of Canteen food" AS "Choice of Canteen food",
      "Main"."Availability of choice" AS "Availability of choice",
      "Main"."Allergies" AS "Allergies"
   FROM "Main" "Main"
   WHERE ("Main"."Favorite Food" LIKE  '%' || :Enter_Search_String || '%' )
   

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[Marco3366]

Good morning/afternoon.

Thank you very much for your response, I didn't really understand where I should be changing the different aspects of the query you highlighted in your previous post. Should I change them in the main table or... Also when I create a query with the wizard and include all the different fields they always come up with Main.ID or Main.Section (Main is the name of my main form).

I need to create a query from my main table about people that have pizza as their favorite food. On the search conditions should I select match any of the following or match all of the following?


Thank you for reading

bye

[Sliderule]

Your Original Post to this forum says:
When I try creating a query (with wizard) with my main table I get this error:
SQL Status: HY000
Error code: 1000

You responded, after 18 days, you said:

Good morning/afternoon.

Thank you very much for your response, I didn't really understand where I should be changing the different aspects of the query you highlighted in your previous post

I responded, assuming you are using HSQL as your database back-end, on where the error was in your Query. You can write or correct the query, depending exactly what you want a Query to respond . . . from the four alternatives I gave you.

Additionally, I explicitly told you how to try each Query I wrote for you:

You could try, any of the following, depending in exactly what you want . . . after starting your Base document:

I believe what you should do next, is exactly as written in my response, follow those instructions, or, perhaps someone else can help you here.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.