Re: DB2: Combining result sets
Not exactly, as I left out that there is also column D and E which may have different values. >From your comment it would appear that this is a lot more difficult.. On Tue, 19 Apr 2022 00:13:00 +0200 Bernd Oppolzer wrote: :>If the results in col1, col2 and col3 may be different for the same :>"something" condition :>in tables table1 thru table3, my solution is not correct. :> :>In this case, you need some sort of "select from table1 ... union all ... :>select from table2 where not exists (result from table1)" etc. etc. :> :>But this is a complete other requirement. You should maybe be more :>specific about :>what your targets are. :> :>Kind regards :> :>Bernd :> :> :>Am 19.04.2022 um 00:02 schrieb Bernd Oppolzer: :>> select col1, col2, col3, min (wherefound) :>> from (select col1, col2, col3, 'source 1' as wherefound :>> from table1 :>> where something :>> union ALL :>> select col1, col2, col3, 'source 2' as wherefound :>> from table2 :>> where something :>> union ALL :>> select col1, col2, col3, 'source 3' as wherefound :>> from table3 :>> where something) as t1 :>> group by col1, col2, col3 :>> :>> I changed the UNION to UNION ALL, BTW; :>> :>> kind regards :>> :>> Bernd :>> :>> :>> Am 18.04.2022 um 19:48 schrieb Binyamin Dissen: :>>> Seems that I have been knocked off of the DB2-L listserv. :>>> :>>> I am doing a union of three queries where it is possible that the :>>> critical :>>> columns are in more than one of the queries. :>>> :>>> For example: :>>> :>>> select col1, col2 col3, 'source 1' :>>> from table1 :>>> where something :>>> union :>>> select col1, col2, col3,, 'source 2' :>>> from table2 :>>> where something :>>> union :>>> select col1, col2, col3, 'source 3' :>>> from table3 :>>> where something :>>> :>>> I would like a single row even if the data (col1, col2, col3) is in :>>> more than :>>> one of the queries, so that 'source1' is returned if in table1 and :>>> table2 :>>> and/or table3, 'source 2' if not in table1 but in table2 (and perhaps :>>> table3) :>>> and 'source 3' if only in table 3. :>>> :>>> -- :>>> Binyamin Dissen :>>> http://www.dissensoftware.com :>>> :>>> Director, Dissen Software, Bar & Grill - Israel :>>> :>>> -- :>>> For IBM-MAIN subscribe / signoff / archive access instructions, :>>> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN :>> :>> -- :>> For IBM-MAIN subscribe / signoff / archive access instructions, :>> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN :> :>-- :>For IBM-MAIN subscribe / signoff / archive access instructions, :>send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- Binyamin Dissen http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2: Combining result sets
On Mon, 18 Apr 2022 20:48:13 +0300, Binyamin Dissen wrote: >Seems that I have been knocked off of the DB2-L listserv. > Sorry to hear that... Maybe re-apply because the real SQL specialists live over there. >I am doing a union of three queries where it is possible that the critical >columns are in more than one of the queries. > >For example: > > select col1, col2 col3, 'source 1' > from table1 > where something > union > select col1, col2, col3, 'source 2' > from table2 > where something > union > select col1, col2, col3, 'source 3' >from table3 >where something > >I would like a single row even if the data (col1, col2, col3) is in more than >one of the queries, so that 'source1' is returned if in table1 and table2 >and/or table3, 'source 2' if not in table1 but in table2 (and perhaps table3) >and 'source 3' if only in table 3. > To be tested: select col1, col2, col3, min(source_tbl) as first_tbl from ( select col1, col2, col3, 'source 1' as source_tbl from table1 where something union all select col1, col2, col3, 'source 2' as source_tbl from table2 where something union all select col1, col2, col3, 'source 3' as source_tbl from table3 where something ) as cols group by col1, col2, col3 ; This of course supposes that the source-table-identifying values you assign in the different sub queries have the values they have in your example. Cheers, Jantje. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2: Combining result sets
Ah, thank you! I got to the point where I thought I was pretty comfortable with SQL, but clearly I've been away longer than I realized. --- Bob Bridges, robhbrid...@gmail.com, cell 336 382-7313 /* Error saving file. Format drive now (Y/y)? */ -Original Message- From: IBM Mainframe Discussion List On Behalf Of Bernd Oppolzer Sent: Monday, April 18, 2022 18:03 select col1, col2, col3, min (wherefound) from (select col1, col2, col3, 'source 1' as wherefound from table1 where something union ALL select col1, col2, col3, 'source 2' as wherefound from table2 where something union ALL select col1, col2, col3, 'source 3' as wherefound from table3 where something) as t1 group by col1, col2, col3 I changed the UNION to UNION ALL, BTW; --- Am 18.04.2022 um 19:48 schrieb Binyamin Dissen: > Seems that I have been knocked off of the DB2-L listserv. > > I am doing a union of three queries where it is possible that the > critical columns are in more than one of the queries. > > For example: > > select col1, col2 col3, 'source 1' >from table1 >where something > union > select col1, col2, col3,, 'source 2' > from table2 >where something > union > select col1, col2, col3, 'source 3' > from table3 > where something > > I would like a single row even if the data (col1, col2, col3) is in > more than one of the queries, so that 'source1' is returned if in > table1 and table2 and/or table3, 'source 2' if not in table1 but in > table2 (and perhaps table3) and 'source 3' if only in table 3. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2: Combining result sets
If the results in col1, col2 and col3 may be different for the same "something" condition in tables table1 thru table3, my solution is not correct. In this case, you need some sort of "select from table1 ... union all ... select from table2 where not exists (result from table1)" etc. etc. But this is a complete other requirement. You should maybe be more specific about what your targets are. Kind regards Bernd Am 19.04.2022 um 00:02 schrieb Bernd Oppolzer: select col1, col2, col3, min (wherefound) from (select col1, col2, col3, 'source 1' as wherefound from table1 where something union ALL select col1, col2, col3, 'source 2' as wherefound from table2 where something union ALL select col1, col2, col3, 'source 3' as wherefound from table3 where something) as t1 group by col1, col2, col3 I changed the UNION to UNION ALL, BTW; kind regards Bernd Am 18.04.2022 um 19:48 schrieb Binyamin Dissen: Seems that I have been knocked off of the DB2-L listserv. I am doing a union of three queries where it is possible that the critical columns are in more than one of the queries. For example: select col1, col2 col3, 'source 1' from table1 where something union select col1, col2, col3,, 'source 2' from table2 where something union select col1, col2, col3, 'source 3' from table3 where something I would like a single row even if the data (col1, col2, col3) is in more than one of the queries, so that 'source1' is returned if in table1 and table2 and/or table3, 'source 2' if not in table1 but in table2 (and perhaps table3) and 'source 3' if only in table 3. -- Binyamin Dissen http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2: Combining result sets
select col1, col2, col3, min (wherefound) from (select col1, col2, col3, 'source 1' as wherefound from table1 where something union ALL select col1, col2, col3, 'source 2' as wherefound from table2 where something union ALL select col1, col2, col3, 'source 3' as wherefound from table3 where something) as t1 group by col1, col2, col3 I changed the UNION to UNION ALL, BTW; kind regards Bernd Am 18.04.2022 um 19:48 schrieb Binyamin Dissen: Seems that I have been knocked off of the DB2-L listserv. I am doing a union of three queries where it is possible that the critical columns are in more than one of the queries. For example: select col1, col2 col3, 'source 1' from table1 where something union select col1, col2, col3,, 'source 2' from table2 where something union select col1, col2, col3, 'source 3' from table3 where something I would like a single row even if the data (col1, col2, col3) is in more than one of the queries, so that 'source1' is returned if in table1 and table2 and/or table3, 'source 2' if not in table1 but in table2 (and perhaps table3) and 'source 3' if only in table 3. -- Binyamin Dissen http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2: Combining result sets
I should have mentioned that this is a cursor. On Mon, 18 Apr 2022 14:32:11 -0400 Bob Bridges wrote: :>Are col1, col2 and col3 going to be identical in all three tables? You :>don't need to know which table they came from? :> :>Offhand it seems to me you want a subquery, something like this: :> :> Select first col1 col2 col3, src :> From ( :>select col1, col2 col3, 'source 1' :>from table1 :>where something :> union :>select col1, col2, col3,, 'source 2' :>from table2 :>where something :> union :>select col1, col2, col3, 'source 3' :>from table3 :>where something) :> :>...Hm, it seems my SQL has collected a year or two's rust; I'd have to think :>out exactly how this would work. But maybe this is enough of a hint for you :>to get the rest of it (he finishes weakly). :> :>--- :>Bob Bridges, robhbrid...@gmail.com, cell 336 382-7313 :> :>/* One of the most practical of our present safeguards of privacy is the :>fragmented nature of personal information. It is scattered in little bits :>across the geography and years of our life. Retrieval is impractical and :>often impossible. A central data bank removes completely this safeguard. :>-Congressman Frank Horton, in the early 1970s before the Internet */ :> :>-Original Message- :>From: IBM Mainframe Discussion List On Behalf Of :>Binyamin Dissen :>Sent: Monday, April 18, 2022 13:48 :> :>Seems that I have been knocked off of the DB2-L listserv. :> :>I am doing a union of three queries where it is possible that the critical :>columns are in more than one of the queries. :> :>For example: :> :> select col1, col2 col3, 'source 1' :> from table1 :> where something :> union :> select col1, col2, col3,, 'source 2' :> from table2 :> where something :> union :> select col1, col2, col3, 'source 3' :>from table3 :>where something :> :>I would like a single row even if the data (col1, col2, col3) is in more :>than one of the queries, so that 'source1' is returned if in table1 and :>table2 and/or table3, 'source 2' if not in table1 but in table2 (and perhaps :>table3) and 'source 3' if only in table 3. :> :>-- :>For IBM-MAIN subscribe / signoff / archive access instructions, :>send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- Binyamin Dissen http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2: Combining result sets
Are col1, col2 and col3 going to be identical in all three tables? You don't need to know which table they came from? Offhand it seems to me you want a subquery, something like this: Select first col1 col2 col3, src From ( select col1, col2 col3, 'source 1' from table1 where something union select col1, col2, col3,, 'source 2' from table2 where something union select col1, col2, col3, 'source 3' from table3 where something) ...Hm, it seems my SQL has collected a year or two's rust; I'd have to think out exactly how this would work. But maybe this is enough of a hint for you to get the rest of it (he finishes weakly). --- Bob Bridges, robhbrid...@gmail.com, cell 336 382-7313 /* One of the most practical of our present safeguards of privacy is the fragmented nature of personal information. It is scattered in little bits across the geography and years of our life. Retrieval is impractical and often impossible. A central data bank removes completely this safeguard. -Congressman Frank Horton, in the early 1970s before the Internet */ -Original Message- From: IBM Mainframe Discussion List On Behalf Of Binyamin Dissen Sent: Monday, April 18, 2022 13:48 Seems that I have been knocked off of the DB2-L listserv. I am doing a union of three queries where it is possible that the critical columns are in more than one of the queries. For example: select col1, col2 col3, 'source 1' from table1 where something union select col1, col2, col3,, 'source 2' from table2 where something union select col1, col2, col3, 'source 3' from table3 where something I would like a single row even if the data (col1, col2, col3) is in more than one of the queries, so that 'source1' is returned if in table1 and table2 and/or table3, 'source 2' if not in table1 but in table2 (and perhaps table3) and 'source 3' if only in table 3. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
DB2: Combining result sets
Seems that I have been knocked off of the DB2-L listserv. I am doing a union of three queries where it is possible that the critical columns are in more than one of the queries. For example: select col1, col2 col3, 'source 1' from table1 where something union select col1, col2, col3,, 'source 2' from table2 where something union select col1, col2, col3, 'source 3' from table3 where something I would like a single row even if the data (col1, col2, col3) is in more than one of the queries, so that 'source1' is returned if in table1 and table2 and/or table3, 'source 2' if not in table1 but in table2 (and perhaps table3) and 'source 3' if only in table 3. -- Binyamin Dissen http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
