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)
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'))
|
|
|
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: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Thu, 08 September 2022 07:42Welcome 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
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:24:19 CDT 2024
|