SQL help sought

[dps]

Hello.

I have about 12 different sql queries currently in one sql file. Currently I have to manually run these scripts (using Navigator) one by one. If anything is retrieved then I save the reults as a .csv file. If nothing is retireved then I move onto the next script.

Question is how best can I automate this procedure so that those scripts that do return something can be saved as a .csv file and I can send that to my user rather then having to go through each script in the file????

 

[LKBrwnDBA]

Add "spool' commands to your script file, something like:

spool script1.csv
select empno||',"'||ename||'",'||deptno
  from emp;
spool script2.sql
select deptno||',"'||dname||'"' from dept;
spool off

And execute as script:

@MyScript.sql

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[dps]

I get no .csv file when I know I should

One of the queries is
SELECT
c.PRD_CODE AS "PRODUCT"
FROM cantain c
WHERE
c.sls_grp_code = 'FX'
ORDER BY
c.sls_grp_code

I have even tried spooloing off after each script

 

[dps]

If I place a
spool c:\msql.csv
spool off

spool c:\next sql.csv


..ie after each script

then at least I get a .csv file produced but cannot open it - unable to read file!

 

[LKBrwnDBA]

You have to put the spool BEFORE each script and modify the SELECT to produce the 'comma delimited' output:

spool c:\MyScript1.csv
SELECT c.PRD_CODE||',"'||c.PRD_DESCR||'"'
  FROM cantain c
 WHERE c.sls_grp_code = 'FX'
 ORDER BY c.sls_grp_code;
spool c:\MyScript2.sql
select deptno||',"'||dname||'"' from dept;
spool off

What program are you using to open it?
PS: You need to issue the spool off at the end.
Also notice the string columns are enclosed in quotes (").




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[dps]

Thanks for the response.

I would just double click the file I suppose you can open with Excel and ensure the option for comma delimited is on.

Do you mean

SELECT
c.PRD_CODE||',"'||AS "PRODUCT",
c.CAM_NUM||',"'||AS "CAMP"
FROM cantain c
WHERE
c.sls_grp_code = 'FX'
ORDER BY
c.sls_grp_code

Running the below gives me zero bytes!!

SELECT
c.PRD_CODE||',"'||c.CAM_NUM||',"'
FROM cantain c
WHERE
c.sls_grp_code = 'FX'
ORDER BY
c.sls_grp_code

Column aliases are important to me and should show, so for example c.PRD_CODE||',"'||AS "PRODUCT", ...should have PRODUCT showing for that column containing the product value.

 

[LKBrwnDBA]

No, I meant exactly what I coded, notice there are no "commas" between columns and no "aliases".
To generate the column headers (aliases) you would need to use the "prompt" command.

spool c:\MyScript1.csv
prompt PRODUCT,CAMP,...etc...
SELECT c.PRD_CODE||',"'||c.CAM_NUM||'"'
  FROM cantain c
 WHERE c.sls_grp_code = 'FX'
 ORDER BY c.sls_grp_code;
--...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb