• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Can't figure out these complicated UNIONS

I am trying to combine several queries into one big one, and it isn't working.  I need to do this because I'm converting from an ACE report (which allows temp files) to ARGOS (which does not).  Maybe I'm going about it the wrong way, but here is what I have:

select
    id_rec.id,
    id_rec.fullname,
    adm_rec.plan_enr_yr,
    adm_rec.plan_enr_sess,
    adm_rec.enrstat,
    adm_rec.major,
    adm_rec.deg,
    adm_rec.location,
    0 plan_grad_yr,
    ' ' plan_grad_sess,
    major_table.ltr_txt,
    DATE('01/01/1899') enr_date,
	case
	when adm_rec.enrstat = 'APPCOMP' or adm_rec.enrstat = 'APPLIED' or adm_rec.enrstat = 'APPL_FEE' or adm_rec.enrstat = 'REAPPLY' 
	or adm_rec.enrstat = 'PENDINT' then 'APPLIED'
	when adm_rec.enrstat = 'ACCEPTED' then 'ACCEPTED'
	when adm_rec.enrstat = 'PENDING' then 'PENDING'
	when adm_rec.enrstat = 'WAITLIST' then 'WAITLIST'
	when adm_rec.enrstat = 'ADMITTED' then 'ADMITTED'
	when adm_rec.enrstat = 'INQUIRED' then 'INQUIRED'
	when adm_rec.nrstat = 'DECLINED' or adm_rec.enrstat = 'CANCELED' or adm_rec.enrstat = 'NOSHOW' or adm_rec.enrstat = 'ENROLLED'
	then 'DECLINED'
	when adm_rec.enrstat = 'FIRST' then 'FIRST'
	when adm_rec.enrstat = 'SECOND' then 'SECOND'
	when adm_rec.enrstat = 'READMIT' then 'READMIT'
	when adm_rec.enrstat = 'DENIED' then 'DENIED'
	when adm_rec.enrstat = ' ' then ' '
	end newstat,
    case
    when prog_enr_rec.ipeds_intend_deg is null or prog_enr_rec.ipeds_intend_deg = ' ' then 'NONE'
    else prog_enr_rec.ipeds_intend_deg
    end newdeg
from
    id_rec, adm_rec, major_table
where
    id_rec.id = adm_rec.id
