OraFAQ Forum: SQL & PL/SQL » case statement giving an error

Home » SQL & PL/SQL » SQL & PL/SQL » case statement giving an error (oracle19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

case statement giving an error [message #689101]

Thu, 21 September 2023 11:44

shrinika
Messages: 306
Registered: April 2008

Senior Member

Hello, the below SQL works absolutely fine. no issues.

All i need is, i need to store the outout in CSV format.

select
merchant_id||','||
sum(case when trim(upper(RULE_VALUE))='NEG-VI-GL-C1' then 1 else 0 end) as "XYZ 12" ,
sum(case when trim(upper(RULE_VALUE))='CVEL-VI-GL-C2-C4' then 1 else 0 end) as "XYZ 14"
from rpt.transaction
where transaction_date >= TRUNC (sysdate-1)
and Reseller_id = 'steranet'
and rownum < 10
group by merchant_id;

But once i added ||','|| I get the below errors.

SQL> select
  2  merchant_id||','||
sum(case when trim(upper(RULE_VALUE))='NEG-VI-GL-C1' then 1 else 0 end) as "XYZ 12" ||','||
sum(case when trim(upper(RULE_VALUE))='CVEL-VI-GL-C2-C4' then 1 else 0 end) as "XYZ 14"
from rpt.transaction
where transaction_date >= TRUNC (sysdate-1)
and Reseller_id = 'steranet'
and rownum < 10
group by merchant_id;
  3    4    5    6    7    8    9  sum(case when trim(upper(RULE_VALUE))='NEG-VI-GL-C1' then 1 else 0 end) as "XYZ 12" ||','||
                                                                                    *
ERROR at line 3:
ORA-00923: FROM keyword not found where expected


SQL>

Report message to a moderator

Re: case statement giving an error [message #689102 is a reply to message #689101]

Thu, 21 September 2023 12:10

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

So, you have something like this:

C##SCOTT@XE_21.3.0.0.0> select deptno as merchant_id,
  2  	    sum(case when trim(upper(job))='CLERK' then 1 else 0 end) as "XYZ 12" ,
  3  	    sum(case when trim(upper(job))='SALESMAN' then 1 else 0 end) as "XYZ 14"
  4  from   emp
  5  where  hiredate <= TRUNC (sysdate-1)
  6  and rownum < 10
  7  group by deptno
  8  /

MERCHANT_ID      XYZ 12      XYZ 14                                             
-----------  ----------  ----------                                             
         20           1           0                                             
         30           0           3                                             
         10           0           0                                             

3 rows selected.

And when you try to concatenate commas for spooling to a csv file, you get something like this:

C##SCOTT@XE_21.3.0.0.0> select deptno as merchant_id || ',' ||
  2  	    sum(case when trim(upper(job))='CLERK' then 1 else 0 end) as "XYZ 12" || ',' ||
  3  	    sum(case when trim(upper(job))='SALESMAN' then 1 else 0 end) as "XYZ 14"
  4  from   emp
  5  where  hiredate <= TRUNC (sysdate-1)
  6  and rownum < 10
  7  group by deptno
  8  /
select deptno as merchant_id || ',' ||
                             *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

One solution would be to use column names without quotes or spaces and concatenate the commas in an outer query like this:

C##SCOTT@XE_21.3.0.0.0> select merchant_id || ',' || XYZ_12 || ',' || XYZ_14
  2  from   (select deptno as merchant_id,
  3  		    sum(case when trim(upper(job))='CLERK' then 1 else 0 end) as XYZ_12 ,
  4  		    sum(case when trim(upper(job))='SALESMAN' then 1 else 0 end) as XYZ_14
  5  	     from   emp
  6  	     where  hiredate <= TRUNC (sysdate-1)
  7  	     and rownum < 10
  8  	     group by deptno)
  9  /

MERCHANT_ID||','||XYZ_12||','||XYZ_14                                           
--------------------------------------------------------------------------------
20,1,0                                                                          
30,0,3                                                                          
10,0,0                                                                          

3 rows selected.

Another solution would be to use your original query without concatenation and set the column separator to a comma, but it will create a lot of extra whitespace, like this:

C##SCOTT@XE_21.3.0.0.0> SET COLSEP ','
C##SCOTT@XE_21.3.0.0.0> select deptno as merchant_id,
  2  	    sum(case when trim(upper(job))='CLERK' then 1 else 0 end) as "XYZ 12" ,
  3  	    sum(case when trim(upper(job))='SALESMAN' then 1 else 0 end) as "XYZ 14"
  4  from   emp
  5  where  hiredate <= TRUNC (sysdate-1)
  6  and rownum < 10
  7  group by deptno
  8  /

MERCHANT_ID,    XYZ 12,    XYZ 14                                               
-----------,----------,----------                                               
         20,         1,         0                                               
         30,         0,         3                                               
         10,         0,         0                                               

3 rows selected.

You will probably also want to include something like:

SET ECHO OFF FEEDBACK OFF HEADING OFF VERIFY OFF

before you spool.

Another option, probably the best, is to setp markup csv on, like this:

##SCOTT@XE_21.3.0.0.0> SET MARKUP CSV ON
C##SCOTT@XE_21.3.0.0.0> select deptno as merchant_id,
  2  	    sum(case when trim(upper(job))='CLERK' then 1 else 0 end) as "XYZ 12" ,
  3  	    sum(case when trim(upper(job))='SALESMAN' then 1 else 0 end) as "XYZ 14"
  4  from   emp
  5  where  hiredate <= TRUNC (sysdate-1)
  6  and rownum < 10
  7  group by deptno
  8  /

"MERCHANT_ID","XYZ 12","XYZ 14"
20,1,0
30,0,3
10,0,0

3 rows selected.

I should mention that the last two options use SQL*Plus set commands that may not be available in whatever you are using.

[Updated on: Thu, 21 September 2023 12:23]

Report message to a moderator

Re: case statement giving an error [message #689114 is a reply to message #689102]

Tue, 26 September 2023 10:26

shrinika
Messages: 306
Registered: April 2008

Senior Member

Thank you so much @Barbara Boehmer

It works great. I really appreciate your time and help!

Report message to a moderator

Previous Topic:	Tuning of the update statement in oracle
Next Topic:	Deleting referenced tables based on child table criteria

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 13:12:12 CDT 2024