Home » SQL & PL/SQL » SQL & PL/SQL » select query failing (oracle 10g)
select query failing [message #421774] Thu, 10 September 2009 07:49 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
SELECT EDQCL.DIET_QUESTION_CONTROL_ID,
  EDQL.DIET_QUESTION_ID,
  EDCL.DIET_CONTROL_ID,
  EDCL.DIET_CONTROL_NAME,
  EDCL.DIET_CONTROL_TYPE,
  EDQCL.HAS_TEXT,
  EDH.TEXT_VALUE,
  EDH.DIET_HISTORY_ID,
  EDCL.PARENT_ID,
  EDCL.HAS_CHILD,
  EDQL.IS_MULTI_SELECT
FROM EMRDietQuestionControlsLkup EDQCL
INNER JOIN EMRDietQuestionLkup EDQL
ON EDQL.DIET_QUESTION_ID=EDQCL.DIET_QUESTION_ID
AND EDQL.STATUS         =1
AND EDQCL.STATUS        =1
AND (EDQL.GROUP_ID     IS NULL
OR EDQL.GROUP_ID        =1520)
INNER JOIN EMRDietControlLkup EDCL
ON EDCL.DIET_CONTROL_ID=EDQCL.DIET_CONTROL_ID
AND EDCL.STATUS        =1
LEFT OUTER JOIN EMRDietHistory EDH
ON EDH.DIET_QUESTION_ID=EDQCL.DIET_QUESTION_ID
AND EDH.DIET_CONTROL_ID=EDQCL.DIET_CONTROL_ID
AND EDH.PATIENT_ID     ='52'
AND  ENCOUNTER_ID IN (SELECT MAX(ENCOUNTER_ID) FROM EMREncounterDetails WHERE PATIENT_ID = '66'
AND (IS_SAVE_FOR_LATER = 0
OR IS_SAVE_FOR_LATER  >= 3)) ORDER BY EDQCL.DIET_QUESTION_CONTROL_ID ASC




error coming like

SQL Error: ORA-01799: a column may not be outer-joined to a subquery
01799. 00000 - "a column may not be outer-joined to a subquery"
*Cause: <expression>(+) <relop> (<subquery>) is not allowed.
*Action: Either remove the (+) or make a view out of the subquery.
In V6 and before, the (+) was just ignored in this case.


please help me out as sqme query works fine for mssql
Re: select query failing [message #421775 is a reply to message #421774] Thu, 10 September 2009 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please help me out as sqme query works fine for mssql

What is supported in both rdbms is different.
Your query is unreadable, please format it (indent, See SQL Formatter).

Regards
Michel
Re: select query failing [message #421776 is a reply to message #421775] Thu, 10 September 2009 07:57 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
this is formatted in oracle sql developer itself.i got your answer but unable to trace it out in oracle.
Re: select query failing [message #421777 is a reply to message #421774] Thu, 10 September 2009 07:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
...
LEFT OUTER JOIN EMRDietHistory EDH
ON EDH.DIET_QUESTION_ID=EDQCL.DIET_QUESTION_ID
AND EDH.DIET_CONTROL_ID=EDQCL.DIET_CONTROL_ID
AND EDH.PATIENT_ID     ='52'
WHERE  (ENCOUNTER ID IS NULL OR ENCOUNTER_ID IN (SELECT MAX(eed.ENCOUNTER_ID) FROM EMREncounterDetails eed WHERE eed.PATIENT_ID = '66'
AND (eed.IS_SAVE_FOR_LATER = 0
OR eed.IS_SAVE_FOR_LATER  >= 3))) ORDER BY EDQCL.DIET_QUESTION_CONTROL_ID ASC

[Updated on: Thu, 10 September 2009 08:00]

Report message to a moderator

Re: select query failing [message #421779 is a reply to message #421777] Thu, 10 September 2009 08:11 Go to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
THANK YOU
Previous Topic: ORA-06550: Error
Next Topic: ORA-01555 INSERT INTO SELECT
Goto Forum:
  


Current Time: Fri Dec 09 08:11:01 CST 2016

Total time taken to generate the page: 0.07811 seconds