Multi-Select Query Error (No union support?)

[manshack_one]

This is my original query at the bottom of 4 cascading input controls.

select unitdesc from UNITS 
where $X{IN,system,HCD-system} 
and $X{IN,divreg,HCD-divreg} 
and $X{IN,district,HCD-district} 
order by unitdesc;

It pulls all the unit descriptions like it's supposed to but now the users are only wanting to get a list of unit descriptions that have actual data.  So now I have to query each table and pull back the list of descriptions from there. Here's what I tried to do but get an Error box (Just says error):

(select CAD.tblUnit from CAD, UNITS
where $X{IN,UNITS.system,HCD-system} 
and $X{IN,UNITS.divreg,HCD-divreg} 
and $X{IN,UNITS.district,HCD-district}
union
(select DIABETES.tblUnit from DIABETES, UNITS
where $X{IN,UNITS.system,HCD-system} 
and $X{IN,UNITS.divreg,HCD-divreg} 
and $X{IN,UNITS.district,HCD-district}
union
(select HEART.tblUnit from HEART, UNITS
where $X{IN,UNITS.system,HCD-system} 
and $X{IN,UNITS.divreg,HCD-divreg} 
and $X{IN,UNITS.district,HCD-district}
union
(select HLD.tblUnit from HLD, UNITS
where  $X{IN,UNITS.system,HCD-system} 
and $X{IN,UNITS.divreg,HCD-divreg} 
and $X{IN,UNITS.district,HCD-district}
union
(select HTN.tblUnit from HTN, UNITS
where $X{IN,UNITS.system,HCD-system} 
and $X{IN,UNITS.divreg,HCD-divreg} 
and $X{IN,UNITS.district,HCD-district}
union
(select RESPIRATORY.tblUnit from RESPIRATORY, UNITS
where $X{IN,UNITS.system,HCD-system} 
and $X{IN,UNITS.divreg,HCD-divreg} 
and $X{IN,UNITS.district,HCD-district}
union
(select SEIZURES.tblUnit from SEIZURES, UNITS
where $X{IN,UNITS.system,HCD-system} 
and $X{IN,UNITS.divreg,HCD-divreg} 
and $X{IN,UNITS.district,HCD-district})
order by tblUnit;

[mdahlman]

Is that a copy/paste of what you are running? It has 7 left parens and only 1 right paren. The database won't like that.

Regards,
Matt

[manshack_one]

Weird, not sure where the other half of those enclosures went to. I'll go double check it.

 

[manshack_one]

 Here's what's in there right now and it gets an Error but the log is describing index out of bounds problem:

(select CAD.tblUnit from CAD, UNITS

where CAD.tblUnit = UNITS.unitdesc

and $X{IN,UNITS.system,HCD-system}

and $X{IN,UNITS.divreg,HCD-divreg} 

and $X{IN,UNITS.district,HCD-district})

union

(select DIABETES.tblUnit from DIABETES, UNITS

where DIABETES.tblUnit = UNITS.unitdesc

and $X{IN,UNITS.system,HCD-system}

and $X{IN,UNITS.divreg,HCD-divreg} 

and $X{IN,UNITS.district,HCD-district})

union

(select HEART.tblUnit from HEART, UNITS

where HEART.tblUnit = UNITS.unitdesc

and $X{IN,UNITS.system,HCD-system}

and $X{IN,UNITS.divreg,HCD-divreg} 

and $X{IN,UNITS.district,HCD-district})

union

(select HLD.tblUnit from HLD, UNITS

where  HLD.tblUnit = UNITS.unitdesc

and $X{IN,UNITS.system,HCD-system}

and $X{IN,UNITS.divreg,HCD-divreg} 

and $X{IN,UNITS.district,HCD-district})

union

(select HTN.tblUnit from HTN, UNITS

where HTN.tblUnit = UNITS.unitdesc

and $X{IN,UNITS.system,HCD-system}

and $X{IN,UNITS.divreg,HCD-divreg} 

and $X{IN,UNITS.district,HCD-district})

union

(select RESPIRATORY.tblUnit from RESPIRATORY, UNITS

where RESPIRATORY.tblUnit = UNITS.unitdesc

and $X{IN,UNITS.system,HCD-system}

and $X{IN,UNITS.divreg,HCD-divreg} 

and $X{IN,UNITS.district,HCD-district})

union

(select SEIZURES.tblUnit from SEIZURES, UNITS

where SEIZURES.tblUnit = UNITS.unitdesc

and $X{IN,UNITS.system,HCD-system}

and $X{IN,UNITS.divreg,HCD-divreg} 

and $X{IN,UNITS.district,HCD-district})

order by tblUnit;

 

 

[manshack_one]

 For reference, this is the actual query that returns the right data but without the parameters.  I'm trying to do the same thing but with the parameters HCD-system, HCD-divreg and HCD-district.

(select tblUnit from CAD, UNITS
where CAD.tblUnit = UNITS.unitdesc
and UNITS.system = 'UTMB'
and UNITS.divreg = 'OUTPATIENT'
and UNITS.district = 'SAN ANTONIO')

union

(select tblUnit from DIABETES, UNITS
where DIABETES.tblUnit = UNITS.unitdesc
and UNITS.system = 'UTMB'
and UNITS.divreg = 'OUTPATIENT'
and UNITS.district = 'SAN ANTONIO')

union

(select tblUnit from HEART, UNITS
where HEART.tblUnit = UNITS.unitdesc
and UNITS.system = 'UTMB'
and UNITS.divreg = 'OUTPATIENT'
and UNITS.district = 'SAN ANTONIO')

union

(select tblUnit from HLD, UNITS
where HLD.tblUnit = UNITS.unitdesc
and UNITS.system = 'UTMB'
and UNITS.divreg = 'OUTPATIENT'
and UNITS.district = 'SAN ANTONIO')

union

(select tblUnit from HTN, UNITS
where HTN.tblUnit = UNITS.unitdesc
and UNITS.system = 'UTMB'
and UNITS.divreg = 'OUTPATIENT'
and UNITS.district = 'SAN ANTONIO')

union

(select tblUnit from RESPIRATORY, UNITS
where RESPIRATORY.tblUnit = UNITS.unitdesc
and UNITS.system = 'UTMB'
and UNITS.divreg = 'OUTPATIENT'
and UNITS.district = 'SAN ANTONIO')

union

(select tblUnit from SEIZURES, UNITS

where SEIZURES.tblUnit = UNITS.unitdesc
and UNITS.system = 'UTMB'
and UNITS.divreg = 'OUTPATIENT'
and UNITS.district = 'SAN ANTONIO')

order by tblUnit;

[mdahlman]

Your idea seems fine. Arbitrary SQL is allowed (unions work for you in the hard-coded example). And lots of $X clauses should be fine.

Try it with just one $X in clause. Then two. I think it has to be just a little detail somewhere rather than any big problem.

-Matt