and adm_rec.major = major_table.major
and adm_rec.plan_enr_yr = 2013
and adm_rec.plan_enr_sess = 'FA'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and (enrstat NOT IN ('DENIED', 'DECLINED', 'CANCELED', 'NOSHOW', 'ENROLLED'))
and (id_rec.fullname not matches 'Student*')
and adm_rec.major not like 'MHW%'
and adm_rec.major not like 'TRNG%'
and (id_rec.id not in (110358, 66349, 110345, 106659, 110352, 110341, 110343, 110356, 110729, 110342, 110349, 110354))
and id_rec.id not in (
select
    id_rec.id
from
    id_rec, stu_acad_rec, prog_enr_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and stu_acad_rec.yr = 2013
and stu_acad_rec.sess = 'FA'
and stu_acad_rec.reg_stat = 'C'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and prog_enr_rec.enr_date >= '05/21/2013'
and stu_acad_rec.major1 = major_table.major
and (id_rec.fullname not matches 'Student*')
UNION
select
    unique id_rec.id
from
    id_rec, stu_acad_rec, prog_enr_rec, ed_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and id_rec.id = ed_rec.id
and stu_acad_rec.yr = 2013
and stu_acad_rec.sess = 'FA'
and stu_acad_rec.reg_stat = 'C'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and ed_rec.enr_date >= '05/21/2013'
and ed_rec.major1 = major_table.major
and (ed_rec.sch_id = 37830 or ed_rec.sch_id = 34306)
and (ed_rec.exit_date IS NULL or ed_rec.exit_date = '   ')
and (ed_rec.grad_date IS NULL or ed_rec.grad_date = '   ')
and (ed_rec.enr_date >= prog_enr_rec.enr_date)
and (id_rec.fullname not matches 'Student*')
order by enr_date desc
UNION
select
    id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    'FIRST' enrstat,
    stu_acad_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    prog_enr_rec.enr_date,
    'FIRST' newstat,
    case
    when prog_enr_rec.ipeds_intend_deg is null or prog_enr_rec.ipeds_intend_deg = ' ' then 'NONE'
    else prog_enr_rec.ipeds_intend_deg
    end newdeg
from
    id_rec, stu_acad_rec, prog_enr_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and stu_acad_rec.yr = 2013
and stu_acad_rec.sess = 'FA'
and stu_acad_rec.reg_stat = 'C'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and prog_enr_rec.enr_date >= '05/21/2013'
and stu_acad_rec.major1 = major_table.major
and (id_rec.fullname not matches 'Student*')
UNION
select
    unique id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    'READMIT' enrstat,
    ed_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    ed_rec.enr_date,
    'FIRST' newstat,
    case
    when prog_enr_rec.ipeds_intend_deg is null or prog_enr_rec.ipeds_intend_deg = ' ' then 'NONE'
    else prog_enr_rec.ipeds_intend_deg
    end newdeg
from
    id_rec, stu_acad_rec, prog_enr_rec, ed_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and id_rec.id = ed_rec.id
and stu_acad_rec.yr = 2013
and stu_acad_rec.sess = 'FA'
and stu_acad_rec.reg_stat = 'C'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and ed_rec.enr_date >= '05/21/2013'
and ed_rec.major1 = major_table.major
and (ed_rec.sch_id = 37830 or ed_rec.sch_id = 34306)
and (ed_rec.exit_date IS NULL or ed_rec.exit_date = '   ')
and (ed_rec.grad_date IS NULL or ed_rec.grad_date = '   ')
and (ed_rec.enr_date >= prog_enr_rec.enr_date)
and (id_rec.fullname not matches 'Student*')
order by enr_date desc
UNION
select
    id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    'SECOND' enrstat,
    stu_acad_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    prog_enr_rec.enr_date,
    'SECOND' newstat,
    case
    when prog_enr_rec.ipeds_intend_deg is null or prog_enr_rec.ipeds_intend_deg = ' ' then 'NONE'
    else prog_enr_rec.ipeds_intend_deg
    end newdeg
from
    id_rec, stu_acad_rec, prog_enr_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and stu_acad_rec.yr = 2013
and stu_acad_rec.sess = 'FA'
and stu_acad_rec.reg_stat = 'C'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and stu_acad_rec.major1 = major_table.major
and (id_rec.fullname not matches 'Student*')
and id_rec.id not in((
select
    id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    'FIRST' enrstat,
    stu_acad_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    prog_enr_rec.enr_date,
    'FIRST' newstat,
    case
    when prog_enr_rec.ipeds_intend_deg is null or prog_enr_rec.ipeds_intend_deg = ' ' then 'NONE'
    else prog_enr_rec.ipeds_intend_deg
    end newdeg
from
    id_rec, stu_acad_rec, prog_enr_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and stu_acad_rec.yr = 2013
and stu_acad_rec.sess = 'FA'
and stu_acad_rec.reg_stat = 'C'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and prog_enr_rec.enr_date >= '05/21/2013'
and stu_acad_rec.major1 = major_table.major
and (id_rec.fullname not matches 'Student*')
UNION
select
    unique id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    'READMIT' enrstat,
    ed_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    ed_rec.enr_date,
    'FIRST' newstat,
    case
    when prog_enr_rec.ipeds_intend_deg is null or prog_enr_rec.ipeds_intend_deg = ' ' then 'NONE'
    else prog_enr_rec.ipeds_intend_deg
    end newdeg
from
    id_rec, stu_acad_rec, prog_enr_rec, ed_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and id_rec.id = ed_rec.id
and stu_acad_rec.yr = 2013
and stu_acad_rec.sess = 'FA'
and stu_acad_rec.reg_stat = 'C'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and ed_rec.enr_date >= '05/21/2013'
and ed_rec.major1 = major_table.major
and (ed_rec.sch_id = 37830 or ed_rec.sch_id = 34306)
and (ed_rec.exit_date IS NULL or ed_rec.exit_date = '   ')
and (ed_rec.grad_date IS NULL or ed_rec.grad_date = '   ')
and (ed_rec.enr_date >= prog_enr_rec.enr_date)
and (id_rec.fullname not matches 'Student*')
order by enr_date desc
UNION
select
    id_rec.id,
    id_rec.fullname,
    prog_enr_rec.adm_yr plan_enr_yr,
    prog_enr_rec.adm_sess plan_enr_sess,
    'SECOND' enrstat,
    prog_enr_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    prog_enr_rec.enr_date,
    'SECOND' newstat,
    case
    when prog_enr_rec.ipeds_intend_deg is null or prog_enr_rec.ipeds_intend_deg = ' ' then 'NONE'
    else prog_enr_rec.ipeds_intend_deg
    end newdeg
from
   id_rec, prog_enr_rec, major_table
where
   id_rec.id = prog_enr_rec.id
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and prog_enr_rec.major1 = major_table.major
and (id_rec.id in (110358, 66349, 110345, 106659, 110352, 110341, 110343, 110356, 110729, 110342, 110349, 110354))
and (id_rec.fullname not matches 'Student*')
and id_rec.id not in(
select
    id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    'FIRST' enrstat,
    stu_acad_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    prog_enr_rec.enr_date,
    'FIRST' newstat,
    case
    when prog_enr_rec.ipeds_intend_deg is null or prog_enr_rec.ipeds_intend_deg = ' ' then 'NONE'
    else prog_enr_rec.ipeds_intend_deg
    end newdeg
from
    id_rec, stu_acad_rec, prog_enr_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and stu_acad_rec.yr = 2013
and stu_acad_rec.sess = 'FA'
and stu_acad_rec.reg_stat = 'C'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and prog_enr_rec.enr_date >= '05/21/2013'
and stu_acad_rec.major1 = major_table.major
and (id_rec.fullname not matches 'Student*')
UNION
select
    unique id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    'READMIT' enrstat,
    ed_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    ed_rec.enr_date,
    'FIRST' newstat,
    case
    when prog_enr_rec.ipeds_intend_deg is null or prog_enr_rec.ipeds_intend_deg = ' ' then 'NONE'
    else prog_enr_rec.ipeds_intend_deg
    end newdeg
from
    id_rec, stu_acad_rec, prog_enr_rec, ed_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and id_rec.id = ed_rec.id
and stu_acad_rec.yr = 2013
and stu_acad_rec.sess = 'FA'
and stu_acad_rec.reg_stat = 'C'
and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
and ed_rec.enr_date >= '05/21/2013'
and ed_rec.major1 = major_table.major
and (ed_rec.sch_id = 37830 or ed_rec.sch_id = 34306)
and (ed_rec.exit_date IS NULL or ed_rec.exit_date = '   ')
and (ed_rec.grad_date IS NULL or ed_rec.grad_date = '   ')
and (ed_rec.enr_date >= prog_enr_rec.enr_date)
and (id_rec.fullname not matches 'Student*')
order by enr_date desc

Open in new window

0
Barry62
Asked:
Barry62
  • 4
  • 3
  • 2
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Barry,

The result of a UNION operation is to combine all of the rows in the first query with all of the rows in the second query.  For this to happen, both of the queries must return the same number of columns, and the data types of each column must be compatible.

The query that returns just a single column can't possibly be UNIONed with the other queries.

Do you really want to union a bunch of separate queries, or do you need to JOIN the queries (so that items with the same key are on the same row)?


Kent
0
 
PortletPaulCommented:
There simply is no way to union these 8 very different queries as they exist right now - and it also appears that you shouldn't try to either. It would perform quite badly I believe. You really should be joining tables to arrive at the combined results, or at the very least combining as many as you can to reduce from 8 queries to something much less.

I'd also like to suggest you adopt ANSI join syntax which will not only substantially improve the legibility of these queries, these joins make it often simpler to arrive at a complex solution.

 "select unique" occurs within your queries (e.g. lines 63-64 above). do you mean "select distinct"?
http://dev.mysql.com/doc/refman/5.0/en/select.html
select distinct by the way can by itself slow down query performance substantially.
0
 
Barry62Author Commented:
Well, I know it looks like a mess, but here is a representation of what the UNION groupings are supposed to be:

(query1 not in (query2 UNION query3) UNION (query2 UNION query3))

UNION

((query4 union query5) not in (query2 UNION query3))



I'll try to figure out how to simplify it.  The problem is that the original SQL is written in ACE, which allows you to do a query and save it to a temp file and then join or union the temp files together for the output.  The original SQL was union'ing everything, so I thought I'd try it that way.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Barry62Author Commented:
If you want to see the original SQL sequence, here it is:


select
    id_rec.id,
    id_rec.fullname,
    adm_rec.plan_enr_yr,
    adm_rec.plan_enr_sess,
    adm_rec.enrstat,
    adm_rec.major,
    adm_rec.deg,
    adm_rec.location,
    0 plan_grad_yr,
    " " plan_grad_sess,
    major_table.ltr_txt,
    DATE("01/01/1899") enr_date
from
    id_rec, adm_rec, major_table
where
    id_rec.id = adm_rec.id
and adm_rec.major = major_table.major
and adm_rec.plan_enr_yr = $inputyr
and adm_rec.plan_enr_sess = $inputsess
and (id_rec.valid = "Y" or id_rec.valid is null or id_rec.valid = " ")
and (enrstat NOT IN ("DENIED", "DECLINED", "CANCELED", "NOSHOW", "ENROLLED"))
and (id_rec.fullname not matches "Student*")
and adm_rec.major not like "MHW%"
and adm_rec.major not like "TRNG%"
and (id_rec.id not in (110358, 66349, 110345, 106659, 110352, 110341, 110343, 11
0356, 110729, 110342, 110349, 110354))
into temp pre_0 with no log;

select
    id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    "FIRST" enrstat,
    stu_acad_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    prog_enr_rec.enr_date
from
    id_rec, stu_acad_rec, prog_enr_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and stu_acad_rec.yr = $inputyr
and stu_acad_rec.sess = $inputsess
and stu_acad_rec.reg_stat = "C"
and (id_rec.valid = "Y" or id_rec.valid is null or id_rec.valid = " ")
and prog_enr_rec.enr_date >= $inputdate
and stu_acad_rec.major1 = major_table.major
and (id_rec.fullname not matches "Student*")
into temp a_0 with no log;

select
    unique id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    "READMIT" enrstat,
    ed_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    ed_rec.enr_date
from
    id_rec, stu_acad_rec, prog_enr_rec, ed_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and id_rec.id = ed_rec.id
and stu_acad_rec.yr = $inputyr
and stu_acad_rec.sess = $inputsess
and stu_acad_rec.reg_stat = "C"
and (id_rec.valid = "Y" or id_rec.valid is null or id_rec.valid = " ")
and ed_rec.enr_date >= $inputdate
and ed_rec.major1 = major_table.major
and (ed_rec.sch_id = 37830 or ed_rec.sch_id = 34306)
and (ed_rec.exit_date IS NULL or ed_rec.exit_date = "   ")
and (ed_rec.grad_date IS NULL or ed_rec.grad_date = "   ")
and (ed_rec.enr_date >= prog_enr_rec.enr_date)
and (id_rec.fullname not matches "Student*")
order by enr_date desc
into temp b_0 with no log;

select
    id_rec.id,
    id_rec.fullname,
    stu_acad_rec.yr plan_enr_yr,
    stu_acad_rec.sess plan_enr_sess,
    "SECOND" enrstat,
    stu_acad_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    prog_enr_rec.enr_date
from
    id_rec, stu_acad_rec, prog_enr_rec, major_table
where
    id_rec.id = stu_acad_rec.id
and id_rec.id = prog_enr_rec.id
and stu_acad_rec.yr = $inputyr
and stu_acad_rec.sess = $inputsess
and stu_acad_rec.reg_stat = "C"
and (id_rec.valid = "Y" or id_rec.valid is null or id_rec.valid = " ")
and stu_acad_rec.major1 = major_table.major
and (id_rec.fullname not matches "Student*")
into temp c1_0 with no log;

select
    id_rec.id,
    id_rec.fullname,
    prog_enr_rec.adm_yr plan_enr_yr,
    prog_enr_rec.adm_sess plan_enr_sess,
    "SECOND" enrstat,
    prog_enr_rec.major1 major,
    prog_enr_rec.ipeds_intend_deg deg,
    prog_enr_rec.location,
    prog_enr_rec.plan_grad_yr,
    prog_enr_rec.plan_grad_sess,
    major_table.ltr_txt,
    prog_enr_rec.enr_date
from
   id_rec, prog_enr_rec, major_table
where
   id_rec.id = prog_enr_rec.id
and (id_rec.valid = "Y" or id_rec.valid is null or id_rec.valid = " ")
and prog_enr_rec.major1 = major_table.major
and (id_rec.id in (110358, 66349, 110345, 106659, 110352, 110341, 110343, 110356
, 110729, 110342, 110349, 110354))
and (id_rec.fullname not matches "Student*")
into temp c2_0 with no log;

select c1_0.*
from c1_0
UNION
select c2_0.*
from c2_0
into temp c_0 with no log;

select a_0.*
from a_0
UNION
select b_0.*
from b_0
into temp first_0 with no log;

select pre_0.*
from pre_0
where (id not in (select id from first_0 ))
into temp adm_0 with no log;

select c_0.*
from c_0
where (id not in (select id from first_0 ))
into temp cont_0 with no log;

select adm_0.*
from adm_0
UNION
select first_0 .*
from first_0
into temp grp_1 with no log;

select grp_1.*
from grp_1
UNION
select cont_0.*
from cont_0
order by id, enr_date desc

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Barry,

The second sub-query returns a single column, and ID value.  It looks like this is the student id's that you want to select.

If you want all of the data for a particular student ID on a single row, you'll want to OUTER JOIN the other sub-queries to the ID values returned in this query.  If you want the data on separate rows, you'll want to use the ID value in the filter clause of each of the other sub-queries before the UNION operation.  And it looks like you really want to use UNION ALL here.  Also, SQL SERVER doesn't allow you to SORT in each sub-query.  You'll need a single SORT operation at the end of the query.


Kent
0
 
Barry62Author Commented:
I want single rows for each ID.

Actually, I am rewriting it in Argos, which uses an Informix database.  I'm not sure of the differences with SQL Server, if there even are any.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  Then the basic structure will be something like this:

SELECT * FROM
(
  select
    unique id_rec.id
from
    id_rec, stu_acad_rec, prog_enr_rec, ed_rec, major_table
where     id_rec.id = stu_acad_rec.id
  and id_rec.id = prog_enr_rec.id
  and id_rec.id = ed_rec.id
  and stu_acad_rec.yr = 2013
  and stu_acad_rec.sess = 'FA'
  and stu_acad_rec.reg_stat = 'C'
  and (id_rec.valid = 'Y' or id_rec.valid is null or id_rec.valid = ' ')
  and ed_rec.enr_date >= '05/21/2013'
  and ed_rec.major1 = major_table.major
  and (ed_rec.sch_id = 37830 or ed_rec.sch_id = 34306)
  and (ed_rec.exit_date IS NULL or ed_rec.exit_date = '   ')
  and (ed_rec.grad_date IS NULL or ed_rec.grad_date = '   ')
  and (ed_rec.enr_date >= prog_enr_rec.enr_date)
  and (id_rec.fullname not matches 'Student*')
) t0
LEFT JOIN
(
  {sub-query1}
) t1
  ON ...
LEFT JOIN
(
  {sub-query3}
) t2
  ON ...
etc.

Open in new window


Each of the sub-queries in your original UNION style query gets plugged into this.  You'll probably want to alias some of the columns so that you don't have duplicate names.


Kent
0
 
PortletPaulCommented:
so "select unique" really does actually exist? how interesting.

& nice outline kdo

I thought about looking at those queries in more detail but ran out of time. As you want one row per ID then I think  you really have no practical option except use of outer joins.

I would like to again  state a preference for using ANSI join syntax - althogh I don't know Informix - it's my belief that many of the queries require similar tables/joins and that if you separate the joins from filters you could achieve significant overall simplification.
0
 
Barry62Author Commented:
Thanks, guys, I got it working.  kdo, your structure worked for me.  And Paul, your explanation helped get me on the right track.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now