Post reply

get the negative and equal value of the other column

  • February 17, 2011 at 7:47 pm

    #233801

    Hi to all,

    Need help on this one, Basically the problem is this.

    If the ItemSubID is NULL then the ItemSubID must be the negative value of ItemSubValue. But if the ItemSubID is not NULL then the ItemSubID should be equal to the ItemSubValue

    CREATE TABLE #TempData(

    [ItemSubID] [int] NULL,

    [ItemSubValue] [int] NOT NULL)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,-1)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,1)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,2)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,3)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,4)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,5)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(2,-1)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(2,1)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(2,2)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(2,3)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(2,4)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(2,5)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,-2)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,6)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,7)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(NULL,8)

    So the output should look like this:

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(-1,-1)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(2,2)

    INSERT INTO #TempData ([ItemSubID],[ItemSubValue]) VALUES(-2,-2)

    Thanks in advance

    Vince

  • February 17, 2011 at 7:56 pm

    #1288615

    How about this?

    select ISNULL(ItemSubID ,ItemSubValue ) ItemSubID , ItemSubValue from #TempData

    where ( ItemSubID is null and ItemSubValue < 0 )

    or ItemSubID = ItemSubValue

  • February 17, 2011 at 8:11 pm

    #1288618

    thanks, that was perfect!

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

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