Post reply

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

  • 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

  • November 14, 2020 at 1:21 pm

    #3807831

    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

    ID	serialnumber	parentid	newCol
    10	10020		NULL		Multi
    40	10030		NULL		Real

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

  • November 16, 2020 at 12:51 am

    #3808131

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

  • 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)
    Intro to Tally Tables and Functions

  • November 16, 2020 at 7:15 pm

    #3808410

    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. 

  • November 16, 2020 at 8:50 pm

    #3808440

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • November 18, 2020 at 11:38 pm

    #3809412

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

  • November 18, 2020 at 11:39 pm

    #3809413

    it worked thank you!

  • November 19, 2020 at 2:15 am

    #3809451

    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)
    Intro to Tally Tables and Functions

  • November 19, 2020 at 2:44 am

    #3809452

    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

     

     

     

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

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