Home » SQL & PL/SQL » SQL & PL/SQL » MSAccess - SQLPlus Syntax Problem
| MSAccess - SQLPlus Syntax Problem [message #10658] |
Sun, 08 February 2004 19:43  |
LT
Messages: 29
Registered: August 2003
|
Junior Member |
|
|
I wonder if anyone can help me with this.
Currently there is an MSAccess report that uses quite a few Oracle linked tables. My boss wants me to create a single view from within Oracle so I have simply tried to utilise the MSAccess SQL to save me some work but it is giving me a problem. This is the MSAccess SQL:
This is an MSAccess query that calls several linked oracle tables and also uses two other MSAccess queries (V1qLastPolicy and V1CLaim_Transaction - I have created these queries as views in Oracle with no problem).
SELECT CLAIM_MEM_XREF.MEMBER_CODE, MEMBER.STATUS, MEMBER.DATE_MSHIP_APPROVED, MEMBER.DATE_OF_BIRTH, MEMBER.MAILING_NAME, MEMBER.PHONE_NO_HOME, MEMBER.PHONE_NO_WORK, MEMBER.PHONE_NO_MOBILE, MEMBER.MAIL_ADDRESS_1, MEMBER.MAIL_ADDRESS_2, MEMBER.MAIL_ADDRESS_3, MEMBER.MAIL_SUBURB, MEMBER.MAIL_STATE, MEMBER.MAIL_COUNTRY, MEMBER.MAIL_POSTCODE, MEMBER.SPECIAL_CONDITION, MEMBER_POLICY.SPEC_ANSWER, MEMBER_POLICY.REVISED_END_DATE, CLAIM.CLAIM_NO, CLAIM.CLAIM_DESCRIPTION, CLAIM.INCIDENT_DATE, CLAIM.NOTIFICATION_DATE, CLAIM.CASE_CODE, CLAIM.CASE_STATUS, CLAIM_ESTIMATE.TOTAL_ESTIMATE, CLAIM_ESTIMATE.TOTAL_CLAIM_ESTIMATE, V1CLaim_Transaction.SumOfTRANSACTION_AMOUNT, CLAIM_CLAIMANT.CLAIMANT_ID, CLAIM_CLAIMANT.TITLE, CLAIM_CLAIMANT.FIRST_NAME, CLAIM_CLAIMANT.LAST_NAME, CLAIM_NOTE.CREATED_DATE, CLAIM_NOTE.CREATED_BY, CLAIM_NOTE.NOTE_ID, CLAIM_NOTE.NOTE
FROM (((((((CLAIM_MEM_XREF LEFT JOIN V1qLastPolicy ON CLAIM_MEM_XREF.MEMBER_CODE = V1qLastPolicy.MEMBER_CODE) LEFT JOIN MEMBER_POLICY ON V1qLastPolicy.MaxOfPOLICY_NUMBER = MEMBER_POLICY.POLICY_NUMBER) LEFT JOIN CLAIM ON CLAIM_MEM_XREF.CLAIM_ID = CLAIM.CLAIM_ID) LEFT JOIN V1CLaim_Transaction ON CLAIM.CLAIM_ID = V1CLaim_Transaction.CLAIM_ID) LEFT JOIN CLAIM_ESTIMATE ON (CLAIM.LAST_ESTIMATE_ID = CLAIM_ESTIMATE.ESTIMATE_ID) AND (CLAIM.CLAIM_ID = CLAIM_ESTIMATE.CLAIM_ID)) LEFT JOIN MEMBER ON CLAIM_MEM_XREF.MEMBER_CODE = MEMBER.MEMBER_CODE) LEFT JOIN CLAIM_NOTE ON CLAIM.CLAIM_ID = CLAIM_NOTE.CLAIM_ID) LEFT JOIN CLAIM_CLAIMANT ON CLAIM.CLAIM_ID = CLAIM_CLAIMANT.CLAIM_ID
WHERE (((CLAIM_MEM_XREF.MEMBER_CODE)=[[ENTER MEMBER CODE]]) AND ((CLAIM_MEM_XREF.PRIMARY_MEMBER)="Y"));
I have tried to convert the above into an SQLPlus query but I must be doing something wrong. It should return me three records but I dont get anything. I think its something to do with my joins. This is my SQLPLus query:
SELECT
CLAIM_MEM_XREF.MEMBER_CODE,
MEMBER.STATUS,
MEMBER.DATE_MSHIP_APPROVED,
MEMBER.DATE_OF_BIRTH,
MEMBER.MAILING_NAME,
MEMBER.PHONE_NO_HOME,
MEMBER.PHONE_NO_WORK,
MEMBER.PHONE_NO_MOBILE,
MEMBER.MAIL_ADDRESS_1,
MEMBER.MAIL_ADDRESS_2,
MEMBER.MAIL_ADDRESS_3,
MEMBER.MAIL_SUBURB,
MEMBER.MAIL_STATE,
MEMBER.MAIL_COUNTRY,
MEMBER.MAIL_POSTCODE,
MEMBER.SPECIAL_CONDITION,
MEMBER_POLICY.SPEC_ANSWER,
MEMBER_POLICY.REVISED_END_DATE,
CLAIM.CLAIM_NO,
CLAIM.CLAIM_DESCRIPTION,
CLAIM.INCIDENT_DATE,
CLAIM.NOTIFICATION_DATE,
CLAIM.CASE_CODE,
CLAIM.CASE_STATUS,
CLAIM_ESTIMATE.TOTAL_ESTIMATE,
CLAIM_ESTIMATE.TOTAL_CLAIM_ESTIMATE,
VCLaim_Transaction.TOTAL_TRANSACTION,
CLAIM_CLAIMANT.CLAIMANT_ID,
CLAIM_CLAIMANT.TITLE,
CLAIM_CLAIMANT.FIRST_NAME,
CLAIM_CLAIMANT.LAST_NAME,
CLAIM_NOTE.CREATED_DATE,
CLAIM_NOTE.CREATED_BY,
CLAIM_NOTE.NOTE_ID,
CLAIM_NOTE.NOTE
FROM claim_mem_xref, vlast_policy, member_policy, claim, vclaim_transaction,
claim_estimate, member, claim_note, claim_claimant
where
CLAIM_MEM_XREF.MEMBER_CODE = VLast_Policy.MEMBER_CODE
and VLast_Policy.Max_POLICY_NUMBER = MEMBER_POLICY.POLICY_NUMBER
and CLAIM_MEM_XREF.CLAIM_ID = CLAIM.CLAIM_ID
and CLAIM.CLAIM_ID = VCLaim_Transaction.CLAIM_ID
and CLAIM.LAST_ESTIMATE_ID = CLAIM_ESTIMATE.ESTIMATE_ID
and CLAIM.CLAIM_ID = claim_estimate.CLAIM_ID
and CLAIM_MEM_XREF.MEMBER_CODE = MEMBER.MEMBER_CODE
and CLAIM.CLAIM_ID = CLAIM_NOTE.CLAIM_ID
and CLAIM.CLAIM_ID = CLAIM_CLAIMANT.CLAIM_ID
and CLAIM_MEM_XREF.MEMBER_CODE='JONESA008'
AND CLAIM_MEM_XREF.PRIMARY_MEMBER = 'Y'
/
can anyone help?
|
|
|
|
| Re: MSAccess - SQLPlus Syntax Problem [message #10668 is a reply to message #10658] |
Mon, 09 February 2004 00:36  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In your original query you have V1qLastPolicy.MaxOfPOLICY_NUMBER and in your re-write you have VLastPolicy.MaxPOLICY_NUMBER. I will assume that the second one is correct, for purposes of this example. If you are using Oracle version 9i then your ANSI SQL join syntax in your original query should work fine. However, if you are using an earlier version, such as Oracle 8i, then you will need to add a (+) for each of your left joins. You will also need to use a substitution variable, which is a variable name prefaced by an ampersand, to prompt to enter a value, instead of your [[ENTER MEMBER CODE]]. Please see the example below.
SELECT CLAIM_MEM_XREF.MEMBER_CODE,
MEMBER.STATUS,
MEMBER.DATE_MSHIP_APPROVED,
MEMBER.DATE_OF_BIRTH,
MEMBER.MAILING_NAME,
MEMBER.PHONE_NO_HOME,
MEMBER.PHONE_NO_WORK,
MEMBER.PHONE_NO_MOBILE,
MEMBER.MAIL_ADDRESS_1,
MEMBER.MAIL_ADDRESS_2,
MEMBER.MAIL_ADDRESS_3,
MEMBER.MAIL_SUBURB,
MEMBER.MAIL_STATE,
MEMBER.MAIL_COUNTRY,
MEMBER.MAIL_POSTCODE,
MEMBER.SPECIAL_CONDITION,
MEMBER_POLICY.SPEC_ANSWER,
MEMBER_POLICY.REVISED_END_DATE,
CLAIM.CLAIM_NO,
CLAIM.CLAIM_DESCRIPTION,
CLAIM.INCIDENT_DATE,
CLAIM.NOTIFICATION_DATE,
CLAIM.CASE_CODE,
CLAIM.CASE_STATUS,
CLAIM_ESTIMATE.TOTAL_ESTIMATE,
CLAIM_ESTIMATE.TOTAL_CLAIM_ESTIMATE,
V1CLaim_Transaction.SumOfTRANSACTION_AMOUNT,
CLAIM_CLAIMANT.CLAIMANT_ID,
CLAIM_CLAIMANT.TITLE,
CLAIM_CLAIMANT.FIRST_NAME,
CLAIM_CLAIMANT.LAST_NAME,
CLAIM_NOTE.CREATED_DATE,
CLAIM_NOTE.CREATED_BY,
CLAIM_NOTE.NOTE_ID,
CLAIM_NOTE.NOTE
FROM claim_mem_xref,
vlast_policy,
member_policy,
claim,
vclaim_transaction,
claim_estimate,
member,
claim_note,
claim_claimant
WHERE CLAIM_MEM_XREF.MEMBER_CODE = VLastPolicy.MEMBER_CODE (+)
AND VLastPolicy.MaxPOLICY_NUMBER = MEMBER_POLICY.POLICY_NUMBER (+)
AND CLAIM_MEM_XREF.CLAIM_ID = CLAIM.CLAIM_ID (+)
AND CLAIM.CLAIM_ID = V1CLaim_Transaction.CLAIM_ID (+)
AND CLAIM.LAST_ESTIMATE_ID = CLAIM_ESTIMATE.ESTIMATE_ID (+)
AND CLAIM.CLAIM_ID = CLAIM_ESTIMATE.CLAIM_ID (+)
AND CLAIM_MEM_XREF.MEMBER_CODE = MEMBER.MEMBER_CODE (+)
AND CLAIM.CLAIM_ID = CLAIM_NOTE.CLAIM_ID (+)
AND CLAIM.CLAIM_ID = CLAIM_CLAIMANT.CLAIM_ID (+)
AND CLAIM_MEM_XREF.MEMBER_CODE = '&the_member_code'
AND CLAIM_MEM_XREF.PRIMARY_MEMBER = 'Y'
/
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 18:11:24 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
