This discussion is archived
7 Replies Latest reply: Feb 21, 2013 10:09 PM by user10088255 RSS

Comma delimited in Sql query decode function errors out

user10088255 Newbie
Currently Being Moderated
Hi All,

DB: 11.2.0.3.0


I am using the below query to generate the comma delimited output in a spool file but it errors out with the message below:

SQL> set lines 100 pages 50
SQL> col "USER_CONCURRENT_QUEUE_NAME" format a40;
SQL> set head off
SQL> spool /home/xyz/cmrequests.csv
SQL> SELECT
2 a.USER_CONCURRENT_QUEUE_NAME || ','
3 || a.MAX_PROCESSES || ','
4 || sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby ||','
5 ||sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal ||','
6 ||sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal
7 from FND_CONCURRENT_QUEUES_VL a, FND_CONCURRENT_WORKER_REQUESTS b
where a.concurrent_queue_id = b.concurrent_queue_id AND b.Requested_Start_Date <= SYSDATE
8 9 GROUP BY a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES;
|| sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby ||','
*
ERROR at line 4:
ORA-00923: FROM keyword not found where expected


SQL> spool off;
SQL>


Expected output in the spool /home/xyz/cmrequests.csv

Standard Manager,10,0,1,0


Thanks for your time!

Regards,
  • 1. Re: Comma delimited in Sql query decode function errors out
    sb92075 Guru
    Currently Being Moderated
    Handle:     user10088255
    Status Level:     Newbie
    Registered:     Mar 4, 2009
    Total Posts:     187
    Total Questions:     123 (122 unresolved)

    why do you waste our time & your time here since you rarely get your questions answered?

    SQL> SET COLSEP ,
  • 2. Re: Comma delimited in Sql query decode function errors out
    rp0428 Guru
    Currently Being Moderated
    Get to work immediately on marking your previous questions ANSWERED if they have been!
    >
    I am using the below query to generate the comma delimited output in a spool file but it errors out with the message below:

    SQL> set lines 100 pages 50
    SQL> col "USER_CONCURRENT_QUEUE_NAME" format a40;
    SQL> set head off
    SQL> spool /home/xyz/cmrequests.csv
    SQL> SELECT
    2 a.USER_CONCURRENT_QUEUE_NAME || ','
    3 || a.MAX_PROCESSES || ','
    4 || sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby ||','
    5 ||sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal ||','
    6 ||sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal
    7 from FND_CONCURRENT_QUEUES_VL a, FND_CONCURRENT_WORKER_REQUESTS b
    where a.concurrent_queue_id = b.concurrent_queue_id AND b.Requested_Start_Date <= SYSDATE
    8 9 GROUP BY a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES;
    || sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby ||','
    *
    >
    Well if you want to spool query results to a file the first thing you need to do is write a query that actually works.

    Why do you think a query like this is valid?
    SELECT 'this, is, my, giant, string, of, columns, with, commas, in, between, each, word'
    GROUP BY this, is, my, giant, string
    You only have one column in the result set but you are trying to group by three columns and none of them are even in the result set.

    What's up with that?

    You can only group by columns that are actually IN the result set.
  • 3. Re: Comma delimited in Sql query decode function errors out
    516744 Newbie
    Currently Being Moderated
    Try below query

    SELECT USER_CONCURRENT_QUEUE_NAME||','||MAX_PROCESSES||','|| Pending_Standby||','||Pending_Normal ||','||Running_Normal
    FROM (
    SELECT a.USER_CONCURRENT_QUEUE_NAME ,
    a.MAX_PROCESSES ,
    SUM(DECODE(b.PHASE_CODE,'P',DECODE(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby ,
    SUM(DECODE(b.PHASE_CODE,'P',DECODE(b.STATUS_CODE,'I',1,0),0)) Pending_Normal ,
    SUM(DECODE(b.PHASE_CODE,'R',DECODE(b.STATUS_CODE,'R',1,0),0)) Running_Normal
    FROM FND_CONCURRENT_QUEUES_VL a,
    FND_CONCURRENT_WORKER_REQUESTS b
    WHERE a.concurrent_queue_id = b.concurrent_queue_id
    AND b.Requested_Start_Date <= SYSDATE
    GROUP BY a.USER_CONCURRENT_QUEUE_NAME a.MAX_PROCESSES);
  • 4. Re: Comma delimited in Sql query decode function errors out
    user10088255 Newbie
    Currently Being Moderated
    Let me clarify few things here

    The result set mentioned earlier with one example may have been a typo

    The expected result set is an example that will get more than one output as seen below from the same query:

    Used "set colsep ,;"

    SQL> set lines 100 pages 50
    SQL> col "USER_CONCURRENT_QUEUE_NAME" format a40;
    SQL> set head off
    SQL> spool /home/xyz/xyzreq.csv
    SQL> set colsep,;
    SQL> SELECT a.USER_CONCURRENT_QUEUE_NAME,
    a.MAX_PROCESSES,
    2 3 sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby,
    4 sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal,
    5 sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal
    6 from FND_CONCURRENT_QUEUES_VL a, FND_CONCURRENT_WORKER_REQUESTS b
    where a.concurrent_queue_id = b.concurrent_queue_id AND b.Requested_Start_Date <= SYSDATE
    7 8 GROUP BY a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES;

    XYZ FastQueue , 1, 0, 1, 0
    Standard Manager , 10, 0, 1, 0
    XYZ SlowQueue , 0, 0, 1, 0

    SQL> spool off;

    SQL> /

    Standard Manager , 10, 0, 0, 1


    Anyways, thanks for your time for answering!

    Lastly, this forum is not a waste but for sharing knowledge where issue are discussed and shared!
  • 5. Re: Comma delimited in Sql query decode function errors out
    rp0428 Guru
    Currently Being Moderated
    It's a waste when you won't be a good user of the forums and mark your questions ANSWERED when they have been. That wastes peoples time that read the thread and want to help only to find out that the answer has already been provided.

    It also makes them not want to help you in the future. Again, I suggest you revisit your old threads and clean them up by marking them ANSWERED.
  • 7. Re: Comma delimited in Sql query decode function errors out
    user10088255 Newbie
    Currently Being Moderated
    Hi rp0428 and all users of this thread,

    Thanks for taking out your time to help answer the question!

    I agree with your points on this thread and will update with all the other questions as answered

    Thanks once again!

    Regards,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points