Logic similar to EXIST to make all values the same if it meets a criteria
-
Hello everyone,
I have a question. Please let me know if someone has any ideas.
I need a logic with maybe EXIST(?) to check separately if for one Chain there is a field in REPLENTYPE with a value ‘SINGLE’
– I need all fields for this UPC at a specific Masterchainname to be ‘REPLENISHABLE’ for all STARTDATE weeks. So it should be based only on UPC#, and MASTERCHAINNAME.
SELECT DISTINCT UPC, STARTDATE, [MASTERCHAINNAME], REPLENTYPE,
CASE WHEN [REPLENTYPE] IS NULL THEN 'Not in CKB'
WHEN [REPLENTYPE] = 'NONE' THEN 'Non Replenishable'
ELSE 'Replenishable' END AS 'Replentype'
-
EXISTS will do it. But as you have not provided consumable data, I can't code it for you.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
SELECT DISTINCT UPC, STARTDATE, [MASTERCHAINNAME], REPLENTYPE,
CASE WHEN [REPLENTYPE] IS NULL THEN 'Not in CKB'
WHEN [REPLENTYPE] = 'NONE' THEN 'Non Replenishable'
ELSE 'Replenishable' END AS 'Replentype'
FROM dbo.tablename t1
WHERE NOT EXISTS(SELECT * FROM dbo.tablename t2 ON t2.UPC = t1.UPC AND t2.MASTERCHAINNAME = t1.MASTERCHAINNAME AND t2.REPLENTYPE <> 'Replenishable')SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Hello ScottPletcher. Thank you. I have a question.
I needed to change the clause ON to WHERE so that it runs.
However, with NOT EXISTS - I didn't have results, as you can see below.
When I changed to EXIST clause, it works now. I wanted to check if it is correct what I changed.
Is it what the query does?
Query calculates: if SINGLE then 'Replenishable'
If NONE then 'Non Replenishable'
If another value, then 'Replenishable' (because ELSE)
It checks separately if for one Chain there is a field in REPLENTYPE with a specific value, then it makes all values to be the same and it is not just for SINGLE Replen type, but for all Types.
I am also curious why in the subquery Replentype <> 'Replenishable'.
Right now in the actual table there are three REPLENTYPES: 1) None, 2) Prepack, 3) Single. NULL will be only if the Unit is not found.
Please let me know. Thank you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply