Author |
Topic |
chulz90
Starting Member
26 Posts |
Posted - 2013-06-19 : 23:43:17
|
hello guys i've some problems in my query.when i write this query , i've got the right resultselect distinct(eq_status)from rspi_equipmentwhere eq_status in('B','TU','R')when i write this query, i've got the right reult tooselect eq_status, count(to_number(eq_qty))from rspi_equipmentwhere it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')group by eq_statusnotes: in location 9069 for item 02007003 there's only two status, which is 'B' and 'TU'. So the QTY for item 02007003 whith 'B' status is 7 and 'TU' status is 3but when i combine that query , there's an error..here the queryselect (select distinct(eq_status)from rspi_equipmentwhere eq_status in('B','TU','R')), count(NVL(to_number(eq_qty),0))from rspi_equipmentwhere it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')group by eq_status;the result that what i want is, if item 02007003 didn't have 'R' status it's count 0 .can you help me to fix my query???sorry for bad englishthank's for helping |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 00:04:50
|
Why do you want sub queries?-- it is enough... right?select eq_status, count(NVL(to_number(eq_qty),0))from rspi_equipmentwhere it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')group by eq_status;NOTE: I think you are using Oracle/DB2.... This forum is for SQL Server....--Chandu |
|
|
chulz90
Starting Member
26 Posts |
Posted - 2013-06-20 : 00:11:52
|
quote: Originally posted by bandi Why do you want sub queries?-- it is enough... right?select eq_status, count(NVL(to_number(eq_qty),0))from rspi_equipmentwhere it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')group by eq_status;NOTE: I think you are using Oracle/DB2.... This forum is for SQL Server....--Chandu
yes i'am. i'm using pl/sql.in that forum there's no respon |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 00:17:33
|
quote: Originally posted by chulz90yes i'am. i'm using pl/sql.in that forum there's no respon
ok.. what about the result for above query?--Chandu |
|
|
chulz90
Starting Member
26 Posts |
Posted - 2013-06-20 : 00:25:20
|
quote: Originally posted by bandi
quote: Originally posted by chulz90yes i'am. i'm using pl/sql.in that forum there's no respon
ok.. what about the result for above query?--Chandu
the result is Status B = 7 and TU = 3 , there's no status R , i want to show if there's no status R but it still show with zero result.but the result only two status B and TU only |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 00:36:33
|
select eq_status, count(CASE WHEN it_itemnum='02007003' and loc_locationid =9069 THEN to_number(eq_qty) ELSE 0 END) AS Quantityfrom rspi_equipmentwhere eq_status in('B','TU','R')group by eq_status;--Chandu |
|
|
chulz90
Starting Member
26 Posts |
Posted - 2013-06-20 : 04:35:31
|
quote: Originally posted by bandi select eq_status, count(CASE WHEN it_itemnum='02007003' and loc_locationid =9069 THEN to_number(eq_qty) ELSE 0 END) AS Quantityfrom rspi_equipmentwhere eq_status in('B','TU','R')group by eq_status;--Chandu
thanks bandi for helping, but query above have same result with this query belowselect eq_status, count(eq_qty) from rspi_equipmentwhere eq_status in('B','TU','R') |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 04:50:56
|
Do you have atleast one eq_status as 'R' in rspi_equipment table?I think you want the SUM of quantity, not the COUNTCan you post us the sample data and output for that data?--Chandu |
|
|
|