Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to determine the first column with null value Expand / Collapse
Author
Message
Posted Friday, February 22, 2013 1:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 8, Visits: 51
Hello

I have a table with 7 columns like Cumulative_cnt, AX1, AX2, AX3, AX4, AX5, POS

i want to find the first AX column with a 'null' and assign that column number to POS as a value

for e.g. if AX3 has the first null value then POS is assigned a value 3.

table is as follows

AX1-AX5 are varchar
all columns after the first column with a Null is Null
all columns preceding the first column with Null has a value
at least one column has a value and is not null which is usually AX1
Post #1423255
Posted Friday, February 22, 2013 2:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,093, Visits: 12,574
MadRazzi (2/22/2013)
Hello

I have a table with 7 columns like Cumulative_cnt, AX1, AX2, AX3, AX4, AX5, POS

i want to find the first AX column with a 'null' and assign that column number to POS as a value

for e.g. if AX3 has the first null value then POS is assigned a value 3.

table is as follows

AX1-AX5 are varchar
all columns after the first column with a Null is Null
all columns preceding the first column with Null has a value
at least one column has a value and is not null which is usually AX1


This screams of poor design but the query is pretty simple.

Update YourTable
set POS =
case
when AX1 is not null then 1
when AX2 is not null then 2
when AX3 is not null then 3
when AX4 is not null then 4
when AX5 is not null then 5
end



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1423259
Posted Friday, February 22, 2013 2:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 8, Visits: 51
what if ax2 through ax5 is null for a record

wouldn't the case statement assign value 5 to POS?
Post #1423272
Posted Friday, February 22, 2013 3:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 12,905, Visits: 32,182
MadRazzi (2/22/2013)
what if ax2 through ax5 is null for a record

wouldn't the case statement assign value 5 to POS?


no.
the case statement stops executing as soon as a match is found in the criteria is met.

other items are not evaluated. and they are evaluated in the order you put in the order that the case statement is created.


so it would return 2 in your example where 2-5 are null.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1423276
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse