7 Replies Latest reply: Feb 22, 2013 12:09 AM by user10088255 RSS

    Comma delimited in Sql query decode function errors out

    user10088255
      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
          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
            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
              Vijay Reddy
              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
                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
                  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
                    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,