Home » SQL & PL/SQL » SQL & PL/SQL » Multiple selects for a report.
| Multiple selects for a report. [message #21787] |
Thu, 29 August 2002 15:08  |
jag
Messages: 15
Registered: July 2002
|
Junior Member |
|
|
I am using multiple select statements to extract data from multiple tables. My problem is I am trying to ge the output on one page( which is formatted with headings/footings) when I run the query I get all I am looking for but each set of dat is on its own page. I would like to know how to get all out put on one page.
I have tried UNION and UNION ALL, but the keyword is not working -: unknown command "UNION" - rest of line ignored.
--Set up pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 65
--SET THE LINESIZE, WHICH MUST MATCH THE NUMBER OF EQUALS SIGNS USED
--FOR THE RULING LINES IN THE HEADERS AND FOOTERS
SET LINESIZE 71
--GET THE DATA FOR INCLUSION IN THE PAGE FOOTER.
SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'DDMONYYYYHH24MI';
COLUMN SYSDATE NEW_VALUE report_date
SELECT SYSDATE FROM DUAL;
SET TERMOUT ON
SET VERIFY OFF
--SET UP PAGE FOOTERS AND HEADINGS
TTITLE CENTER 'some company' SKIP 2 -
LEFT 'Wobble Report ' -
RIGHT 'Investigation ' SKIP 1 -
LEFT '=======================================================================' -
SKIP 2 'Employee ID: ' FORMAT 9999 emp_id_var ' ' f_name_var ' ' emp_name_var SKIP 3 -
BTITLE LEFT '=======================================================================' -
SKIP 1 -
LEFT report_date -
RIGHT 'Page' FORMAT 999 SQL.PNO
--Format the columns
CLEAR COLUMNS
COLUMN sort_column NOPRINT
COLUMN staff_num NOPRINT
COLUMN staff_num NEW_VALUE emp_id_var NOPRINT
COLUMN surname NEW_VALUE emp_name_var NOPRINT
COLUMN first_name NEW_VALUE f_name_var NOPRINT
--Breaks and computations
BREAK ON base NODUPLICATES -
ON contracts_cd NODUPLICATES -
ON rank_cd NODUPLICATES -
ON emp_status NODUPLICATES -
ON empl_status_cd NODUPLICATES -
ON crew_pos_fly NODUPLICATES -
CLEAR COMPUTES
--Set spool file
SPOOL wobbles
Select cd.surname,
cd.first_name,
cd.staff_num
From CREW_V cd
WHERE staff_num = &&staff_num;
--The first select statement, looking for the Staff members base
SELECT 1 sort_column,
'Crew Base Details' AS base,
b.base,
b.eff_dt,
b.exp_dt
FROM crew_base_v b
WHERE b.staff_num = &&staff_num;
UNION
--View that particular members contract
SELECT 2 AS sort_column,
'Crew Contracts' AS contracts_cd,
c.staff_num,
c.eff_dt,
c.exp_dt
FROM crew_contracts_v c
WHERE c.staff_num = &&staff_num;
UNION
--Select the Rank of the employee
SELECT 3 AS sort_column,
'Crew Rank Details' AS rank_cd,
r.rank_cd,
r.eff_dt,
r.exp_dt
FROM crew_rank_v r
WHERE r.staff_num = &&staff_num;
UNION
--Select the Crew start and end dates
SELECT 4 AS sort_column,
'Crew Details' AS emp_status,
d.empl_dt,
d.term_dt,
d.emp_status
FROM crew_v d
WHERE d.staff_num = &&staff_num;
UNION
--Select the Position FLy for the crew, if they are flying.
SELECT 5 AS sort_column,
'Flying Position' AS crew_pos_fly,
pf.staff_num,
pf.eff_dt,
pf.exp_dt
FROM crew_position_fly_v pf
WHERE pf.staff_num = &&staff_num;
UNION
--Select the employment status.
SELECT 6 AS sort_column,
'Crew Employment Status' AS empl_status_cd,
es.staff_num,
es.eff_dt,
es.exp_dt
FROM crew_employment_status_v es
WHERE es.staff_num = &&staff_num;
UNION
--select the crew ground position if they are not flying.
SELECT 7 AS sort_column,
'Ground Position' AS crew_pos_grn,
pg.crew_pos_grn,
pg.eff_dt,
pg.exp_dt
FROM crew_position_grn_v pg
WHERE pg.staff_num = &&staff_num;
SPOOL off
--Reset everything back to defaults
CLEAR BREAKS
TTITLE OFF
BTITLE OFF
SET NEWPAGE 1
SET PAGESIZE 24
SET LINESIZE 80
|
|
|
|
| Re: Multiple selects for a report. [message #21788 is a reply to message #21787] |
Thu, 29 August 2002 16:03  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Even if you have given UNION, the SELECT statements are not given as one. Because you terminated each SELECT with a semicolon. Remove the semicolon for each SELECT statement preceding the UNION operator. The UNION operator combines the output of two ore more SELECTs. If you terminate a SELECT with a ; just before you give a UNION, hte ORACLE can not combine them into one. So dont terminate any SELECT with a semicolon (;) except the last one. Try it out and let me know.
Good luck :)
|
|
|
|
| Re: Multiple selects for a report. [message #21834 is a reply to message #21788] |
Tue, 03 September 2002 18:22 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
I guess you didn't get what I am saying. Just remove the semicolon before you give a UNION for a SELECT statement. Look at the following, your SELECTs would look like:
--The first select statement, looking for the Staff members base
SELECT 1 sort_column,
'Crew Base Details' AS base,
b.base,
b.eff_dt,
b.exp_dt
FROM crew_base_v b
WHERE b.staff_num = &&staff_num
UNION
--View that particular members contract
SELECT 2 AS sort_column,
'Crew Contracts' AS contracts_cd,
c.staff_num,
c.eff_dt,
c.exp_dt
FROM crew_contracts_v c
WHERE c.staff_num = &&staff_num
-- I removed the above semicolon
UNION
--Select the Rank of the employee
SELECT 3 AS sort_column,
'Crew Rank Details' AS rank_cd,
r.rank_cd,
r.eff_dt,
r.exp_dt
FROM crew_rank_v r
WHERE r.staff_num = &&staff_num
-- I removed the above semicolon
UNION
--Select the Crew start and end dates
SELECT 4 AS sort_column,
'Crew Details' AS emp_status,
d.empl_dt,
d.term_dt,
d.emp_status
FROM crew_v d
WHERE d.staff_num = &&staff_num
-- I removed the above semicolon
UNION
--Select the Position FLy for the crew, if they are flying.
SELECT 5 AS sort_column,
'Flying Position' AS crew_pos_fly,
pf.staff_num,
pf.eff_dt,
pf.exp_dt
FROM crew_position_fly_v pf
WHERE pf.staff_num = &&staff_num
-- I removed the above semicolon
UNION
--Select the employment status.
SELECT 6 AS sort_column,
'Crew Employment Status' AS empl_status_cd,
es.staff_num,
es.eff_dt,
es.exp_dt
FROM crew_employment_status_v es
WHERE es.staff_num = &&staff_num
--removed the above semicolon
UNION
--select the crew ground position if they are not flying.
SELECT 7 AS sort_column,
'Ground Position' AS crew_pos_grn,
pg.crew_pos_grn,
pg.eff_dt,
pg.exp_dt
FROM crew_position_grn_v pg
WHERE pg.staff_num = &&staff_num;
SPOOL off
Now you run your report with these SELECTs, it should not return such 'UNION ...' error.
Good luck :)
|
|
|
|
| Re: Multiple selects for a report. [message #21837 is a reply to message #21788] |
Tue, 03 September 2002 19:13 |
jag
Messages: 15
Registered: July 2002
|
Junior Member |
|
|
I have sorted it out.
Although I did try the removing of the semi-colon as well.
Here is the version that works well.
--Set up pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 60
--SET THE LINESIZE, WHICH MUST MATCH THE NUMBER OF EQUALS SIGNS USED
--FOR THE RULING LINES IN THE HEADERS AND FOOTERS
SET LINESIZE 70
--GET THE DATA FOR INCLUSION IN THE PAGE FOOTER.
SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'DDMONYYYYHH24MI';
COLUMN SYSDATE NEW_VALUE report_date
SELECT SYSDATE FROM DUAL;
SET TERMOUT ON
SET VERIFY OFF
SET FEEDBACK OFF
--SET UP PAGE FOOTERS AND HEADINGS
TTITLE CENTER 'Some Company' SKIP 2 -
LEFT 'Report ' -
RIGHT 'Investigation ' SKIP 1 -
LEFT '=======================================================================' -
SKIP 2 'Employee ID: ' FORMAT 9999 emp_id_var SKIP 3 -
BTITLE LEFT '=======================================================================' -
SKIP 1 -
LEFT report_date -
RIGHT 'Page' FORMAT 999 SQL.PNO
--Format the columns
CLEAR COLUMNS
COLUMN sort_column NOPRINT
COLUMN staff_num NEW_VALUE emp_id_var NOPRINT
COLUMN surname NEW_VALUE emp_name_var NOPRINT
COLUMN first_name NEW_VALUE f_name_var NOPRINT
COLUMN SURNAME NOPRINT
COLUMN PREFERRED_NAME NOPRINT
COLUMN FIRST_NAME NOPRINT
COLUMN EMPL_DT NOPRINT
COLUMN TERM_DT NOPRINT
COLUMN employee_Status_text HEADING 'Table Information' FORMAT A29
COLUMN base HEADING 'Details' FORMAT A7
COLUMN eff_dt HEADING 'Start Date' FORMAT A13
COLUMN exp_dt HEADING 'End Date' FORMAT A13
COLUMN rank_cd HEADING 'Rank Code' FORMAT A5
COLUMN empl_dt HEADING 'Emp Date' FORMAT A13
COLUMN term_dt HEADING 'Term Date' FORMAT A13
COLUMN emp_status HEADING 'Status' FORMAT A13
--Breaks and computations
BREAK ON employee_Status_text SKIP 2 NODUPLICATES -
ON base NODUPLICATES -
ON contracts_cd NODUPLICATES -
ON rank_cd NODUPLICATES -
ON emp_status NODUPLICATES -
ON crew_pos_fly NODUPLICATES -
ON empl_status_cd NODUPLICATES -
ON crew_pos_grn NODUPLICATES
--Set spool file
SPOOL wobbles
SELECT 1 sort_column,
'Employee Base Details' as employee_Status_text,
b.staff_num,
b.base,
b.eff_dt,
b.exp_dt
FROM crew_base_v b
WHERE b.staff_num = &&staff_num
UNION ALL
SELECT 2 as sort_column,
'Employee Contract Details' as employee_Status_text,
c.staff_num,
c.contract_cd,
c.eff_dt,
c.exp_dt
FROM crew_contracts_v c
WHERE c.staff_num = &&staff_num
UNION ALL
SELECT 3 as sort_column,
'Employee Rank Details' as employee_Status_text,
r.staff_num,
r.rank_cd,
r.eff_dt,
r.exp_dt
FROM crew_rank_v r
WHERE r.staff_num = &&staff_num
UNION ALL
SELECT 4 as sort_column,
'Employment Details' as employee_Status_text,
d.staff_num,
d.emp_status,
d.empl_dt,
d.term_dt
FROM crew_v d
WHERE d.staff_num = &&staff_num
UNION ALL
SELECT 5 as sort_column,
'Employee Pos Fly Details' as employee_Status_text,
pf.staff_num,
pf.CREW_POS_FLY,
pf.eff_dt,
pf.exp_dt
FROM crew_position_fly_v pf
WHERE pf.staff_num = &&staff_num
UNION ALL
SELECT 6 as sort_column,
'Employee Status' as employee_Status_text,
es.staff_num,
es.empl_status_cd,
es.eff_dt,
es.exp_dt
FROM crew_employment_status_v es
WHERE es.staff_num = &&staff_num
UNION ALL
SELECT 7 as sort_column,
'Employee Pos Grn Details' as employee_Status_text,
pg.staff_num,
pg.crew_pos_grn,
pg.eff_dt,
pg.exp_dt
FROM crew_position_grn_v pg
WHERE pg.staff_num = &&staff_num
ORDER BY 1;
SPOOL off
--Reset everything back to defaults
CLEAR BREAKS
TTITLE OFF
BTITLE OFF
SET NEWPAGE 1
SET PAGESIZE 24
SET LINESIZE 80
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 20:25:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
