Using If exists trying to assign the value to a variable when it exists.

Question

Post reply

Using If exists trying to assign the value to a variable when it exists.

Mond

Old Hand

Points: 356
More actions
November 14, 2020 at 12:23 am
Go to Answer
#3807724

Hello
Hope you can help it seems easy but not for me 🙁
I have the following table
id serial parentid
10 10020 Null
20 00234 10
30 00235 10
40 10030 Null
I need to show 'Mixed' when the serial has childs and value starts with 1 for examplein the table first record--> 10020 will show 'Mixed' , second and third record wont show and fourth record will show 'Real' because value starts with 1 and doesnt have any childs
So for this Im trying to use the following statements
Declare @PalletContent table (ID int, serialnumber varchar(max), parentid nvarchar(max))
declare @packageid as int
Insert into @PalletContent(id,serialnumber,ParentId)
Select id,serialnumber,ParentId from Package
where LoadPlanId=197602
set @packageid=(Select id from @PalletCount)
If exists (Select 1 from @PalletContent where parentid=@packageid) then 'Mixed Pallet' else 'Real'
But not working 🙁 First is not bringing me the packageid in the assignation of the value and also I noticed If exists just return a true false if exists. How can I assign the value found to a variable to show this value is 'Mixed' or 'Real'
Hope I explain myself good. Ill appreciate your help on this Thanks a lot

Jeff Moden

SSC Guru

Points: 1004401
More actions
November 16, 2020 at 6:22 am
Answer
#3808173
Just my 2 cents...
```
 SELECT parent.*
        ,NewCol = CASE WHEN oa.Cnt > 0 THEN 'Mixed' ELSE 'Real' END
   FROM @PalletContent parent
  OUTER APPLY (SELECT TOP 1 Cnt = 1 FROM @PalletContent child WHERE child.ParentID = parent.id) oa
  WHERE parent.ParentID IS NULL
;
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Collins SSCrazy Points: 2169 More actions · Answer 1

Maybe you're looking for something like this

Declare @PalletContent table (ID                int,
                              serialnumber      varchar(100),
                              parentid          int);

Insert into @PalletContent(id,serialnumber,ParentId) values
(10, '10020', Null),
(20, '00234', 10),
(30, '00235', 10),
(40, '10030', Null);

with unq_parent_cte(ParentId) as (
    select distinct ParentId 
    from @PalletContent
    where ParentId is not null)
select pc.*, case when upc.ParentId is null then 'Real' else 'Multi' end newCol
from @PalletContent pc
     left join unq_parent_cte upc on pc.ID=upc.ParentId
where left(serialnumber, 1)='1';

output

IDserialnumberparentidnewCol
1010020NULLMulti
4010030NULLReal

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Mond Old Hand Points: 356 More actions · Answer 2

Thanks so much Ill try tomorrow and I will let you know the results!! thanks so much again!

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

In the future, would you please follow forum netiquette? We would actually like to see DDL and not have to create it for ourselves. It would also help if you didn't believe in generic "id", non-specified "serial" and a poorly designed "parent_id"; I believe your bad design is forcing you into elaborate almost procedural code. Have you ever been to a library? Did you notice that the Dewey Decimal Classification system is hierarchical? That means that 500 through 599 encodes the sciences. Then within that hierarchy, the 510s are in mathematics. Etc. I think if you will sit down and actually design your serial numbers, you'll be able to do extract parent IDs, with simple string functions.

Please post DDL and follow ANSI/ISO standards when asking for help.

ScottPletcher SSC Guru Points: 101117 More actions · Answer 4

In this specific case, I'd recommend you add a couple of triggers to the table to maintain an accurate flag for this in the parent row.

You'd need an INSERT trigger and an DELETE trigger (assuming you can't UPDATE the parentId).

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

Mond Old Hand Points: 356 More actions · Answer 5

Thanks so much. this logic also works. Ill appreciate it thank you!

Mond Old Hand Points: 356 More actions · Answer 6

Mond

Old Hand

Points: 356

November 18, 2020 at 11:39 pm

#3809413

it worked thank you!

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 7

Mond wrote:

it worked thank you!

What was it that worked for you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mond Old Hand Points: 356 More actions · Answer 8

yes this logic

thank you so much

SELECT parent.*

,NewCol = CASE WHEN oa.Cnt > 0 THEN 'Mixed' ELSE 'Real' END

FROM @PalletContent parent

OUTER APPLY (SELECT TOP 1 Cnt = 1 FROM @PalletContent child WHERE child.ParentID = parent.id) oa

WHERE parent.ParentID IS NULL