Solved

Can't figure out these complicated UNIONS

Posted on 2013-05-21
9
335 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:Kent Olsen
ID: 39185935
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
ID: 39186126
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
ID: 39187305
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Author Comment

by:Barry62
ID: 39187324
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39187356
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
ID: 39187384
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:
Kent Olsen earned 250 total points
ID: 39187432
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
ID: 39187540
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
ID: 39188487
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

821 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