Ask the Expert

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 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"

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: