SQL utilization for connection to Database

Question

question

Walker huang asked Jul 6, '22 Walker huang commented Jul 7, '22

SQL utilization for connection to Database

It already succeed to connect to Database(Microsoft Access).but as the below picture showing, it can not read data from database if using HAVING clause of SQL. On the contrary, it would make it if not using HAVING clause. how to address this problem? thank!

BTW, encounter the same issues if using ORDER BY clause.

Software Version:

FlexSim 22.1.2

sql database

image20220706085421.png (26.4 KiB)

· 2

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Jason Lightfoot ♦♦ commented · Jul 06, 2022 at 01:27 AM

Does the same SQL statement work in an Access Query?

0 ·

Walker huang Jason Lightfoot ♦♦ commented · Jul 06, 2022 at 03:47 PM

Hi, so far not validate it in Access Query

0 ·

Answer 1 · 2022-07-06T17:11:41Z

Jordan Johnson answered Jul 6, '22 Walker huang commented Jul 7, '22

I think this is something specific to this combination of database file and query. I did an internet search for "odbc error number 07002" and found many different applications with the same issue. In each case, the resolution was to correct something about the query.

So, here are some things I would try:

Double check spelling/capitalization of all column/table names
Wrap all identifiers (column names, table names, aliases) in square brackets
Make sure you only use aliases, if you have declared them. For example, I'd try changing your GROUP BY statement to GROUP BY Item
Try the query in Access, to see if it is valid there, as @Jason Lightfoot suggests.

You could also try using a nested query instead of a HAVING clause, if ODBC supports nested queries:

SELECT Item, Max_SPQ FROM (
  SELECT [FLEX PART NO] AS Item, MAX([SPQ]) AS Max_SPQ FROM SPQ
  GROUP BY Item
) WHERE Max_SPQ > 1

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Walker huang commented · Jul 07, 2022 at 12:59 AM

thank you very much Jordan. your answer in great, but need to do a little modification as the below picture, then it will work.

0 ·

1657155443061.png (21.7 KiB)

question