Home » SQL & PL/SQL » SQL & PL/SQL » SQL CASE statement (revised to follow board rules)
SQL CASE statement (revised to follow board rules) [message #231635] Tue, 17 April 2007 15:35 Go to next message
dimples0109
Messages: 6
Registered: March 2007
Junior Member
I have the following query. If I run each when as seperate case statements, they work, but I want to run it as one case so they will print on one line.

ERROR MESSAGE: Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> @delq_stats3.sql
when ucrcrhs_ccat_code = 'DEL' then count(*) as dlq_count
*
ERROR at line 5:
ORA-00905: missing keyword
.

I have googled the syntax for CASE and it appears to be right. help is appreciated.

here is what I want, for each uibmaps code, give count of 4 types all on one row.


code

spool delq_statsq4_2006.ls
set linesize 375
select
uibmaps_column4,
case ucrcrhs_ccat_code
when ucrcrhs_ccat_code = 'DEL' then count(*) as dlq_count
when ucrcrhs_ccat_code = 'FLNT' then count(*)as FLNT_count
when ucrcrhs_ccat_code = 'TRM' then count (*)as TRM_count
when ucrcrhs_ccat_code = 'DTAG' then count(*)as DTAG_count
else ucrcrhs_ccat_code = 'hhuu'
from uibmaps,ucrcrhs
where
ucrcrhs_prem_code = uibmaps_code
and ucrcrhs_ccat_code in ('DEL', 'FLNT', 'TRM', 'DTAG')
and (UCRCRHS_ACTIVITY_DATE between '01-Oct-2006' and '31-Dec-2006'
or UCRCRHS_OCCURANCE_DATE between '01-Oct-2006' and '31-Dec-2006')
group by uibmaps_column4, ucrcrhs_ccat_code;
spool off
exit

[Updated on: Tue, 17 April 2007 15:47]

Report message to a moderator

Re: help with case [message #231637 is a reply to message #231635] Tue, 17 April 2007 15:41 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You should read & FOLLOW the #1 STICKY post by using "code tags".
Plus do actual CUT & PASTE rather than describe what you think is happening.
Re: SQL CASE statement (revised to follow board rules) [message #231640 is a reply to message #231635] Tue, 17 April 2007 15:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your case syntax is not correct. Recheck it.
You mixed
CASE X when Y then
and
CASE when X=Y then

[Updated on: Tue, 17 April 2007 15:59]

Report message to a moderator

Re: SQL CASE statement (revised to follow board rules) [message #231679 is a reply to message #231635] Wed, 18 April 2007 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not only the syntax is incorrect but I bet the query will not do what you think it'd do.

Regards
Michel
Re: SQL CASE statement (revised to follow board rules) [message #231795 is a reply to message #231679] Wed, 18 April 2007 07:46 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Not only the syntax is incorrect and will not do what you think it'd do, but you are comparing DATEs to character strings. Next...
Re: SQL CASE statement (revised to follow board rules) [message #231800 is a reply to message #231795] Wed, 18 April 2007 07:58 Go to previous messageGo to next message
dimples0109
Messages: 6
Registered: March 2007
Junior Member
thanks for all the useful information Mad
Re: SQL CASE statement (revised to follow board rules) [message #231811 is a reply to message #231800] Wed, 18 April 2007 08:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
dimples0109 wrote on Wed, 18 April 2007 14:58
thanks for all the useful information Mad

What reason do YOU have to look so angry? We encounter encryptic posts like these every 10 minutes or so. What do think we feel like when we see another member failing to add simple [CODE] and [/CODE] tags around his code.

From the famous sticky(<-- READ THIS!!!):
sticky

Post any code with the formatting tags:

[code]
Enter your code here.
[/code]
You can also paste the code in your message, select it and hit this button http://www.orafaq.com/forum/theme/orafaq/images/b_code.gif You can also make use of the formatter page of this site. It can be found here: http://www.orafaq.com/utilities/sqlformatter.htm . Just copy your code into the page hit "Format code" and copy the formatted code to your message.


sticky
And as a rule of thumb: be polite. No one is paid for answering your question. The least you can do is show some respect to those who are willing to dedicate some of their spare time to look at your problem.


As for your initial message:
- your case statement is WRONG.
- when you compare date values with literals use TO_DATE('yourdate','yourdateformat') otherwise it will fail one day or another.

How's something like this?
SELECT   uibmaps_column4
       , COUNT (CASE ucrcrhs_ccat_code
                   WHEN 'DEL'
                      THEN 1
                   ELSE 0
                END) AS dlq_count
       , COUNT (CASE ucrcrhs_ccat_code
                   WHEN 'FLNT'
                      THEN 1
                   ELSE 0
                END) AS flnt_count
       , COUNT (CASE ucrcrhs_ccat_code
                   WHEN 'TRM'
                      THEN 1
                   ELSE 0
                END) AS trm_count
       , COUNT (CASE ucrcrhs_ccat_code
                   WHEN 'DTAG'
                      THEN 1
                   ELSE 0
                END) AS dtag_count
FROM     uibmaps
       , ucrcrhs
WHERE    ucrcrhs_prem_code = uibmaps_code
AND      ucrcrhs_ccat_code IN ('DEL', 'FLNT', 'TRM', 'DTAG')
AND      (   ucrcrhs_activity_date BETWEEN TO_DATE ('01-Oct-2006'
                                                  , 'dd-Mon-yyyy'
                                                   )
                                       AND TO_DATE ('31-Dec-2006'
                                                  , 'dd-Mon-yyyy'
                                                   )
          OR ucrcrhs_occurance_date BETWEEN TO_DATE ('01-Oct-2006'
                                                   , 'dd-Mon-yyyy'
                                                    )
                                        AND TO_DATE ('31-Dec-2006'
                                                   , 'dd-Mon-yyyy'
                                                    )
         )
GROUP BY uibmaps_column4
       , ucrcrhs_ccat_code;


I didn't feel like testing it, so I can't guarantee whether it'll work.

MHE

[Updated on: Wed, 18 April 2007 08:17]

Report message to a moderator

Re: SQL CASE statement (revised to follow board rules) [message #231814 is a reply to message #231811] Wed, 18 April 2007 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten,

I think you meant SUM instead of COUNT, or you should not give an ELSE part of your CASEs.

Regards
Michel
Re: SQL CASE statement (revised to follow board rules) [message #231818 is a reply to message #231814] Wed, 18 April 2007 08:58 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Wed, 18 April 2007 15:26
Maarten,

I think you meant SUM instead of COUNT, or you should not give an ELSE part of your CASEs.

Regards
Michel


Very Happy Untested, like I said. But you're right...as usual.

MHE
Previous Topic: peak Off Peak
Next Topic: challenging question can any one can solve it and help me
Goto Forum:
  


Current Time: Sat Dec 03 09:48:45 CST 2016

Total time taken to generate the page: 0.06082 seconds