Home » SQL & PL/SQL » SQL & PL/SQL » SQL query not running
SQL query not running [message #194435] Fri, 22 September 2006 04:19 Go to next message
rp_in
Messages: 5
Registered: July 2006
Junior Member
I am having trouble getting the following sql to work.
Can anyone see what could be wrong with the following statement:

CASE
WHEN MAP_TRANSPORT_COMPANY.VEHICLE_ID IN ('002_615','002_899','005_615','005_899') THEN 0

WHEN SUM(SALES_INV_FACT_DATA.IV_NET_WEIGHT_KG) < 0 THEN -1*count(distinct SALES_INV_FACT_DATA.INV_NUM)

WHEN SUM(SALES_INV_FACT_DATA.IV_NET_WEIGHT_KG) = 0 THEN 0

WHEN SUM(SALES_INV_FACT_DATA.IV_NET_WEIGHT_KG) > 0 THEN count(distinct SALES_INV_FACT_DATA.INV_NUM)
ELSE 0
END



This is the error I get:
DBD,ORA-00937: not a single-group group function state: N/A

Re: SQL query not running [message #194436 is a reply to message #194435] Fri, 22 September 2006 04:22 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

is this the complete SQL??

Sandy
Re: SQL query not running [message #194446 is a reply to message #194435] Fri, 22 September 2006 04:57 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Hi

First of all tell us what you are trying to do.So that we could help you easily.

Regards,
Marlon.
Re: SQL query not running [message #194453 is a reply to message #194446] Fri, 22 September 2006 05:22 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
select CASE 
WHEN MAP_TRANSPORT_COMPANY.VEHICLE_ID IN ('002_615','002_899','005_615','005_899') THEN 0 

WHEN SUM(SALES_INV_FACT_DATA.IV_NET_WEIGHT_KG) < 0 THEN -1*count(distinct SALES_INV_FACT_DATA.INV_NUM) 

WHEN SUM(SALES_INV_FACT_DATA.IV_NET_WEIGHT_KG) = 0 THEN 0 

WHEN SUM(SALES_INV_FACT_DATA.IV_NET_WEIGHT_KG) > 0 THEN count(distinct SALES_INV_FACT_DATA.INV_NUM) 
ELSE 0 
group by SALES_INV_FACT_DATA.INV_NUM,MAP_TRANSPORT_COMPANY.VEHICLE_ID 

END 


note:untested code


i think this should help you figure out whats wrong with your statement.
Re: SQL query not running [message #194456 is a reply to message #194435] Fri, 22 September 2006 05:25 Go to previous messageGo to next message
rp_in
Messages: 5
Registered: July 2006
Junior Member
actually this is the case condition in one of the object that i'm using at the universe level (Business Objects)

Just to add my latest understanding about this issue:

only the first sql statement is throwing en error when it is combined with 2nd or any of the rest sql statements.


1st sql statement is:

WHEN MAP_TRANSPORT_COMPANY.VEHICLE_ID IN ('002_615','002_899','005_615','005_899') THEN 0


Re: SQL query not running [message #194465 is a reply to message #194456] Fri, 22 September 2006 05:37 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
basically you need to understand one thing.you need to know about group functions.since you have used count please do a study in group functions.


and try executing this statement alone
select case WHEN MAP_TRANSPORT_COMPANY.VEHICLE_ID IN ('002_615','002_899','005_615','005_899') THEN 0 else end from <table Name>
Previous Topic: sum of the range
Next Topic: ORA Error
Goto Forum:
  


Current Time: Tue Dec 06 10:20:19 CST 2016

Total time taken to generate the page: 0.06453 seconds