manshack_one Posted March 18, 2010 Share Posted March 18, 2010 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, UNITSwhere $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, UNITSwhere $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, UNITSwhere $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, UNITSwhere $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, UNITSwhere $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, UNITSwhere $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, UNITSwhere $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district})order by tblUnit; Link to comment Share on other sites More sharing options...
mdahlman Posted March 18, 2010 Share Posted March 18, 2010 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 Link to comment Share on other sites More sharing options...
manshack_one Posted March 18, 2010 Author Share Posted March 18, 2010 Weird, not sure where the other half of those enclosures went to. I'll go double check it. Link to comment Share on other sites More sharing options...
manshack_one Posted March 18, 2010 Author Share Posted March 18, 2010 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, UNITSwhere CAD.tblUnit = UNITS.unitdescand $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, UNITSwhere DIABETES.tblUnit = UNITS.unitdescand $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, UNITSwhere HEART.tblUnit = UNITS.unitdescand $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, UNITSwhere HLD.tblUnit = UNITS.unitdescand $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, UNITSwhere HTN.tblUnit = UNITS.unitdescand $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, UNITSwhere RESPIRATORY.tblUnit = UNITS.unitdescand $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, UNITSwhere SEIZURES.tblUnit = UNITS.unitdescand $X{IN,UNITS.system,HCD-system}and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district})order by tblUnit; Link to comment Share on other sites More sharing options...
manshack_one Posted March 18, 2010 Author Share Posted March 18, 2010 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, UNITSwhere CAD.tblUnit = UNITS.unitdescand UNITS.system = 'UTMB'and UNITS.divreg = 'OUTPATIENT'and UNITS.district = 'SAN ANTONIO')union(select tblUnit from DIABETES, UNITSwhere DIABETES.tblUnit = UNITS.unitdescand UNITS.system = 'UTMB'and UNITS.divreg = 'OUTPATIENT'and UNITS.district = 'SAN ANTONIO')union(select tblUnit from HEART, UNITSwhere HEART.tblUnit = UNITS.unitdescand UNITS.system = 'UTMB'and UNITS.divreg = 'OUTPATIENT'and UNITS.district = 'SAN ANTONIO')union(select tblUnit from HLD, UNITSwhere HLD.tblUnit = UNITS.unitdescand UNITS.system = 'UTMB'and UNITS.divreg = 'OUTPATIENT'and UNITS.district = 'SAN ANTONIO')union(select tblUnit from HTN, UNITSwhere HTN.tblUnit = UNITS.unitdescand UNITS.system = 'UTMB'and UNITS.divreg = 'OUTPATIENT'and UNITS.district = 'SAN ANTONIO')union(select tblUnit from RESPIRATORY, UNITSwhere RESPIRATORY.tblUnit = UNITS.unitdescand UNITS.system = 'UTMB'and UNITS.divreg = 'OUTPATIENT'and UNITS.district = 'SAN ANTONIO')union(select tblUnit from SEIZURES, UNITSwhere SEIZURES.tblUnit = UNITS.unitdescand UNITS.system = 'UTMB'and UNITS.divreg = 'OUTPATIENT'and UNITS.district = 'SAN ANTONIO')order by tblUnit; Link to comment Share on other sites More sharing options...
mdahlman Posted March 19, 2010 Share Posted March 19, 2010 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now