Q

Receiving GROUP BY error in Oracle 8i but not 10g

The following query works fine in Oracle 10g, but it gives a group by error in Oracle 8i.

This Content Component encountered an error
The following query works fine in Oracle 10g. But it gives a group by error at the line "(SELECT distinct DetailDesc FROM Common.detail WHERE detail=substr(c.head,12,2)) as description" in Oracle 8i. I have 8i and 10g at two different locations. Please help.
strQry = "select major,demand, head, pnp,cvt,sum(case when accountmonth < 
  " & AccMonCode & " and year = " & AccFinYear & " then amount else 0 end) 
  as uptothismonth," & _
            " sum(case when accountmonth = " & AccMonCode & " and year = 
  " & AccFinYear & " then amount else 0 end) as thismonth, " & _
            " sum(case when accountmonth <= " & AccMonCode & " and year = 
  " & AccFinYear & " then amount else 0 end) as total, " & _
            " (case when c.major >= '2011' and c.major <= '2059' then 'I' " & _
            "      when c.major >= '2070' and c.major <= '2205' then 'II' " & _
            "      when c.major >= '2210' and c.major <= '2217' then 'III' " & _
            "      when c.major >= '2220' and c.major <= '2404' then 'IV' " & _
            "      when c.major >= '2405' and c.major <= '2701' then 'V' " & _
            "      when c.major >= '2702' and c.major <= '3475' then 'VI' " & _
            "      when c.major >= '6003' and c.major <= '7610' then 'VII' " & _
            "      when c.major >= '4055' and c.major <= '4406' then 'VIII' " & _
            "      when c.major >= '4407' and c.major <= '5452' then 'IX' end) as volume, " & _
            " (SELECT distinct DetailDesc FROM Common.detail WHERE detail=substr(c.head,12,2)) as description, " & _
            " (SELECT distinct DemandDesc FROM Common.demand WHERE demand=c.demand) as demanddesc, " & _
            " (SELECT distinct majorDesc FROM Common.major WHERE major=c.major) as majordesc, " & _
            " (SELECT distinct submajorDesc FROM Common.submajor 
  WHERE major||submajor=substr(c.head,1,6)) as submajordesc, " & _
            " (SELECT distinct minorDesc FROM Common.minor 
  WHERE major||submajor||minor=substr(c.head,1,9)) as minordesc, " & _
            " (SELECT distinct subDesc FROM Common.sub 
  WHERE demand||major||submajor||minor||sub||pnp=c.demand||substr(c.head,1,11)||c.pnp) as subdesc, " & _
            " (case when substr(head,7,7) <> '0000000' then '1' else '2' end) as recovery " & _
            " from conspayments c where major < '8000' and year = 
  " & AccFinYear & " group by Head,major,Demand,PNP,CVT ORDER BY volume,major,recovery,demand,head,pnp,cvt"
The ability to put the subselect in the SELECT clause is not available in Oracle 8i. This feature first appeared in Oracle 10g. That is why it works in 10g, but not in 8i. You will have to recode your query to be able to run against an Oracle 8i database. Or upgrade that database to 10g.
This was first published in February 2007

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close