Querying a table to get the columns with values

  • mrodwell

    SSC Rookie

    Points: 32

    Morning, i have a table with over 100 columns and i need to query which columns have a value greater than Null. so i can then record this against another table which has the codes listed with another value. like a ordering page say to keep an eye on stock.

    i'm not sure i can give you examples as i don't even know where to start. at the moment i have 3 tables one with the details then 2 with the stock

     

    please help

  • DesNorton

    SSC-Insane

    Points: 23322

    Firstly, you cannot compare anything to null.  Run this code as proof.

    IF ( NULL > NULL ) PRINT 'NULL > NULL' ELSE PRINT 'CANNOT COMPARE NULL > NULL';
    IF ( 0 > NULL ) PRINT '0 > NULL' ELSE PRINT 'CANNOT COMPARE 0 > NULL';
    IF ( NULL > 0 ) PRINT 'NULL > 0' ELSE PRINT 'CANNOT COMPARE NULL > 0';

    No. If you give us a sample table structure and data, as well as expected outcome, we can try and assist.  There is no need for 100 columns - provide just enough columns to illustrate your problem. (See the link in my signature)

  • Thom A

    SSC Guru

    Points: 98776

    What do you mean by "greater than NULL"? In SQL NULL has the lowest value than any other value, regardless of data type, so are you simply after values that have a non-NULL value? If not, what counts as having a "lower" value of NULL in your set up?

    Also, I don't follow what you mean by a column having a value greater than NULL. Tables are made up or rows and columns; a single column won't have a valu "greater than" something else, it'll be a specific value in a row in that column that will.

    Sample data and expected results will likely help us understand what you're trying to describe here; I suspect some important information is being lost in translation.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Grant Fritchey

    SSC Guru

    Points: 396751

    Sounds like you want to find the rows where a column (or columns) actually have data, meaning, they're NOT NULL. If that's what you're looking for, then that's the phrase that will help you. You may also have to add a > 0, it we're talking about a numeric value.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 4 (of 4 total)

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