Home » SQL & PL/SQL » SQL & PL/SQL » query problem (oracle 10g)
query problem [message #418947] Wed, 19 August 2009 05:11 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
SELECT DISTINCT TO_CHAR(EPI.DATE_OF_CREATION,'MM-DD-YYYY HH:MI AM') AS DATE_OF_CREATION ,
EPI.INSURANCE_PAYER_ID,EPI.PATIENT_ID,ETL.INSURANCE_TYPE_ID,ETL.INSURANCE_TYPE,
EPI.INSURANCE_NAME,EPI.INSURANCE_PHONE,EPI.INSURANCE_ID,EPI.INSURANCE_GROUP, 
EPI.EMPLOYER, EPI.INSURANCE_SSN, EPI.RELATIONSHIP_TO_INSURED, EPI.MEDICAID_NUMBER,
EPI.MEDICARE_NUMBER, EPI.INSURANCE_CARD, EPI.INSURANCE_COPAY,EPI.PATIENT_INSURANCE_ID,
EPI.INSURANCE_REF_ID,EPI.INSURANCE_ADDRESS,EPI.INSURANCE_CITY,EPI.STATE_ID,
EPI.INSURANCE_ZIPCODE,EPI.POLICY_NUMBER,EPI.GUARANTOR_ID,EPI.INSURANCE_NAME_ID,
EPI.EMPLOYER_PHONE,EPI.INSURANCE_PROGRAM,EPI.PATIENT_INSURANCE_TYPE,EPI.FIRSTNAME_OF_INSURED,
EPI.LASTNAME_OF_INSURED, EPI.INSURANCE_DOB,EPI.INSURANCE_GENDER,ENL.INSURANCE_MNEMONIC 
FROM   EMRPatientInsurance EPI, EMRInsuranceTypesLkup ETL 
LEFT OUTER JOIN EMRInsuranceNamesLkUp ENL ON ENL.INSURANCE_NAME_ID= EPI.INSURANCE_NAME_ID 
WHERE   EPI.INSURANCE_TYPE_ID = ETL.INSURANCE_TYPE_ID 
AND (  PATIENT_ID IN (SELECT PATIENT_ID FROM EMRAppointmentDetailsHistory 
WHERE CONSULTANT_ID='1504_Con') OR PATIENT_ID IN 
(SELECT PATIENT_ID FROM EMREncounterDetails WHERE CONSULTANT_ID='1504_Con') 
OR PATIENT_ID IN (SELECT PATIENT_ID FROM EMRLabRequestMaster WHERE CONSULTANT_ID='1504_Con') 
OR PATIENT_ID IN (SELECT PATIENT_ID FROM EMRPrescriptions WHERE CONSULTANT_ID='1504_Con')
OR PATIENT_ID IN (SELECT PATIENT_ID FROM EMRECharts WHERE CONSULTANT_ID='1504_Con')
OR PATIENT_ID IN (SELECT PATIENT_ID FROM EMRChestXrayResults WHERE USER_ID='1504_Con') ) 




exception iam facing is

Error at Command Line:11 Column:68
Error report:
SQL Error: ORA-00904: "EPI"."INSURANCE_NAME_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"

please help me out
Re: query problem [message #418949 is a reply to message #418947] Wed, 19 August 2009 05:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no column called INSURANCE_NAME_ID in the table EMRPatientInsurance
Re: query problem [message #418950 is a reply to message #418949] Wed, 19 August 2009 05:14 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi it is there with data type as number.
Re: query problem [message #418952 is a reply to message #418950] Wed, 19 August 2009 05:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In which case it's probably something to do with you referencing EPI.INSURANCE_NAME_ID while trying to outer join two tables neither of which is EMRPatientInsurance

[spotted an different probable error]

[Updated on: Wed, 19 August 2009 05:21]

Report message to a moderator

Re: query problem [message #418953 is a reply to message #418952] Wed, 19 August 2009 05:25 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
SELECT DISTINCT TO_CHAR(EPI.DATE_OF_CREATION,'MM-DD-YYYY HH:MI AM') AS DATE_OF_CREATION ,
EPI.INSURANCE_PAYER_ID,EPI.PATIENT_ID,ETL.INSURANCE_TYPE_ID,ETL.INSURANCE_TYPE,
EPI.INSURANCE_NAME,EPI.INSURANCE_PHONE,EPI.INSURANCE_ID,EPI.INSURANCE_GROUP, 
EPI.EMPLOYER, EPI.INSURANCE_SSN, EPI.RELATIONSHIP_TO_INSURED, EPI.MEDICAID_NUMBER,
EPI.MEDICARE_NUMBER, EPI.INSURANCE_CARD, EPI.INSURANCE_COPAY,EPI.PATIENT_INSURANCE_ID,
EPI.INSURANCE_REF_ID,EPI.INSURANCE_ADDRESS,EPI.INSURANCE_CITY,EPI.STATE_ID,
EPI.INSURANCE_ZIPCODE,EPI.POLICY_NUMBER,EPI.GUARANTOR_ID,EPI.INSURANCE_NAME_ID,
EPI.EMPLOYER_PHONE,EPI.INSURANCE_PROGRAM,EPI.PATIENT_INSURANCE_TYPE,EPI.FIRSTNAME_OF_INSURED,
EPI.LASTNAME_OF_INSURED, EPI.INSURANCE_DOB,EPI.INSURANCE_GENDER,ENL.INSURANCE_MNEMONIC 
FROM   EMRPatientInsurance EPI
LEFT OUTER JOIN EMRInsuranceTypesLkup ETL ON EPI.INSURANCE_TYPE_ID = ETL.INSURANCE_TYPE_ID
LEFT OUTER JOIN EMRInsuranceNamesLkUp ENL ON ENL.INSURANCE_NAME_ID= EPI.INSURANCE_NAME_ID 
WHERE   EPI.INSURANCE_TYPE_ID = ETL.INSURANCE_TYPE_ID 
AND (  PATIENT_ID IN (SELECT PATIENT_ID FROM EMRAppointmentDetailsHistory 
WHERE CONSULTANT_ID='1504_Con') OR PATIENT_ID IN 
(SELECT PATIENT_ID FROM EMREncounterDetails WHERE CONSULTANT_ID='1504_Con') 
OR PATIENT_ID IN (SELECT PATIENT_ID FROM EMRLabRequestMaster WHERE CONSULTANT_ID='1504_Con') 
OR PATIENT_ID IN (SELECT PATIENT_ID FROM EMRPrescriptions WHERE CONSULTANT_ID='1504_Con')
OR PATIENT_ID IN (SELECT PATIENT_ID FROM EMRECharts WHERE CONSULTANT_ID='1504_Con')
OR PATIENT_ID IN (SELECT PATIENT_ID FROM EMRChestXrayResults WHERE USER_ID='1504_Con') ) 



its working fine with this please check me and let me know the way iam proceeding is correct
Re: query problem [message #418954 is a reply to message #418953] Wed, 19 August 2009 05:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Without knowing what you are trying to do, it is not possible to say whether what you are doing is correct.
Re: query problem [message #418963 is a reply to message #418953] Wed, 19 August 2009 06:47 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
its working fine with this please check me and let me know the way iam proceeding is correct


something is wrong
first you write
emrpatientinsurance epi LEFT OUTER JOIN emrinsurancetypeslkup etl
                ON epi.insurance_type_id = etl.insurance_type_id

then in WHERE clause
epi.insurance_type_id = etl.insurance_type_id


SO I will advice you to read about outer joins

Previously it threw an error because of this
enl.insurance_name_id = epi.insurance_name_id


as most probably ENL emrinsurancenameslkup does not have this insurance_name_id field Wink

Please let me know if I am wrong somewhere.

[Updated on: Wed, 19 August 2009 06:55]

Report message to a moderator

Previous Topic: RECURSION IN PL SQL
Next Topic: polling in oracle
Goto Forum:
  


Current Time: Sun Dec 04 00:31:30 CST 2016

Total time taken to generate the page: 0.07706 seconds