March 14, 2008 at 1:26 pm
I have 2 tables as follows
create table ord1 (customer Char (10), division char(3),
ovnd_key Char(10))
insert into ord1 values ('RICHS', 'M', '12')
insert into ord1 values ('RICHS', 'M', '13')
insert into ord1 values ('RICHS', 'M', ' ')
create table PRCR (customer Char (10), division char(3),
ovnd_key Char(10), Ack_by char(1), sku_upc char(1))
insert into PRCR values ('RICHS', 'M', '12', 'O','N')
insert into PRCR values ('RICHS', 'M', ' ', 'P','N')
insert into PRCR values ('RICHS', 'M', '13', 'P','N')
insert into PRCR values ('RICHS', 'M', '14', 'O','N')
Now, I want to select rows from ord1 such that PRCR these conditions
satisfied:
1. customer and division should match with PRCR.
2. AND PRCR.ack_by = "P" AND PRCR.sku_upc <> "N"
3. If ord1.Ovnd_key match PRCR.Ovnd_key then Select that row from ord1
4. Otherwise find one in PRCR that has Ovnd_key = ''
Ex.
using above data, I should be selecting all rows other than ovnd_key='12'
I tried this
Select distinct h.ovnd_key,h.customer, h.division
from ord1 h
LEFT JOIN zzeoprcr c
ON h.customer = c.customer
And h.division=c.division
And h.oVnd_key = c.oVnd_key
LEFT JOIN zzeoprcr c2
ON h.customer=c2.customer
And h.division=c2.division
And c2.oVnd_key = ' '
where ( (c2.ack_by='P' And Not c2.sku_upc='N')
OR (c.ack_by='P' And Not c.sku_upc='N'))
But do not get correct result.
This selects the all rows, including with ovnd_key="12", but should not
because ack_by="O" in PRCR for '12'.
Any ideas.
Thanks.
March 14, 2008 at 1:48 pm
I must be missing something on this.
First, all of the inserts in your sample data have "N" in column "sku_upc", but you said you want <> "N", and your query reflects that. This means no rows show up at all.
Second, your query includes a table "zzeoprcr", but the table name is PRCR in the create script you provided.
I changed the select to PRCP, instead of zzeoprcr, and got 0 rows because of the N thing.
I changed that part of the select by removing "not". Then I got three rows.
Then I modified the query to actually do what you specified:
Select distinct h.ovnd_key,h.customer, h.division
from ord1 h
LEFT JOIN prcr c
ON h.customer = c.customer
And h.division=c.division
And
(h.oVnd_key = c.oVnd_key
or
h.oVnd_key != c.oVnd_key
and c.oVnd_key = ' ')
where c.ack_by='P' And c.sku_upc='N'
Except it is still keeping the sku_upc='N' in it, because all of the sample data has "N" in that column.
The results are per-spec. The oVnd_key of 12 shows up because it's that way in the ord1 table, and it successfully joins to the PRCR row that has Ack_by = ' '. I can't see a way to eliminate that without going outside your specifications.
So, please clarify your specifications and/or your sample data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 14, 2008 at 1:54 pm
Also, post what the result should be.
March 15, 2008 at 11:18 am
Ok. Admit the typos.
1. In the insert for ord1, the value for sku_upc should be 'U' in all.
This eliminates query clauses to be altered.
2. zzeopdcr should be replaced by PRCR.
As for results, I want to select rows from ord1 only if base conditions of
1. customer and division exist in PRCR
AND 2. PRCR has ack_by = 'P' AND sku_upc <> 'N'
such that if
2.1 ord1.Ovnd_key = prcr.Ovnd_key - then select the ord1 row.
else 2.2 select it if you find the (catch -all or default )
row in PRCR with Ovnd_key=' '
So In sample data provided, I would select rows in ord1 with Ovnd_key=' ' and '13'.
As for query itself, I just need a solution since mine obviously did not work.
Thanks.
March 17, 2008 at 7:30 am
Is that still confusing?
March 17, 2008 at 11:42 am
Try something like:
SELECT *
FROM ord1 H
WHERE EXISTS
(
    SELECT NULL
    FROM PRCR C
    WHERE C.customer = H.customer
        AND C.division = H.division
        AND C.ack_by = 'P'
        AND C.sku_upc <> 'N'
        AND C.ovnd_key IN (H.ovnd_key, '')
)
March 17, 2008 at 12:21 pm
Wow Ken! Did you really color those in yourself, or is there a way to cut & paste from a QA window that preserves the highlighting?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 17, 2008 at 12:24 pm
http://www.Simple-Talk.com has an SQL pretifier that does that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 9:08 pm
I finally got it working as follows:
SELECT *
FROM ord1 H
WHERE EXISTS
(
SELECT NULL
FROM PRCR C1
WHERE C1.customer = H.customer
AND C1.division = H.division
AND C1.ack_by = 'P'
AND C1.sku_upc <> 'N'
AND C1.ovnd_key = H.ovnd_key
)
OR EXISTS
(
SELECT NULL
FROM PRCR C1
WHERE C2.customer = H.customer
AND C2.division = H.division
AND C2.ack_by = 'P'
AND C2.sku_upc <> 'N'
AND C2.ovnd_key = ' '
AND NOT EXIST (
SELECT 1
FROM ord1 H
WHERE EXISTS (
SELECT NULL
FROM PRCR C3
WHERE C3.customer = H.customer
AND C3.division = H.division
AND C3.ack_by = 'P'
AND C3.sku_upc <> 'N'
AND C3.ovnd_key = H.ovnd_key
)
)
Not sure if there is another / better way.
Thanks.
March 18, 2008 at 1:59 pm
Glad you got it working.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 19, 2008 at 2:18 pm
Hi thumor,
Now here is the climax of this nerve wracking thriller :exclamation:
select distinct h.ovnd_key, h.customer, h.division
from ord1 h , prcr c
where h.customer = c.customer
and h.division = c.division
and h.oVnd_key = c.oVnd_key
and (c.ack_by='P' And Not c.sku_upc='N')
The result:
ovnd_key customer division
RICHS M
13 RICHS M
It is exactly same as you get from your code (Of course I removed the 😉 & 😉 )
SELECT *
FROM ord1 H
WHERE EXISTS
(
SELECT NULL
FROM PRCR C1
WHERE C1.customer = H.customer
AND C1.division = H.division
AND C1.ack_by = 'P'
AND C1.sku_upc <> 'N'
AND C1.ovnd_key = H.ovnd_key
)
OR EXISTS
(
SELECT NULL
FROM PRCR C2
WHERE C2.customer = H.customer
AND C2.division = H.division
AND C2.ack_by = 'P'
AND C2.sku_upc <> 'N'
AND C2.ovnd_key = ' '
AND NOT EXISTS (
SELECT 1
FROM ord1 H
WHERE EXISTS (
SELECT NULL
FROM PRCR C3
WHERE C3.customer = H.customer
AND C3.division = H.division
AND C3.ack_by = 'P'
AND C3.sku_upc <> 'N'
AND C3.ovnd_key = H.ovnd_key)
)
)
Now the catch :ermm::
The above results work with the sample data
insert into ord1 values ('RICHS', 'M', '12')
insert into ord1 values ('RICHS', 'M', '13')
insert into ord1 values ('RICHS', 'M', ' ')
insert into PRCR values ('RICHS', 'M', '12', 'O','U')
insert into PRCR values ('RICHS', 'M', ' ', 'P','U')
insert into PRCR values ('RICHS', 'M', '13', 'P','U')
insert into PRCR values ('RICHS', 'M', '14', 'O','U')
If you modify the data a little,
Update ord1 set ovnd_key = ‘Any Key’ where ovnd_key = ' '
then the above two queries (mine and yours 😉 go kaput. They will return (missing one row !!!)
The result:
customer division ovnd_key
RICHS M 13
Now the below code will do the job 😎
select distinct a.ovnd_key,a.customer, a.division
from
(select customer, division, ' ' ovnd_key_z, ovnd_key from ord1 h where ltrim(rtrim(cast(ovnd_key as varchar)))+'+'+ltrim(rtrim(cast(customer as varchar)))+'+'+ltrim(rtrim(cast(division as varchar)))
not in
(Select distinct ltrim(rtrim(cast(h.ovnd_key as varchar)))+'+'+ltrim(rtrim(cast(h.customer as varchar)))+'+'+ltrim(rtrim(cast(h.division as varchar)))
from ord1 h, prcr c
where
h.customer = c.customer
And h.division=c.division
And h.oVnd_key = c.oVnd_key)
union
select customer, division, ovnd_key, ovnd_key from ord1 h where ltrim(rtrim(cast(ovnd_key as varchar)))+'+'+ltrim(rtrim(cast(customer as varchar)))+'+'+ltrim(rtrim(cast(division as varchar)))
in
(Select distinct ltrim(rtrim(cast(h.ovnd_key as varchar)))+'+'+ltrim(rtrim(cast(h.customer as varchar)))+'+'+ltrim(rtrim(cast(h.division as varchar)))
from ord1 h, prcr c
where
h.customer = c.customer
And h.division=c.division
And h.oVnd_key = c.oVnd_key)) a,
PRCR b
where a.customer = b.customer
and a.division = b.division
and a.oVnd_key_z = b.oVnd_key
and b.ack_by='P' And Not b.sku_upc='N'
The result:
ovnd_key customer division
Any Key RICHS M
13 RICHS M
I hope it is clear. Enjoy :satisfied:
Best regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
March 20, 2008 at 8:31 am
Well, actually my query does not work.
Issue being the ovnd_key='13' insert into PRCR.
Without that insert, my query does not give desired results.
The above query seems to work fine.
🙂
March 20, 2008 at 10:01 am
Nice to know. A happy end to the hair raising thriller.
By the way, the issue with your query is not the ovnd_key='13'. The issue is if the value of ovnd_key <> ' ' or ''
Anyways, I am happy that your work is done. Let us know how is it actually used in a live system.
Best regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply