OraFAQ Forum: SQL & PL/SQL » problem with sql passthru syntax - get "FROM keyword not found where expected (4 merged)

Home » SQL & PL/SQL » SQL & PL/SQL » problem with sql passthru syntax - get "FROM keyword not found where expected (4 merged) (MS Access query passthru to Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem with sql passthru syntax - get "FROM keyword not found where expected (4 merged) [message #689426]

Fri, 22 December 2023 22:07

Bob Alston
Messages: 6
Registered: September 2022

Junior Member

Trying to convert MS Access 16 sql syntax to proper pass thru sql syntax.
I found that my linked tables in Access which connect to the Oracle DB have special prefixes which are not part of the Oracle table names. I tested that with a pass thru of a simple Select request with one field for one table.
I know about double quotes vs single quotes and "*" vs "%".
Below is my code. Hopefully the issue will pop out to an experienced writer of sql syntax for Oracle:

SELECT CLIENTS.SHISID
,' ' AS [--CLient Ad Hoc--]
,CLIENTS_AD_HOC.TYPE_OF_RECORD
,CLIENTS_AD_HOC.CLIENT_LAST_NAME
,CLIENTS_AD_HOC.CLIENT_FIRST_NAME
,CLIENTS_AD_HOC.CLIENT_DOB
,CLIENTS_AD_HOC.CLIENT_ZIP
,CLIENTS_AD_HOC.CLIENT_CITY_COUNTY
,CLIENTS_AD_HOC.CLIENT_ID
,CLIENTS_AD_HOC.CLIENT_GENDER
,CLIENTS_AD_HOC.CLIENT_RACE
,CLIENTS_AD_HOC.CLIENT_ETHNICITY
,CLIENTS_AD_HOC.LEGAL_STATUS
,CLIENTS_AD_HOC.MARITAL_STATUS
,CLIENTS_AD_HOC.EMPLOYMENT_STATUS
,CLIENTS_AD_HOC.PREGNANTSTATUS
,CLIENTS_AD_HOC.FEMALEWITHDEPENDENT
,CLIENTS_AD_HOC.STUDENT_HOME_SET
,CLIENTS_AD_HOC.DISCHARGE_DATE_DEMOG
,CLIENTS_AD_HOC.INTAKE_DATE_DEMOG
,' ' AS [--Client Program Data--]
,CLIENT_PROGRAM_DATA.PROGRAM
,CLIENT_PROGRAM_DATA.DISCHARGE_DATE
,CLIENT_PROGRAM_DATA.INTAKE_DATE
,CLIENT_PROGRAM_DATA.DISCHARGE_REFERRAL_TYPE
,CLIENT_PROGRAM_DATA.LEVELOFCARE
,CLIENT_PROGRAM_DATA.LOCATION_NAME
,CLIENT_PROGRAM_DATA.LEGALSTATUSADMIT
,CLIENT_PROGRAM_DATA.ANTICIPATED_DISCHARGE_DATE
,CLIENT_PROGRAM_DATA.LEAD_CLINICIAN_NAME
,CLIENT_PROGRAM_DATA.DISCHARGE_REASON
,CLIENT_PROGRAM_DATA.DISCHARGE_REASON_PROGRAM
,CLIENT_PROGRAM_DATA.PROGRAM_PRIMARY_PAYER
,'' AS [--Clients--]
,CLIENTS.CURRENT_JURISDICTION
,CLIENTS.FAMINCOME
,CLIENTS.PHYS_NAME
,' ' AS [--Client Program--]
,CLIENT_PROGRAM_DATA.PROGRAM_PRIMARY_PAYER
,' ' AS [--Client Custom Data--]
,CLIENT_CUSTOM_DATA.FIELD_NAME
,CLIENT_CUSTOM_DATA.RECODED_RESPONSE
,CLIENT_CUSTOM_DATA1.FIELD_NAME
,CLIENT_CUSTOM_DATA1.RECODED_RESPONSE
,DRUG_HISTORY_DATA.DRUG_CATEGORY
,DRUG_HISTORY_DATA.DRUG_DETAIL
,DRUG_HISTORY_DATA.DRUGORDER
FROM CLIENT_CUSTOM_DATA1
RIGHT JOIN (CLIENT_CUSTOM_DATA
RIGHT JOIN (DRUG_HISTORY_DATA
RIGHT JOIN (((CLIENTS
LEFT JOIN CLIENTS_AD_HOC ON CLIENTS.SHISID = CLIENTS_AD_HOC.SHISID)
LEFT JOIN CLIENT_PROGRAM_DATA ON CLIENTS.SHISID = CLIENT_PROGRAM_DATA.SHISID)
LEFT JOIN CLIENT_PROGRAM_DATA ON CLIENTS.SHISID = CLIENT_PROGRAM_DATA.SHISID) O NDRUG_HISTORY_DATA.SHISID = CLIENTS.SHISID) ON CLIENT_CUSTOM_DATA.SHISID = CLIENTS.SHISID) ON CLIENT_CUSTOM_DATA1.SHISID = CLIENTS.SHISID
WHERE (((CLIENT_CUSTOM_DATA.FIELD_NAME) LIKE 'CPS Involvement?')
AND ((CLIENT_CUSTOM_DATA1.FIELD_NAME) LIKE 'Reunification?')
AND ((DRUG_HISTORY_DATA.DRUGORDER)='1'))

Report message to a moderator

Re: problem with sql passthru syntax - get "FROM keyword not found where expected (4 merged) [message #689430 is a reply to message #689426]

Sat, 23 December 2023 00:34

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Thu, 08 September 2022 07:42

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

...

Using SQL*Plus:

SQL> SELECT CLIENTS.SHISID
  2     ,' ' AS [--CLient Ad Hoc--]
  3     ,CLIENTS_AD_HOC.TYPE_OF_RECORD
  4     ,CLIENTS_AD_HOC.CLIENT_LAST_NAME
  5     ,CLIENTS_AD_HOC.CLIENT_FIRST_NAME
  6     ,CLIENTS_AD_HOC.CLIENT_DOB
  7     ,CLIENTS_AD_HOC.CLIENT_ZIP
  8     ,CLIENTS_AD_HOC.CLIENT_CITY_COUNTY
  9     ,CLIENTS_AD_HOC.CLIENT_ID
 10     ,CLIENTS_AD_HOC.CLIENT_GENDER
 11     ,CLIENTS_AD_HOC.CLIENT_RACE
 12     ,CLIENTS_AD_HOC.CLIENT_ETHNICITY
 13     ,CLIENTS_AD_HOC.LEGAL_STATUS
 14     ,CLIENTS_AD_HOC.MARITAL_STATUS
 15     ,CLIENTS_AD_HOC.EMPLOYMENT_STATUS
 16     ,CLIENTS_AD_HOC.PREGNANTSTATUS
 17     ,CLIENTS_AD_HOC.FEMALEWITHDEPENDENT
 18     ,CLIENTS_AD_HOC.STUDENT_HOME_SET
 19     ,CLIENTS_AD_HOC.DISCHARGE_DATE_DEMOG
 20     ,CLIENTS_AD_HOC.INTAKE_DATE_DEMOG
 21     ,' ' AS [--Client Program Data--]
 22     ,CLIENT_PROGRAM_DATA.PROGRAM
 23     ,CLIENT_PROGRAM_DATA.DISCHARGE_DATE
 24     ,CLIENT_PROGRAM_DATA.INTAKE_DATE
 25     ,CLIENT_PROGRAM_DATA.DISCHARGE_REFERRAL_TYPE
 26     ,CLIENT_PROGRAM_DATA.LEVELOFCARE
 27     ,CLIENT_PROGRAM_DATA.LOCATION_NAME
 28     ,CLIENT_PROGRAM_DATA.LEGALSTATUSADMIT
 29     ,CLIENT_PROGRAM_DATA.ANTICIPATED_DISCHARGE_DATE
 30     ,CLIENT_PROGRAM_DATA.LEAD_CLINICIAN_NAME
 31     ,CLIENT_PROGRAM_DATA.DISCHARGE_REASON
 32     ,CLIENT_PROGRAM_DATA.DISCHARGE_REASON_PROGRAM
 33     ,CLIENT_PROGRAM_DATA.PROGRAM_PRIMARY_PAYER
 34     ,'' AS [--Clients--]
 35     ,CLIENTS.CURRENT_JURISDICTION
 36     ,CLIENTS.FAMINCOME
 37     ,CLIENTS.PHYS_NAME
 38     ,' ' AS [--Client Program--]
 39     ,CLIENT_PROGRAM_DATA.PROGRAM_PRIMARY_PAYER
 40     ,' ' AS [--Client Custom Data--]
 41     ,CLIENT_CUSTOM_DATA.FIELD_NAME
 42     ,CLIENT_CUSTOM_DATA.RECODED_RESPONSE
 43     ,CLIENT_CUSTOM_DATA1.FIELD_NAME
 44     ,CLIENT_CUSTOM_DATA1.RECODED_RESPONSE
 45     ,DRUG_HISTORY_DATA.DRUG_CATEGORY
 46     ,DRUG_HISTORY_DATA.DRUG_DETAIL
 47     ,DRUG_HISTORY_DATA.DRUGORDER
 48  FROM CLIENT_CUSTOM_DATA1
 49     RIGHT JOIN (CLIENT_CUSTOM_DATA
 50     RIGHT JOIN (DRUG_HISTORY_DATA
 51     RIGHT JOIN (((CLIENTS
 52     LEFT JOIN CLIENTS_AD_HOC ON CLIENTS.SHISID = CLIENTS_AD_HOC.SHISID)
 53     LEFT JOIN CLIENT_PROGRAM_DATA ON CLIENTS.SHISID = CLIENT_PROGRAM_DATA.SHISID)
 54     LEFT JOIN CLIENT_PROGRAM_DATA ON CLIENTS.SHISID = CLIENT_PROGRAM_DATA.SHISID)
 55          O NDRUG_HISTORY_DATA.SHISID = CLIENTS.SHISID)
 56          ON CLIENT_CUSTOM_DATA.SHISID = CLIENTS.SHISID)
 57          ON CLIENT_CUSTOM_DATA1.SHISID = CLIENTS.SHISID
 58  WHERE (((CLIENT_CUSTOM_DATA.FIELD_NAME) LIKE 'CPS Involvement?')
 59     AND ((CLIENT_CUSTOM_DATA1.FIELD_NAME) LIKE 'Reunification?')
 60     AND ((DRUG_HISTORY_DATA.DRUGORDER)='1'))
 61  /
        ,' ' AS [--CLient Ad Hoc--]
                *
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

Column alias is enclosed between "": AS "--CLient Ad Hoc--", or AS "[--CLient Ad Hoc--]" if you want to keep the brackets.

In addition, feedback in your topics if you want to continue to get help.

[Updated on: Sat, 23 December 2023 13:00]

Report message to a moderator

Re: problem with sql passthru syntax - get "FROM keyword not found where expected (4 merged) [message #689431 is a reply to message #689430]

Sat, 23 December 2023 12:21

Bob Alston
Messages: 6
Registered: September 2022

Junior Member

Michel -
Thank you very much. Hopefully the last problem I face.

Report message to a moderator

Re: problem with sql passthru syntax - get "FROM keyword not found where expected (4 merged) [message #689432 is a reply to message #689431]

Sat, 23 December 2023 20:20

Bob Alston
Messages: 6
Registered: September 2022

Junior Member

OK One more issue. In my query I need to utilize the same table two times, SHIS_DB_CLIENT_CUSTOM_DATA and SHIS_DB_CLIENT_CUSTOM_DATA1. I relinked to the table twice and got the two local names shown earlier. Alternatively, I often simply add the same table twice to the Access query design, where the 2nd is designated SHIS_DB_CLIENT_CUSTOM_DATA_1. Neither will fly in a pass thru. Any suggestions?

Bob

Report message to a moderator

How to reference the same table two times with different criteria in passthru query [message #689433 is a reply to message #689426]

Tue, 26 December 2023 13:15

Bob Alston
Messages: 6
Registered: September 2022

Junior Member

In my query I need to utilize the same table two times, SHIS_DB_CLIENT_CUSTOM_DATA and SHIS_DB_CLIENT_CUSTOM_DATA1. I relinked to the table twice and got the two local names shown earlier. Alternatively, I often simply add the same table twice to the Access query design, where the 2nd is designated SHIS_DB_CLIENT_CUSTOM_DATA_1. Neither will fly in a pass thru. Any suggestions?

Bob

I just tried this:
/forum/fa/14781/0/

I have a table named CLIENT_CUSTOM_DATA on a Oracle DB which I want to access two times using different criteria.

Attachment: ScreenHunter 39.png
(Size: 10.34KB, Downloaded 866 times)

[Updated on: Tue, 26 December 2023 13:41]

Report message to a moderator

Re: How to reference the same table two times with different criteria in passthru query [message #689434 is a reply to message #689433]

Tue, 26 December 2023 14:02

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You should post this question in an Access forum.

Report message to a moderator

Re: How to reference the same table two times with different criteria in passthru query [message #689435 is a reply to message #689434]

Tue, 26 December 2023 14:24

Bob Alston
Messages: 6
Registered: September 2022

Junior Member

OK. Just did. However the issue seems to me to be one that even other front end users might encounter.
B ob

Report message to a moderator

Re: How to reference the same table two times with different criteria in passthru query [message #689436 is a reply to message #689433]

Tue, 26 December 2023 22:47

flyboy
Messages: 1903
Registered: November 2006

Senior Member

https://stackoverflow.com/questions/21145028/how-to-use-the-as-keyword-to-alias-a-table-in-oracle
Quote:

You can use AS for table aliasing on many SQL servers (at least MsSQL, MySQL, PostrgreSQL) but it's always optional and on Oracle it's illegal. So remove the AS

FROM CLIENT_CUSTOM_DATA1 custom1 LEFT JOIN CLIENT_CUSTOM_DATA custom2 ON custom1.SHISID = custom2.SHISID

I wonder why you put double quotes (although it is quite hard to decipher from the picture, text would be more transparent) around "custom2" in one place only. Because, when you use it once, you have to use it on all its occurrences. So, the good practice in Oracle is not to use double quotes around aliases at all.

Report message to a moderator

Previous Topic:	Problem in pivot query
Next Topic:	Create Partition and Sub-Partition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 07:14:17 CDT 2024