Looping and Variables 1

[sxschech]

I know how to do this with MS-Access and was wondering how to write it in Oracle. We were given a script, a portion of which I include below, which is run each month. We need to expand it to run for multiple years rather than a single year. Since the years and dates need to go together, I was thinking that a loop would be the way to go, although certainly open to alternative ideas.

The output of the script of Unioned SQL statements produces a CSV file. When the script was originally created, we only had one data set (year 2008) eventually we will have 3 year groupings of data. (2008, 2009, 2010; 2009, 2010, 2011; etc.) For Jan 2009, we would run it for Year = 2009 and Cutoff Date 01-Jan-2009 (may not always be the first of the month as we could be requested to run for different dates within the month and when running for different years), then the same query needs to be run for 2008 with a cutoff date of 01-Jan-2008, or the date the requestor specifies.

As an example we may decide to run the data in 2009 as of 05-Jan-2009 and for 2008 we may use 07-Jan-2008; The first Monday in each year.

There are 14 output csv files created and each file is made up of 7 Union queries.

How would we set up the loop and pass the dates and years. Another aspect that we would like to do with the loop, unless there is another approach, is that the person who wrote the script repeated all the scripts because we wanted to have the csv files saved in two ways, the first is a generic name so that our access/excel application can directly read the file, the other gives the csv files a dated file name so that we have an historical record of what was run at a particular day. Currently we pass the variables for the Cutoff Date and the historical file date by entering it into a command line of a DOS bat file that then runs the code to SQL*Plus.

Here is a partial code

set feedback off
set pause off
set pagesize 50000
set newpage 0
set heading off
set verify off
set termout off

spool "c:\temp\first_gen.csv"

select 'YEAR'||chr(9)||'STU_POP'||chr(9)||'QNAME'||chr(9)||
'SORT_STATUS'||chr(9)||'STATUS'||chr(9)||'TOTAL'||chr(9)||
'NFG'||chr(9)||'FG'||chr(9)||'DECLINED_TO_ANSWER'||chr(9)||'UNKNOWN' as stuff
from dual
UNION ALL
select year||chr(9)||stu_pop||chr(9)||'FIRST_GEN'||chr(9)||
'1.Applicants'||chr(9)||
'Applicants'||chr(9)||
sum(applied)||chr(9)||
sum(case when first_gen = 'N'   and applied = 1 then 1 else 0 end)||chr(9)||
sum(case when first_gen = 'Y'   and applied = 1 then 1 else 0 end)||chr(9)||
sum(case when first_gen = 'D'   and applied = 1 then 1 else 0 end)||chr(9)||
sum(case when first_gen is null and applied = 1 then 1 else 0 end)
from usf_stu.appl_stats
where stu_population in ('F', 'T')
  and student_level = 'UG'
  and appl_date < [red]'&cut_off'[/red]
  and year = [red]2008[/red] 
  and college <> 'PS'
group by year, stu_pop, 'FIRST_GEN'
UNION ALL
...
...
spool off

----------------------------------------------
-- Put the run date in the output file name --
----------------------------------------------

--spool "c:\temp\first_gen[red]_'&1'[/red].csv"

select 'YEAR'||chr(9)||'STU_POP'||chr(9)||'QNAME'||chr(9)||
'SORT_STATUS'||chr(9)||'STATUS'||chr(9)||'TOTAL'||chr(9)||
'NFG'||chr(9)||'FG'||chr(9)||'DECLINED_TO_ANSWER'||chr(9)||'UNKNOWN' as stuff
from dual
UNION ALL
select year||chr(9)||stu_pop||chr(9)||'FIRST_GEN'||chr(9)||
'1.Applicants'||chr(9)||
'Applicants'||chr(9)||
sum(applied)||chr(9)||
sum(case when first_gen = 'N'   and applied = 1 then 1 else 0 end)||chr(9)||
sum(case when first_gen = 'Y'   and applied = 1 then 1 else 0 end)||chr(9)||
sum(case when first_gen = 'D'   and applied = 1 then 1 else 0 end)||chr(9)||
sum(case when first_gen is null and applied = 1 then 1 else 0 end)
from usf_stu.appl_stats
where stu_population in ('F', 'T')
  and student_level = 'UG'
  and appl_date < '&cut_off'
  and year = 2008 and college <> 'PS'
