View Compiles but Selecting It Returns Error

[thermidor]

Hi All,

I have created a view with the definition below - please be patient, it's not as hairy as it looks at first). The problem here is I can create the view, but when I try to select on the view I get a "ORA-00907: missing right parenthesis" error. I also get this error if I try to run the SQL separately. I'm confused because the statement is not that complicated. If I run the part in red, it runs fine...

This one really has me pulling my hair out. If anyone can give any insight/suggestions, I'd really appreciate it.

TIA,
Sven

SELECT
acct.acctcd AS acctcd,
upper(acct.acctdsc) AS acctshortnm,
coalesce(chsup.chcd,' ') AS chcd,
upper(coalesce(chsup.chshortnm,' ')) AS chshortnm,
coalesce(chsup.schcd,' ') AS schcd,
upper(coalesce(chsup.schshortnm,' ')) AS schshortnm,
SYSDATE AS effdt
FROM
acct acct
LEFT JOIN
(SELECT
acctcd,
acctshortnm,
src,
chcd,
chshortnm,
schcd,
schshortnm
FROM
((SELECT
ch.chcd AS chcd,
ch.chshortnm AS chshortnm,
s.schcd AS schcd,
s.schshortnm AS schshortnm,
s.schcd AS acctcd,
s.schshortnm AS acctshortnm,
'S' AS src
FROM Cch ch
LEFT JOIN
chs s
ON ch.schcd=s.schcd )
UNION ALL (
SELECT ch.chcd AS chcd,
ch.chshortnm AS chshortnm,
s.schcd AS schcd,
s.schshortnm AS schshortnm,
ch.chcd AS acctcd,
ch.chshortnm AS acctshortnm,
'C' AS src
FROM ch ch
LEFT JOIN
chs s
ON ch.schcd=s.schcd) ) x
WHERE acctcd IS NOT NULL) [/color red]
chsup ON acct.acctcd = chsup.acctcd

 

[LKBrwnDBA]

You are missing a join or something:

SELECT acct.acctcd AS acctcd, ...
  FROM acct acct 
  LEFT JOIN 
   +->(SELECT acctcd, acctshortnm, ...
   |     FROM 
   |   +->((SELECT ch.chcd AS chcd, ...
   |   |     FROM Cch ch 
   |   |     LEFT JOIN chs s ON ch.schcd=s.schcd) 
   |   |   UNION ALL 
   |   |  (SELECT ch.chcd AS chcd, ...
   |   |     FROM ch ch 
   |   |     LEFT JOIN chs s ON ch.schcd=s.schcd)
   |   +->) x 
   +--> WHERE acctcd IS NOT NULL) 
 ???->chsup ON acct.acctcd = chsup.acctcd

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Unless, chsup is the ALIAS? -- then all parentesis do seem to match!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[thermidor]

chsup is the alias.
What makes this even more confusing to me is I took the part that "works" (in red int the first post) and put it into a separate view. So then I could simplify my view definition to something like:
SELECT acct.acctcd AS acctcd, ...
FROM acct acct
LEFT JOIN
(SELECT * from tmpview)
chsup ON acct.acctcd = chsup.acctcd

Unfortunately this returned the same error...

Thanks,
Sven

 

[LKBrwnDBA]

Does this second view execute correctly by itself?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[thermidor]

The second view worked correctly. That is it compiled and I was able to select from it...

 

[LKBrwnDBA]

Just a hunch, but change second view to:

SELECT acct.acctcd AS acctcd, ...
  FROM acct acct 
  LEFT JOIN tmpview chsup
    ON acct.acctcd = chsup.acctcd

I have no way to reproduce the issue because I have version 8.1.7 (no LEFT JOIN syntax).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[thermidor]

Based on my research this is a known Oracle bug affecting versions < 10.2.