| |
| |
| |
|
Page: 1 2 3 4 5
Comments:
<0> You beat me to it.
<0> Not all rdbms have intersect though.
<0> For some reason.
<1> Funny considering I use a DB that don't support it :)
<2> actually ive never seen intersect...
<2> :(
<3> I've never heard of intersect
<1> It's a UNION operator to return the row only if the values appear in both selects.
<2> Halo_Four: i understand what it means.. i just never seen it in any rdbms
<2> or rather never used it or seen it used
<1> oh wait, SQL Server 2005 does have INTERSECT
<0> 2k doesn't, I think.
<2> ah ok
<4> thats the close button
<0> Nice that they added it, even though I almost never need it :)
<2> havnet got a chance to paly with 2k5
<1> Well tickle my nuts with a feather and call me Sally.
<2> been **** with db2 and oracle lately
<1> They also added EXCEPT
<0> Oracle has intersect :)
<2> ya but i dont use oracle that much :)
<0> hehe
<3> Halo_Four: UNION DISTINCT in DB2 does that
<0> cubert: Nope. Union distinct returns one copy of it, but it can appear in only 1 result set and it'll still come back.
<2> no union distinct will give you an or and then only give you the distinct ones
<2> its still an or
<3> ah.. okay..
<0> intersect the value has to appear at least once in every query or it doesn't come back
<1> UNION in MSSQL does that by default, to return it twice you'd have to use UNION ALL
<3> I misread the documentation
<3> Halo_Four: that's the default in DB2 also
<3> according to this site I found DB2 has INTERSECT and EXCEPT
<2> just use a regular and dude
<3> you mean OR ;)
<3> in my case
<2> well or if you want the same behaviour as the union
<2> but u said you didnt
<0> I'm so confused! ;)
<3> I want the subselect to only return records that have all three of those item numbers
<3> the UNION did that
<1> It shouldn't
<1> If only one of those subselects returned the value it would be included in the final resultset.
<0> If it did, its just a fluke of your data
<2> ya u need an intersect... an AND
<0> Because that's not how its written.
<3> weird
<3> I can't use AND because it's three separate rows with the item numbers. There's only one item number per row, I need to group up all the order numbers that have all three item numbers I specified
<0> Either that, or you **** at explaining what you're trying to do ;)
<3> Drk`Angel: there is always that problem :P
<3> brb
<2> select a.id from table1 a, table1 b, table1 c where a.id = b.id and b.id = c.id and a.id = c.id and a.somecol = 'blah1' and b.somecol = 'blah2' and c.somecol = 'blah3'
<3> SicLuDe: okay, I'll see if I can make something like that work
<3> my data isn't structured that way
<2> so change it
<2> change the query the way you want
<2> you wont be spoonfed everything
<2> heh
<3> I'm not asking to be spoonfed.
<3> I'm just sure how to tell it to give me three different rows from the same table.
<2> a.somecol = 'blah1' and b.somecol = 'blah2' and c.somecol = 'blah3'
<5> UNION would be one way
<2> will giv eyou 3 diff rows
<5> SicLuDe: that might return no rows
<3> so FROM table1 as a, table1 as b and table1 as c?
<5> SELECT col FROM table WHERE col = 'foo' UNION SELECT col FROM table WHERE col = 'bar' UNION SELECT col FROM table WHERE col = 'foobar'
<2> MuteThis: he wants an intersect not a union
<2> atleast thats the way he has been explaining it
<5> UNION ALL
<2> MuteThis: that doesnt help :)
<2> that just foregoes the distinct
<3> MuteThis: http://sql-servers.com/nopaste/?show=264
<3> that gives me an error saying UNION is not valid
<5> you'd need to do....
<3> someone suggested I replace it with an OR instead of all the UNION, and that appears to work
<5> http://sql-servers.com/nopaste/?show=265
<5> yeah i don't know what you're doing
<3> oh lord
<3> that would take about eleventy billion years to run :P
<5> all you want is to filter based on poitem?
<3> this seems to work: http://sql-servers.com/nopaste/?show=266
<5> why not joing WMBIRD.OPENPOD in to your base query?
<3> POC are header records, one per order
<3> POD are detail records, one record for every order line
<3> I need all the order headers where there are line records with all three of the item numbers I listed
<0> Yeah, you'd want intersect for that, not union.
<3> so the OR thing probably isn't working, right?
<3> and can I use intersect in a WHERE clause?
<0> No, that won't give you what you want either.
<5> yeah, so why not join WMBIRD.POITEM into WMBIRD.OPENPOC on POC>POREF# = POD.POITEM
<5> and then do WHERE POD.POITEM IN ('','','')
<3> because POITEM has to match **ALL** those item numbers
<3> which means it's spanning multiple POD records
<0> Easiest way to do it would be an intersect subquery similar to what you have, but I'm sure there's a better way.
<0> I just can't think of it right now. :)
<5> how about using an exists then
<5> or what you have is fine
<3> I dunno... I've never heard of exists, I'll check my reference
<5> for clarify i might change the OR's to IN()
<5> clarity
<0> Could probably do a triple join to that table.
<0> Yeah, that'd work.
<0> maybe. hm.
<0> eh, eff it. Subquery with intersect. ;)
<3> can't do that in a where clause :-\
<5> stupid question, if it works as is, what the fuss?
<3> it doesn't work as is
<3> I think this does though: http://sql-servers.com/nopaste/?show=267
<6> i dont think all those subqueries are necessary; can't you just use joins?
<3> not any way that I know of
<3> Table 1 is order headers
<3> Table 2 is order details
<3> or in my case POC and POD
<3> I want a single record returned when there are order detail records containing three separate items, and the order must contain all three
<6> ah it's way too early for me to think about this
<6> hehe
<3> so it's grouping up all the lines and making sure there is a single order number with three item numbers ***ociated with it
<6> i think you can still join them up though
<3> how can I use a join to match one table to three separate rows in another table simultaneously?
<5> http://sql-servers.com/nopaste/?show=268
<6> join 3 times
<5> http://sql-servers.com/nopaste/?show=269
<5> the exists version
<5> oops, get rid of that in
<7> MuteThis: get it right ...geez
<5> :'(
<3> which IN?
<3> the WHERE EXISTS *IN*?
<5> yes
<3> ah, okay
<3> should this execute faster, or is it just semantically cleaner?
<5> i think EXISTS runs faster than IN, they probably both get optimized to the same damn thing
<2> exist garuntees the better execution plan
<2> in MIGHT get you the good execution plan
<3> *hehe*
<3> considering some retard built this database with no indexes, I'll take all the help I can get
<2> well then again i am not too familiar with the performance on db2
<5> add the indexes in
<5> and rename those fsking fields
<2> databases on db2 from what i have seen arent all that greatly designed
<3> it's our ERP, I can't
<2> MuteThis: db2 usually means enterprise
<2> meaning its impossible to get any changes
<2> without going through 20 million people
<3> oh I can make changes, but they come back and undo them
<2> same deal
<3> :)
<5> well you could sneak some indexes in
<3> since it's DB2 on an AS/400 they also incorporate these damned EBCDIC files that I can't touch with SQL. I have to go around my *** to get to my elbow and copy the file out to another intermediate structure, query it, then destroy my copy before they find out and have a fit.
Return to
#sql
or
Go to some related
logs:
#politics
#politics
#politics
Cold Anatomy of a tidal wave
#beginner
#gentoo
#sex
#gentoo
#worldcup
#hardware
|
|