Home » SQL & PL/SQL » SQL & PL/SQL » What is wrong with this query?
What is wrong with this query? [message #185732] Thu, 03 August 2006 06:10 Go to next message
kw57
Messages: 3
Registered: August 2006
Junior Member
This is a query being entered into Cognos Impromptu to an Oracle 9i database.

The errror being reported is a "Statement is not ended correctly"

Any asistance would be gratefully received.



Select T3."GMAID", T3,"GMLD01"

from "PRODDTA"."F0901" T3,

where T3."GMAID" = T1."GBAID"

(select T1."GBAID",

MAX(DECODE(T1."GBLT",'BA',NVL("GBAN11"/100,0))) T1.Budget_Amt

MAX(DECODE(T1."GBLT",'AA',NVL("GBAN11"/100,0))) T1.Actual_Amt

from "PRODDTA"."F0902" T1,

group by T1."GBAID" T1."GBAID" T1

where T3."GMAID" = T1."GBAID"

and T1."GBAN11"/100 is not null

and T1.Budget_Amt <>0

and T1.Actual_Amt <>0)

order by "Description" asc
Re: What is wrong with this query? [message #185735 is a reply to message #185732] Thu, 03 August 2006 06:21 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
at first glance, get rid of the comm after T3.

Jim
Re: What is wrong with this query? [message #185740 is a reply to message #185735] Thu, 03 August 2006 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And remove the comma from the end of
from "PRODDTA"."F0902" T1,
Re: What is wrong with this query? [message #185748 is a reply to message #185735] Thu, 03 August 2006 07:13 Go to previous messageGo to next message
kw57
Messages: 3
Registered: August 2006
Junior Member
Having removed the 2 [,] at the end of the table names the query still refuses to verify / execute and shows an incomplete query error.

Any ideas?

[Updated on: Thu, 03 August 2006 07:15]

Report message to a moderator

Re: What is wrong with this query? [message #185757 is a reply to message #185748] Thu, 03 August 2006 07:47 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I've never seen this "Cognos Impromptu" and really don't know what it is (Google isn't option right now), but it produces not queries, but garbage.

GROUP BY is between FROM and WHERE clauses.

SELECT (subquery?) comes from nowhere, as well as T1.%_amt columns.

"T1.GBAN11/100 IS NULL" is nothing better than "T1.GBAN11 IS NULL"

I have no idea will this query return anything, but it is correctly written, at least ...
SELECT   t3.gmai, t3.gmld01, t1.gbaid,
         MAX (DECODE (t1.gblt, 'BA', NVL (gban11 / 100, 0))), t1.budget_amt,
         MAX (DECODE (t1.gblt, 'AA', NVL (gban11 / 100, 0))), t1.actual_amt
    FROM proddta.f0901 t3, proddta.f0902 t1
   WHERE t3.gmaid = t1.gbaid
     AND t1.gban11 IS NOT NULL
     AND t1.budget_amt <> 0
     AND t1.actual_amt <> 0
GROUP BY t3.gmai, t3.gmld01, t1.gbaid, t1.budget_amt, t1.actual_amt
ORDER BY description
Re: What is wrong with this query? [message #185764 is a reply to message #185748] Thu, 03 August 2006 08:07 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
kw57 wrote on Thu, 03 August 2006 08:13

Having removed the 2 [,] at the end of the table names the query still refuses to verify / execute and shows an incomplete query error.

Any ideas?


You could have pasted this garbage query, as Littlefoot correctly labels it, into a SQL*Plus session and it would tell you where the errors are, albeit one at a time, so it will take several attempts to run in order to find all the errors.
Re: What is wrong with this query? [message #185773 is a reply to message #185764] Thu, 03 August 2006 09:27 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Z├╝rich, Switzerland
Senior Member
Hi,

whats about the field in the subquery
Does GBAN11/100 do not need the table declaration T1 ??

MAX(DECODE(T1."GBLT",'BA',NVL("GBAN11"/100,0))) T1.Budget_Amt
Re: What is wrong with this query? [message #185792 is a reply to message #185773] Thu, 03 August 2006 11:51 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Not necessary; it would be needed if this column existed in more than one table. Using table alias would then avoid COLUMN AMBIGUOUSLY DEFINED error.
Previous Topic: SQL query required (Merged)
Next Topic: SUM in WHERE clause
Goto Forum:
  


Current Time: Sun Dec 04 22:59:23 CST 2016

Total time taken to generate the page: 0.08638 seconds