Home » SQL & PL/SQL » SQL & PL/SQL » Selecting with Condition (Oracle 9.2)
Selecting with Condition [message #353556] Tue, 14 October 2008 03:04 Go to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Hi All . I am doing a Select Statement but need to sum a Qty where UOM = '6' and again where UOM='3' I tried 2 ways and non work. Please piont me in the right direction.


1.
----------------------------------------------------------------
Select
distinct(EDITWHO)as PICKER ,
count(Distinct (CASEID))as CASEID_COUNT,
count(CASEID)as LINE_COUNT,
TO_CHAR(SUM((UOMQTY)where taskdetail.UOM='6')) "Eaches",
TO_CHAR(SUM((UOMQTY)where taskdetail.UOM='3')) "Innerpacks",

from taskdetail
where Status >= '5'
group by editwho
order by count(Distinct (CASEID)) desc
------------------------------------------------------------
2.---------------------------------------------

Select
distinct(EDITWHO)as PICKER ,
count(Distinct (CASEID))as CASEID_COUNT,
count(CASEID)as LINE_COUNT,
(SUM((UOMQTY)where taskdetail.UOM='6')) as Eaches,
(SUM((UOMQTY)where taskdetail.UOM='3')) as Innerpacks
from taskdetail
where Status >= '5'
group by editwho
order by count(Distinct (CASEID)) desc

------------------------------------------------------------
Please Help. I get error on
SUM((UOMQTY)where taskdetail.UOM='6')) as Eaches,
SUM((UOMQTY)where taskdetail.UOM='3')) as Innerpacks



Re: Selecting with Condition [message #353557 is a reply to message #353556] Tue, 14 October 2008 03:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check out the docs for the CASE construct.
icon14.gif  Re: Selecting with Condition [message #353561 is a reply to message #353556] Tue, 14 October 2008 03:20 Go to previous messageGo to next message
saswatm
Messages: 2
Registered: June 2008
Junior Member
You can try DECODE as well to do the sum based on the value of UOM.
Re: Selecting with Condition [message #353576 is a reply to message #353556] Tue, 14 October 2008 04:08 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Thanks you two. The case seems to be easier for me. This is what I got.

Select
distinct(EDITWHO)as PICKER ,
count(Distinct (CASEID))as "CASEID COUNT",
count(CASEID)as "LINE COUNT",
SUM(CASE WHEN UOM='6' then UOMQTY else 0 END )"EACHES" ,
SUM(CASE WHEN UOM='3' then UOMQTY else 0 END )"INNERPACK",
SUM(CASE WHEN UOM='2' then UOMQTY else 0 END )"CASES" ,
SUM(UOMQTY)"TOTAL"
from taskdetail
where Status >= '5'
and TO_CHAR(endtime,'DD-MM-YYYY')=TO_CHAR(sysdate, 'DD-MM-YYYY')
group by editwho
order by count(Distinct (CASEID)) desc
Re: Selecting with Condition [message #353578 is a reply to message #353556] Tue, 14 October 2008 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Selecting with Condition [message #353614 is a reply to message #353576] Tue, 14 October 2008 05:53 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
I am looking to loop this do do a history o seven days.

I tried

DECLARE
i PLS_INTEGER := 1;
BEGIN
LOOP
i := i - 1;



IF i < -7 THEN


Select
distinct(EDITWHO)as "PICKER FOR TODAY" ,
count(Distinct (CASEID))as "CASEID COUNT FOR TODAY",
count(CASEID)as "LINE COUNT FOR TODAY",
SUM(CASE WHEN UOM='6' then UOMQTY else 0 END )"EACHES FOR TODAY" ,
SUM(CASE WHEN UOM='3' then UOMQTY else 0 END )"INNERPACK FOR TODAY",
SUM(CASE WHEN UOM='2' then UOMQTY else 0 END )"CASES FOR TODAY" ,
SUM(UOMQTY)"TOTAL FOR TODAY"
from taskdetail
where Status >= '5'
and tasktype = 'PK'
and TO_CHAR(endtime ,'DD-MM-YYYY')=TO_CHAR(sysdate -i , 'DD-MM-YYYY')
group by editwho
order by SUM(UOMQTY) desc
EXIT;
END IF;
END LOOP;
COMMIT;
END;

But get som select into expected


Please Help
Re: Selecting with Condition [message #353623 is a reply to message #353614] Tue, 14 October 2008 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 14 October 2008 11:09
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel


Re: Selecting with Condition [message #353626 is a reply to message #353556] Tue, 14 October 2008 06:28 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> But get som select into expected
Why do you not exactly report the error?
SQL> begin
  2    select 1 from dual;
  3  end;
  4  /
  select 1 from dual;
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00428: an INTO clause is expected in this SELECT statement

For further investigation: note that PL/SQL is different from SQL. It is thoroughly described in the documentation, found e.g. online on http://tahiti.oracle.com/. Concentrate on PL/SQL User's Guide and Reference.
As you posted the Oracle version, this link could interest you: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems45.htm#36067.

By the way, what are you planning to do with the result of this query?
Re: Selecting with Condition [message #353632 is a reply to message #353626] Tue, 14 October 2008 07:21 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Sorry About not giving the full error.

Currently I Display the results for today. I wanna Diplay Results for the last 7 Days. Will a Loop work for this.
Re: Selecting with Condition [message #353643 is a reply to message #353632] Tue, 14 October 2008 07:56 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Deon Smit,

Deon Smit wrote on Tue, 14 October 2008 17:51
Currently I Display the results for today. I wanna Diplay Results for the last 7 Days. Will a Loop work for this.

I guess this is the condition you use for that.
TO_CHAR(endtime,'DD-MM-YYYY')=TO_CHAR(sysdate, 'DD-MM-YYYY')

I think you can achieve this through normal SQL only.
Try BETWEEN Condition.

Also, I think you might have to add the column endtime in your GROUP BY clause if you want to group the results according to each day.

Hope this helps.
Regards,
Jo
Previous Topic: REF CURSORS using multiple cursors
Next Topic: declare variable to show date and tim in my parameter
Goto Forum:
  


Current Time: Sun Dec 04 06:32:41 CST 2016

Total time taken to generate the page: 0.04222 seconds