Page 1 of 1

UNION ALL

PostPosted: Sun Nov 07, 2010 2:12 pm
by Shakthivel V
Hi Everybody,
I have a requirement such as to fetch persons details such as last name,
first name etc, and his addresses. Each person can have five addresses. I
have to get all the details for all the patient in a single database
transaction. Also i Have to order by personcode which is unique for each
person. For this I tried something like this but it gives oracle exception -
The query i used is

Select 1, A.personcode, A. lastname, A. fistname FROM table1 A where
A.perosncode IN (1,2) ORDER BY A.personcode
Union All
Select 2, A.personcode, B.street1, B.street2 FROM table2 B where
B.personcode IN (1,2) ORDER BY B.personcode

I get the error as ORA-00900 : invalid SQL statement.

Any suggestions welcom..

Thanks in Advance

Re: UNION ALL

PostPosted: Sun Nov 07, 2010 4:41 pm
by NicC
For starters how about:
1 - cutting and pasting your query as you have errors in your column names as posted
2 - using the code tags to present your query in a fixed font so everything lines up
3 - I presume you need to match the personcode columns on both tables (WHERE A.PERSONCODE = B.PERSONCODE)

Re: UNION ALL

PostPosted: Sun Nov 07, 2010 10:12 pm
by dick scherrer
Hello,

Which database is being used?

The ORA-00900 is an Oracle error not DB2. . .

Re: UNION ALL

PostPosted: Tue Nov 09, 2010 3:31 pm
by GuyC
Select 2, A.personcode, ... FROM table2 B ...
will be rather difficult