Logic similar to EXIST to make all values the same if it meets a criteria

Question

Logic similar to EXIST to make all values the same if it meets a criteria

JeremyU

SSChasing Mays

Points: 640
More actions
July 1, 2024 at 6:24 pm

#4432762
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'
```

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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 Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

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".

JeremyU SSChasing Mays Points: 640 More actions · Answer 3

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.

Screenshot4

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'.

Screenshot2

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.

screenshot3

Please let me know. Thank you.

This reply was modified 4 months, 3 weeks ago by JeremyU.
This reply was modified 4 months, 3 weeks ago by JeremyU.
This reply was modified 4 months, 2 weeks ago by JeremyU.