Results 1 to 5 of 5
  1. #1
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1

    Question Unanswered: Help With Nested Case Statment

    Hi I am wondering if it possible to achieve, the result column, I'm trying to build a nested CASE statment with not much luck.

    lev1 Id - lev 5Id are seperate columns. as shown below



    Code:
    Lev1 Id	Lev2 Id	    Lev3 Id      Lev4 Id	 Lev5 Id	 Result
    01	01-13				                        01-13
    01	01-07	    01-07-01			                01-07-01
    01	01-09	    01-09-10	01-09-10-02	                01-09-10-02
    07	07-01	    07-01-01	07-01-01-90	07-01-01-90-08	07-01-01-90-08
    Here is the code I am using to construct the 5 columns of data, and my attempt at a nested Case statment

    Code:
    SELECT 'Lev1 Id'=B.BClassId, 'Lev2 Id'=B1.BClassId, 'Lev3 Id'=B2.BClassId, 'Lev4 Id'=B3.BClassId, 'Lev5 Id'=B4.BClassId, 'Lev6 Id'=B5.BClassId, 'test'=COALESCE(CASE WHEN B5.BClassId='' THEN B4.BClassId ELSE B5.BClassId END, CASE WHEN B1.BClassId='' THEN B.BClassId ELSE B1.BClassId END )
    FROM bclass As B
    
    left Join bclass B1 on B.BClassId = B1.BClassParent
    left Join bclass B2 on B1.BClassId = B2.BClassParent
    left Join bclass B3 on B2.BClassId = B3.BClassParent
    left Join bclass B4 on B3.BClassId = B4.BClassParent
    left Join bclass B5 on B4.BClassId = B5.BClassParent
    
    where len(B.bclassid)=2

    I would like the RESULT column to show the last column holding data, e.g. If lev2 id Is Null, Then Lev Id, If lev 3 Id Is Null, Then Lev 2 Id and so on

    hope this makes sense

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you differentiate between the 01 in levels 1, 2 and 3 in your data.
    can you supply the ddl of your tabel AND the data, ideally as INSERT INTO statement(s)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Hi Healdem

    DDl Uploaded

    Where you’re asking about differentiating 01 02 etc.., let me try and explain, we supply products for example apples pears & bananas
    Each product has a master Bclass
    01 – Apples (Level1)
    02 – Pears (Level1)
    03 – Bananas and so on (Level1)

    Each of these BClasses then breaks out (multiple times depending on product, brand – colour - flavour)

    01-01 Apples – Cox (Level2)
    01-01-01 Apples – Cox – Red (Level3)
    01-01-02 Apples – Cox – Green (Level3)
    01-02 Apples – Golden Delicious (Level2)
    02-01 Pears – Cox (Level2)
    02-01-01 Pears – Cox – Red (Level3)
    02-01-02 Pears – Cox – Green (Level3)

    From the code I have already supplied, using access and looking at the sql code, I created a simple if stament please see below, i would like to replicate the if statment in sql

    Code:
     IIf([Lev2BClass] Is Null,[Lev1BClass],IIf([Lev3BClass] Is Null,[Lev2BClass],IIf([Lev4BClass] Is Null,[Lev3BClass],IIf([Lev5BClass] Is Null,[Lev4BClass],IIf([Lev6BClass] Is Null,[Lev5BClass],[Lev6BClass]))))) AS FinalBClass
    creates my desired out put, I'm trying to replicate the above code directly onto the sql server

    Thanks
    Attached Files Attached Files
    Last edited by MarkWhyte; 10-07-13 at 05:45. Reason: add text

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    then you are going to have to store
    either
    the parent category tree at each level

    or

    not use 01 for more than one category

    if you allow more than one branch to use, say 01 as a category then its imp;ossible to walk the tree back up to the to level without additional information

    usually when Ive seen this sort of approach each individual category is unique
    so eother you need to store the exanded category each level OR enforce uniqueness of the category so that using your example 01 can only be a sub category of one parent type. Do you need to even know these numbers, at any stage do humans need to see the number or are they just internal system numbers

    your problem, is that based on your current dataset there is no way of differentiating between 01 in level 2 & 3
    what I'd suggest you do is to use an internal PK/FK eg
    table: Categories
    ID integer PK autonumber)
    Description varchar(50)
    Parent integer, nullable FK references Categories.ID

    Parent should be nullable (a null parent indicates the top level for each category tree
    but you cannot have 'cox' as the same category in both pears AND apples. yes the text may read cox, but in the background the cox variety in the pears tree is a different number to the cox variety in the apple tree
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Thank you for your input, here goes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •