Nested Query Works in 9 and not 8?

[bobhagan]

I created a stored procedure with a query containing a nested subselect:

SELECT C.CLIENT_NM, C.CLIENT_ID,
  Max(decode(substr(s.STATUS,1,2),'N-', s.Status_dt, null))     "LastRefer_Dt",
  Max(decode(substr(e.ENC_TYPE,1,4),'INIT', e.END_DT, null)) "LastInitVisit_Dt",
 [COLOR=red] (select ENC_STAT from encountr where encountr.end_dt = (select max(decode(substr(encountr.ENC_TYPE,1,4), 'INIT', encountr.end_dt, null)) from encountr where encountr.client_id = c.client_id)) "InitVisitStatus" [/color red]
FROM
   CLIENT C,
   STATUS S,
   ENCOUNTR E
WHERE ...
GROUP BY ...

I compiled this on my home machine running 9i, but when I tried it on a work machine running against ORA 8.0.5 server, that says ENC_STAT is not part of the grouping. Is this really a difference between versions? If so, is there anyway to write this so it works with 8? Or am I doing something wrong?

Thanks
Bob Hagan

 

[SantaMufasa]

Bob,

First, what you are leaving out with your elipsis ("...") in the GROUP BY is central to your error, so please copy and paste the entire SELECT statement and its exact error message.

Second, every version of Oracle supports "virtual VIEWs" that you specify in the "FROM..." clause. So, if you provide the full syntax, I'm certain we can get this to work for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 05:21 (21Dec04) UTC (aka "GMT" and "Zulu"),
@ 22:21 (20Dec04) Mountain Time

 

[bobhagan]

The entire query is

SELECT C.MGR_KEY, C.WKR_KEY, C.CLIENT_NM, C.CLIENT_ID,C.ADDR1, C.ZIP, C.PHONE1, C.CASE_TYPE,   
  Max(decode(substr(s.STATUS,1,2),'N-', s.Status_dt, null)) "LastRefer_Dt",   
  Max(decode(s.STATUS,'O', s.Status_dt, null)) "LastOpen_Dt",   
  Max(decode(s.STATUS,'TRANS-O', s.Status_dt, null)) "LastTransO_Dt",   
  Max(decode(s.STATUS,'TRANS-P', s.Status_dt, null)) "LastTransP_Dt",   
  Max(decode(s.STATUS,'C', s.Status_dt, null)) "LastClose_Dt",   
  Max(decode(substr(e.ENC_TYPE,1,4),'INIT', e.END_DT, null)) "LastInit_Dt",   
  (select ENC_STAT from encountr where encountr.end_dt = (select max(decode(substr(encountr.ENC_TYPE,1,4), 'INIT', encountr.end_dt, null)) from encountr where encountr.client_id = c.client_id)) "InitStat",   
  Max(decode(substr(e.ENC_TYPE,1,4),'FOLLUP', e.END_DT, null)) "LastFOLLUP_Dt",   
  Max(decode(substr(e.ENC_TYPE,1,4),'M-VIST', e.END_DT, null)) "LastMVIST_Dt"   
FROM   
   CLIENT C,   
   STATUS S,   
   ENCOUNTR E   
WHERE   
C.CLIENT_ID=S.CLIENT_ID AND   
E.CLIENT_ID=S.CLIENT_ID (+)    
GROUP BY C.MGR_KEY, C.WKR_KEY, C.CLIENT_NM, C.CLIENT_ID, C.ADDR1, C.ZIP, C.PHONE1, C.CASE_TYPE

(There were some parameters in the WHERE, but I was trying to run this to Crystal and I finally figured out that the way to do that was to convert this to a view)

The error is ORA 00979 Not a Group by expression.
When I got this message, a co-worker and I tried adding Enc_stat or InitStat and even the whole expression to the group by and always got ORA 00904 Invalid Column Name.

What's really strange is that this compiles with no trouble on my home computer that's running 9i. I have an 8i client on my work machine and the server runs 8.0.5.

What's a virtual view? I'm afraid I've been a FoxPro guy, and now do reports for a group that's running a legacy Oracle app. This is my first shot at doing anything complicated with Oracle. The end report using Crystal to do the data access is a real dog, so I got desparate.

Thanks for any suggestions.
Bob Hagan

 

[SantaMufasa]

Bob,

First, you have a logic error in your original query:

...Max(decode(substr(e.ENC_TYPE,1,4),'FOLLUP', e.END_DT, null)) "LastFOLLUP_Dt",   
   Max(decode(substr(e.ENC_TYPE,1,4),'M-VIST', e.END_DT, null)) "LastMVIST_Dt"...

The problem, above, is that the results of your DECODE will always be NULL since "substr(e.ENC_TYPE,1,4)" can never be equal to either the six-character string, 'FOLLUP', or six-character string, 'M-VIST'.

