gschoser
asked on
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,Prosp ect - 8494019 ,CTI ,Inquiry
5613925966,RCI,Phone,Prosp ect - 1-1KR-1196,CTI ,Inquiry
8153631180,RCI,Phone,Prosp ect - 1-3IEZ-254,CTI_HOTWEB ,Inquiry
8154554979,RCI,Phone,Prosp ect - 9085280 ,CTI ,Inquiry
9723968877,RCI,Phone,Prosp ect - 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_T YP
----------,---,-----,----- --
Siebel CVP,RCI,Phone,Inquiry
1 row selected.
SQL> Select
2 substr(con_home_ph_num,1,1 0) 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_ent ry_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_HOTW EB','CTI_P RE_SHOPPIN G_CALL')
26 ORDER BY
27 con_home_ph_num asc
28 /
CHAN_ADDR ,BRA,CHANN,SRC_ID ,EBR_SOURCE ,EBR_TYP
----------,---,-----,----- ---------- ------,--- ---------- ---------- ,-------
6269140432,RCI,Phone,Prosp ect - 9063231 ,CTI_PRE_SHOPPING_CALL ,Inquiry
6319791332,RCI,Phone,Prosp ect - 9063598 ,CTI_HOTWEB ,Inquiry
6627811912,RCI,Phone,Prosp ect - 9063233 ,CTI_PRE_SHOPPING_CALL ,Inquiry
8153631180,RCI,Phone,Prosp ect - 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.cs v -- 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,1 0) 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_ent ry_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_HOTW EB','CTI_P RE_SHOPPIN G_CALL')
ORDER BY
con_home_ph_num asc
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,Prosp
5613925966,RCI,Phone,Prosp
8153631180,RCI,Phone,Prosp
8154554979,RCI,Phone,Prosp
9723968877,RCI,Phone,Prosp
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_T
----------,---,-----,-----
Siebel CVP,RCI,Phone,Inquiry
1 row selected.
SQL> Select
2 substr(con_home_ph_num,1,1
3 substr(x_rccl_brand,1,3) AS Brand,
4 'Phone' AS Channel,
5 substr(con_person_uid,1,21
6 substr(x_rccl_source,1,23)
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_ent
25 and x_rccl_source IN ('CTI','CTI_GVS','CTI_HOTW
26 ORDER BY
27 con_home_ph_num asc
28 /
CHAN_ADDR ,BRA,CHANN,SRC_ID ,EBR_SOURCE ,EBR_TYP
----------,---,-----,-----
6269140432,RCI,Phone,Prosp
6319791332,RCI,Phone,Prosp
6627811912,RCI,Phone,Prosp
8153631180,RCI,Phone,Prosp
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.cs
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,1
substr(x_rccl_brand,1,3) AS Brand,
'Phone' AS Channel,
substr(con_person_uid,1,21
substr(x_rccl_source,1,23)
'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_ent
and x_rccl_source IN ('CTI','CTI_GVS','CTI_HOTW
ORDER BY
con_home_ph_num asc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.