group by year, stu_pop, 'FIRST_GEN'
UNION ALL
...
...

 

[LKBrwnDBA]

1) Based on what or how do you generate the year and cutoff date pairs?

2) For the issue of avoiding duplication and keeping a history file, just do this:

-- Etc ...
spool off

----------------------------------------------
-- Put the run date in the output file name --
----------------------------------------------
host copy c:\temp\first_gen.csv c:\temp\first_gen_'&1'.csv

-- Query is not necesary anymore.

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

 

[sxschech]

Currently, the only variable we enter is the cutoff date as the year is hard coded into the sql statements.

We use a dos bat file that looks something like this...

sqlplus.exe UserName/UserPW@abcd @"\\Discserver\Applications\stats.sql" %1

to run it, we go to the cmd prompt and type
[tt]
cd\temp
run_sql 05-JAN-08
[/tt]

Since we are going to need more variables, we are open to putting them directly in the stats.sql file, if it is too complicated to do it in the bat file.

 

[LKBrwnDBA]

One solution would be to create a report 'cut off' table where you can select one or many dates to report.

For example:

rpt_year cutoff_dt   report_ind
-------- ---------   ----------
2008     07-Jan-2008  N
2009     01-Jan-2009  Y
2009     05-Jan-2009  N

You would 'join' this table with your query and select those year/cut-off dates with the 'Y' indicator and therefore you got your 'looping'.

HTH




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

 

[sxschech]

Thank you. Appreciate your help LKBrwnDBA. Seems to be working. One other question, if I may...

If we would like to run the queries under two different conditions, the first <> and the second =. Can this be easily done?

[tt]
and college [red]<>[/red] 'PS'

and college [red]=[/red] 'PS'
[/tt]

...
from usf_stu.appl_stats, usf_stu.cutoff
where stu_population in ('F', 'T')
  and student_level = 'UG'
  and accepted_date < cutoff_dt
  and year = rpt_year 
  and college [red]<>[/red] 'PS'
...

Can it be parameterized or variable to be as such for the equal version.

...
from usf_stu.appl_stats, usf_stu.cutoff
where stu_population in ('F', 'T')
  and student_level = 'UG'
  and accepted_date < cutoff_dt
  and year = rpt_year 
  and college [red]=[/red] 'PS'
...

 

[LKBrwnDBA]

Maybe you can add a fourth column to your report 'cut off' table to indicate which condition you want:

rpt_year cutoff_dt   report_ind  [red]ps_sw[/red]
-------- ---------   ----------  [red]------[/red]
2008     07-Jan-2008  N          [red]N[/red]
2009     01-Jan-2009  Y          [red]Y[/red]
2009     05-Jan-2009  N          [red]Y[/red]

And perhaps it will work if you modify your query like this:

-- etc --
  and ((ps_sw = 'Y' and college = 'PS')
   or  (ps_sw = 'N' and college <> 'PS'))

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

 

[sxschech]

Hi again,

seems that I haven't been able to get the

host copy c:\temp\first_gen.csv c:\temp\first_gen_'&1'.csv

to work. It copies the file (at least there is a message stating " 1 Copy(s) filed.", but the file size is 0 bytes and opening up the file shows that it is empty. Perhaps that is why the script's creator duplicated all the queries.

Dir shows:
[tt]
c:\temp\first_gen.csv 2 kb
c:\temp\first_gen_'14-Jan-09'.csv 0 kb
[/tt]

We even tried removing the apostrophes, but still file showed 0 kb.

 

[sxschech]

Figured it out. Needed to put the host copy statement after SPOOL OFF statement not before.