Second, your original code, although it works syntactically in Oracle 9, as you presumed, it does not work in Oracle 8. Following is my re-work of your original code so that it works in Oracle 8. I have emboldened the type-face of my changes:

  Max(decode(substr(s.STATUS,1,2),'N-', s.Status_dt, null)) "LastRefer_Dt",
  Max(decode(s.STATUS,'O', s.Status_dt, null)) "LastOpen_Dt",
  Max(decode(s.STATUS,'TRANS-O', s.Status_dt, null)) "LastTransO_Dt",
  Max(decode(s.STATUS,'TRANS-P', s.Status_dt, null)) "LastTransP_Dt",
  Max(decode(s.STATUS,'C', s.Status_dt, null)) "LastClose_Dt",
  Max(decode(substr(e.ENC_TYPE,1,4),'INIT', e.END_DT, null)) "LastInit_Dt",
  [b]sq.ENC_STAT[/b] "InitStat",
  Max(decode(substr(e.ENC_TYPE,1,4),'FOLLUP', e.END_DT, null)) "LastFOLLUP_Dt", -- logic error
  Max(decode(substr(e.ENC_TYPE,1,4),'M-VIST', e.END_DT, null)) "LastMVIST_Dt" -- logic error
FROM[b]
  (select client_id,ENC_STAT from encountr where encountr.end_dt in
          (select max(decode(substr(encountr.ENC_TYPE,1,4), 'INIT', encountr.end_dt, null))
             from encountr)
  ) sq,[/b]
   CLIENT C,
   STATUS S,
   ENCOUNTR E
WHERE
[b]sq.client_id(+) = c.client_id and[/b]
C.CLIENT_ID=S.CLIENT_ID AND
E.CLIENT_ID=S.CLIENT_ID (+)
GROUP BY C.MGR_KEY
	,C.WKR_KEY
	,C.CLIENT_NM
	,C.CLIENT_ID
	,C.ADDR1
	,C.ZIP
	,C.PHONE1
	,C.CASE_TYPE
[b]	,sq.client_id
	,sq.ENC_STAT [/b]
/

A "virtual (or in-line) view" exists when you a "SELECT" statement appears in your "FROM" list, as I have done, above.

Let me know if this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:19 (21Dec04) UTC (aka "GMT" and "Zulu"),
@ 11:19 (21Dec04) Mountain Time

 

[bobhagan]

Thanks for the suggestions.

The logic bit is embarrasing, because I had that working and didn't even notice I'd recreated the extra columns incorrectly.

The original subquery on my 9i setup returned values for all records on the enc_stat column. With the new version on 9i, it returns an enc_stat value for only the first client. On 8i, although the query compiles and runs, it returns none of the values for the enc_stat column. (Maybe the first record had no value) It looks like the sub-query doesn't update. I couldn't find a way to fix it.

Thanks
Bob Hagan

 

[bobhagan]

<(Maybe the first record had no value) >
No, that's not the case. I just checked.

Also, is there a difference between the way 8 and 9 handle joins? I noticed last night that on 9i that I had to add (+) to "C.CLIENT_ID=S.CLIENT_ID" or it acted like an inner join and only returned clients with both status and contact (encountr) records. I thought I'd made a mistake at work, but here 8 will not let me put on two outer joins, and with only the one, it returns all clients selected, contacts or not.

Thanks again
Bob Hagan

 

[SantaMufasa]

Bob,

It's tough to nail down problems without your most recent incarnation of code. Could you please post your code?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:13 (23Dec04) UTC (aka "GMT" and "Zulu"),
@ 09:13 (23Dec04) Mountain Time

 

[bobhagan]

SELECT C.WKR_KEY, C.CLIENT_NM, C.CLIENT_ID, C.STATUS,  
  Max(decode(substr(s.STATUS,1,2),'N-', s.Status_dt, null)) "LastRefer_Dt",
  Max(decode(substr(e.ENC_TYPE,1,4),'INIT', e.END_DT, null)) "LastInitVisit_Dt",
  sq.ENC_STAT "InitV_Status"
FROM
  (select client_id, ENC_STAT from encountr where encountr.end_dt in
          (select max(decode(substr(encountr.ENC_TYPE,1,4), 'INIT', encountr.end_dt, null))
             from encountr)
  ) sq,
   CLIENT C,
   STATUS S,
   ENCOUNTR E
WHERE
sq.client_id(+) = c.client_id and
C.CLIENT_ID=S.CLIENT_ID AND
E.CLIENT_ID=S.CLIENT_ID (+)
AND C.MGR_KEY LIKE 'KA008'
AND (C.STATUS IN ('N-24','N-24EC','N-24DOI','N-72','N-72EC','N-72DOI','O','TRANS-O','TRANS-P', 'CUIUPDATE','CIUUPD-EC','CIUUPD-DOI')
OR (C.STATUS LIKE 'C' AND C.STATUS_DT >= TO_DATE('12,1,2004','MM/DD/YYYY') AND C.STATUS_DT <= TO_DATE('12,31,2004','MM/DD/YYYY'))) 
GROUP BY 
    C.WKR_KEY
    ,C.CLIENT_NM
    ,C.CLIENT_ID
	,C.STATUS
    ,sq.client_id
    ,sq.ENC_STAT

This cuts out some of the repetitive calculated fields, but adds in what I'm using in the WHERE with hard-coded values instead of parms.

Happy Christmas Santa.