[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

Problems with SQL Statement Output

I am having a number of problems with some queries that I am running from inside a UNIX Shell script.

I have tried various things but can not see to get this to work with out manually fixing the files before sending them to opt out.  This is fine during our testing period, however we are going live in a couple of weeks and I need to get this problem resolved.  

I have detailed below what I need and what I am getting.  I have tried many different things but can not get this to work correctly.  I have six of these queries that I need to run, one right after the other.  

I was thinking that I could use TTITLE for the header information instead of running from Dual.  But I can not get ttitle to work correctly.  When I added ttitle I had the following command:

ttitle 'Siebel_CVP, RCI, Phone, Inquiry'

It could be that I do not have it in the correct place and the title will change for each of the different queries that run.  Same as the header will change after each of the queries that are run.

Any information would be greatly appreciated.  I will be out of the office starting at noon today but will be checking my email frequently today but not tomorrow.

All the information you need is below.

I am sending out CSV files.  The first row in the file needs to be a header record for them:

The header will be something similar to this:

Siebel_CVP, RCI, Phone, Inquiry.

Yes, I know the exact same information is in the File Name, but they are telling me that they Must have a Header Record.

The Detail Part of the file looks like the following:

CHAN_ADDR ,BRA,CHANN,SRC_ID               ,EBR_SOURCE             ,EBR_TYP
----------,---,-----,---------------------,-----------------------,-------
5166500841,RCI,Phone,Prospect - 8494019   ,CTI                    ,Inquiry
5613925966,RCI,Phone,Prospect - 1-1KR-1196,CTI                    ,Inquiry
8153631180,RCI,Phone,Prospect - 1-3IEZ-254,CTI_HOTWEB             ,Inquiry
8154554979,RCI,Phone,Prospect - 9085280   ,CTI                    ,Inquiry
9723968877,RCI,Phone,Prospect - 9084816   ,CTI_PRE_SHOPPING_CALL  ,Inquiry

I have created a query that gives me the information above.  However, when it outputs the file it also has the SQL query information at the top of the file.  I have tried several things.

1).  I have tried running the query first then running the spool at the bottom, when I do this I get just my detail records but not the Header Record.
2.)  I have tried changing the order of my sets put this is not working either.

When I run my query I am getting the following result set:

SQL> Select
  2      'Siebel CVP' as Source_Name,
  3   'RCI' AS Brand,
  4   'Phone' AS Channel,
  5      'Inquiry' AS EBR_Type
  6  FROM dual;
SOURCE_NAM,BRA,CHANN,EBR_TYP
----------,---,-----,-------
Siebel CVP,RCI,Phone,Inquiry

1 row selected.

SQL> Select
  2       substr(con_home_ph_num,1,10) AS Chan_Addr,
  3       substr(x_rccl_brand,1,3) AS Brand,
  4      'Phone' AS Channel,
  5       substr(con_person_uid,1,21) AS Src_Id,
  6       substr(x_rccl_source,1,23) AS EBR_source,
  7      'Inquiry' AS EBR_Type
  8  FROM
  9  siebel.eim_contact
 10  WHERE
 11     con_home_ph_num is not null and
 12     con_home_ph_num > '201'
 13     and length (con_home_ph_num) = '10'
 14     and con_home_ph_num <> '0'
 15     and con_home_ph_num <> '1111111111'
 16     and con_home_ph_num <> '9999999999'
 17     and con_home_ph_num <> '0000000000'
 18     and con_home_ph_num NOT LIKE '+%'
 19     and con_home_ph_num NOT LIKE '0%'
 20     and con_home_ph_num NOT LIKE '123%'
 21     and con_home_ph_num <> '2222222222'
 22     and x_rccl_call_type is null
 23     and x_rccl_brand = 'RCI'
 24     and TO_DATE(TO_CHAR(x_rccl_entry_date, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY')
 25     and x_rccl_source IN ('CTI','CTI_GVS','CTI_HOTWEB','CTI_PRE_SHOPPING_CALL')
 26     ORDER BY
 27     con_home_ph_num asc
 28  /
CHAN_ADDR ,BRA,CHANN,SRC_ID               ,EBR_SOURCE             ,EBR_TYP
----------,---,-----,---------------------,-----------------------,-------
6269140432,RCI,Phone,Prospect - 9063231   ,CTI_PRE_SHOPPING_CALL  ,Inquiry
6319791332,RCI,Phone,Prospect - 9063598   ,CTI_HOTWEB             ,Inquiry
6627811912,RCI,Phone,Prospect - 9063233   ,CTI_PRE_SHOPPING_CALL  ,Inquiry
8153631180,RCI,Phone,Prospect - 1-3IEZ-254,CTI_HOTWEB             ,Inquiry

4 rows selected.

SQL> spool off

Here is the query that I am running:

set echo off
set head on
set feed on
set colsep','
set lin 150
set recsep wr
set trims on
set term off
set verify off
set pages 0 emb on
spool c:\Siebel_CVP_RCI_Phone.csv  -- File Name must be present
Select
    'Siebel CVP' as Source_Name,  -- This creates the header line which Opt Out Must Have
      'RCI' AS Brand,
      'Phone' AS Channel,
    'Inquiry' AS EBR_Type  
FROM dual;
Select
     substr(con_home_ph_num,1,10) AS Chan_Addr,
     substr(x_rccl_brand,1,3) AS Brand,
    'Phone' AS Channel,
     substr(con_person_uid,1,21) AS Src_Id,
     substr(x_rccl_source,1,23) AS EBR_source,
    'Inquiry' AS EBR_Type
FROM
siebel.eim_contact
--s_db_view_s_contact
WHERE
   con_home_ph_num is not null and
   con_home_ph_num > '201'
   and length (con_home_ph_num) = '10'
   and con_home_ph_num <> '0'
   and con_home_ph_num <> '1111111111'
   and con_home_ph_num <> '9999999999'
   and con_home_ph_num <> '0000000000'
   and con_home_ph_num NOT LIKE '+%'
   and con_home_ph_num NOT LIKE '0%'
   and con_home_ph_num NOT LIKE '123%'
   and con_home_ph_num <> '2222222222'
   and x_rccl_call_type is null
   and x_rccl_brand = 'RCI'
   and TO_DATE(TO_CHAR(x_rccl_entry_date, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY')
   and x_rccl_source IN ('CTI','CTI_GVS','CTI_HOTWEB','CTI_PRE_SHOPPING_CALL')
   ORDER BY
   con_home_ph_num asc
0
gschoser
Asked:
gschoser
1 Solution
 
virtual_voidCommented:
You need to turn the feedback off as well as the head off ( however if you want to only put head on for the second query you can do so)
start sqlplus in the silent mode sqlplus -s so that you don't get the queries in your output.
_____________________________________________________________________________

You can make the following changes:
cat out.sql
set echo off head off feed off colsep',' lin 150 recsep wr trims on
set term off
set verify off
set pages 0 emb on
spool Siebel_CVP_RCI_Phone.csv
Select
    'Siebel CVP' as Source_Name,  -- This creates the header line which Opt Out
Must Have
     'RCI' AS Brand,
     'Phone' AS Channel,
    'Inquiry' AS EBR_Type
FROM dual;
Select
     substr(username,1,10) AS Chan_Addr,
     substr(username,1,3) AS Brand,
    'Phone' AS Channel,
     substr(osuser,1,21) AS Src_Id,
     substr(osuser,1,23) AS EBR_source,
    'Inquiry' AS EBR_Type
FROM
v$session
/

$ cat out.sh
sqlplus -s '/ as sysdba' <<EOF
@out.sql
EOF
view *.csv
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now