4 Replies Latest reply: Feb 20, 2013 2:10 PM by Frank Kulash RSS

    Spool file format to be changed

    992298
      Hi Guys,

      This really urgent for me, Please help.

      Issue description :

      I am basically creating a spool file in a particular format. Here is my SQL Content.
      ==================
      set colsep ,
      set pagesize 0
      set trimspool on
      set headsep on
      set heading on
      set feedback off
      set echo off
      spool FFCS.csv
      set linesize 10000

      select chr(10)||'Events'||chr(10) from dual;

      select 'EVENT_ID','EVENT_TITLE' from dual;

      select
      activity_uid,
      name,
      from
      s_evt_Act evt
      =================
      So if my SQL is returning one rows then it would be like

      Output:-

      Event

      EVENT_ID EVENT_TITLE
      1-23-45 Reps Meet


      So if my SQL is returning more than one rows then it would be like

      Output:-

      Event

      EVENT_ID EVENT_TITLE
      1-23-45 Reps Meet
      1-245-45 TBMs Meet

      but what i need is, if there are more than one records, it should display as follows.
      Output:-

      Event

      EVENT_ID EVENT_TITLE
      1-23-45 Reps Meet

      Event

      EVENT_ID EVENT_TITLE
      1-245-45 TBMs Meet

      Please help. I heard its possible with cursors but i not sure. please help..

      Edited by: 989295 on Feb 20, 2013 11:09 AM
        • 1. Re: Spool file format to be changed
          Frank Kulash
          Hi,

          Welcome to the forum!

          Here's one way:
          SET     COLSEP          ,
          -- ...
          --  All of your SET commands (except "HEADING ON" and "PAGESIZE 0") can go here
          --  Actually, it doesn't matter if you include them, since they will be overridden below:
          
          SET     HEADING          OFF
          SET     PAGESIZE     5
          
          TTITLE     LEFT "Event"     SKIP 2          LEFT "EVENT_ID EVENT_TITLE"
          
          SPOOL  ffcf.csv
          
          
          SELECT       deptno     AS event_id
          ,       dname          AS event_title
          FROM       scott.dept
          WHERE       deptno     <= 30
          ORDER BY  deptno
          ;
          
          
          SPOOL  OFF
          I don't have a copy of your table, so I used scott.dept for testing.

          Formatting of output is best left to your front end. You could do the formatting in SQL if you really wanted to, but there's no reason to in this case.



          Edited by: Frank Kulash on Feb 20, 2013 2:25 PM
          • 2. Re: Spool file format to be changed
            Solomon Yakobson
            A hint:
            SQL> set pagesize 0
            SQL> select  'Department' || chr(10) || chr(10) ||
              2          rpad('DNAME',15) || 'LOC' || chr(10) ||
              3          rpad(dname,15) || loc
              4    from  dept
              5  /
            Department
            
            DNAME          LOC
            ACCOUNTING     NEW YORK
            
            Department
            
            DNAME          LOC
            RESEARCH       DALLAS
            
            Department
            
            DNAME          LOC
            SALES          CHICAGO
            
            Department
            
            DNAME          LOC
            OPERATIONS     BOSTON
            
            
            SQL>   
            SY.

            Edited by: Solomon Yakobson on Feb 20, 2013 2:22 PM
            • 3. Re: Spool file format to be changed
              992298
              Thanks for your reply.

              I got your point.

              But i have a catch here.

              That SKIP =2 can not be a constant as ii have one more complexity.

              after spooling every row i am firing one more SQL in between depending upon the value of the Event.

              See below the example.

              Event

              EVENT_ID EVENT_TITLE
              1-23-45 Reps Meet

              EVENT_ID Product_ID Product_Name
              1-23-45 26-23-45 Capsules

              Event

              EVENT_ID EVENT_TITLE
              5-23-45 ABMs Meet

              EVENT_ID Product_ID Product_Name
              5-23-45 26-23-45 Capsules
              5-23-45 51-63-45 Tablets
              • 4. Re: Spool file format to be changed
                Frank Kulash
                Hi,
                989295 wrote:
                Thanks for your reply.

                I got your point.

                But i have a catch here.

                That SKIP =2 can not be a constant as ii have one more complexity.

                after spooling every row i am firing one more SQL in between depending upon the value of the Event.

                See below the example.

                Event

                EVENT_ID EVENT_TITLE
                1-23-45 Reps Meet

                EVENT_ID Product_ID Product_Name
                1-23-45 26-23-45 Capsules

                Event

                EVENT_ID EVENT_TITLE
                5-23-45 ABMs Meet

                EVENT_ID Product_ID Product_Name
                5-23-45 26-23-45 Capsules
                5-23-45 51-63-45 Tablets
                I see; sometimes there will be "EVENT_ID EVENT_TITLE" blocks under a single "Event" heading, and sometimes there will be more than 1 row of data in the "EVENT_ID EVENT_TITLE" block. I don't think SQL*Plus can handle that, so you probably will need SQL, perhaps something like this:
                ...
                SET     HEADING          OFF
                SET     PAGESIZE     0
                SET     RECSEP          OFF
                
                SPOOL   c.csv
                
                
                WITH     got_nums     AS
                (
                     SELECT       empno
                     ,       ename
                     ,       job
                     ,       deptno
                     ,       ROW_NUMBER () over ( PARTITION BY  job
                                                  ORDER BY          ename
                                         ) AS j_num
                     ,       ROW_NUMBER () over ( PARTITION BY  job, deptno
                                                  ORDER BY          ename
                                         ) AS d_num
                     FROM      scott.emp
                )
                SELECT       CASE
                           WHEN  j_num = 1
                           THEN  CHR (10) || 'Event' || CHR (10) 
                       END
                ||       CASE
                           WHEN  d_num = 1
                           THEN  CHR (10) || 'EVENT_ID EVENT_TITLE' || CHR (10)
                       END
                ||       empno
                ||','||       ename
                FROM       got_nums
                ORDER BY  job, deptno
                ;
                
                
                SPOOL  OFF
                The query above only produces 1 column of output, so it doesn't matter what COLSEP is. Notice how I manually put a comma between the empno and ename values, at the end of the SELECT clause. You don't have to do this; it makes the file harder for humans to read, because it's much smaller.


                 

                I hope this answers your question.
                If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
                Explain, using specific examples, how you get those results from that data. Describe when you want the "Event" and "EVENT_ID EVENT TITLE" headers to appear, and point out examples in your sample data and output.
                Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
                See the forum FAQ {message:id=9360002}