Solved

Can't figure out these complicated UNIONS

Posted on 2013-05-21
9
331 Views
Last Modified: 2013-05-22
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
Comment
Question by:Barry62
  • 4
  • 3
  • 2
9 Comments
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
Comment Utility
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
 
LVL 8

Author Comment

by:Barry62
Comment Utility
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
 
LVL 8

Author Comment

by:Barry62
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
 
LVL 8

Author Comment

by:Barry62
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Kdo earned 250 total points
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 8

Author Closing Comment

by:Barry62
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now