1 2 Previous Next 25 Replies Latest reply on Jul 27, 2019 2:53 PM by mathguy

    Query to get only not null columns

    4016797

      Hello All,

       

      I have a table where some of the columns will have values (not null) and some of them are null (no value) like below. Now I want to return columns in the select query which will have values (Not null).

       

      Can anyone please suggest how to proceed?

       

        • 1. Re: Query to get only not null columns
          John Thorton

          4016797 wrote:

           

          Hello All,

           

          I have a table where some of the columns will have values (not null) and some of them are null (no value) like below. Now I want to return columns in the select query which will have values (Not null).

           

          Can anyone please suggest how to proceed?

           

          Please click on the URL below

           

          How do I ask a question on the forums?

           

          Please provide requested details for #5 - #9 inclusive.

          • 2. Re: Query to get only not null columns
            jaramill

            Besides READING and ANSWERING the questions in the link that John Thorton posted, so in your example, for the three (3) rows you're expecting what?

            What is your select statement/query??

             

            Are you expecting to see for all rows in your dataset the following columns? (col1, col2, col3, col5, col6)?  Where only col4 has NULL value in all rows?

            Or are you expecting to see for all rows in your dataset only column 6 (since it has data in ALL rows)?

             

            Again read the link posted, and show us your data and tables.

            • 3. Re: Query to get only not null columns
              Mustafa KALAYCI

              hi,

               

              welcome to the community. I am not sure what you want exactly but even so why do you want to do this? what are you trying to achieve? also please show us desired output based on your sample data and also provide a copy and paste sample data using create table, insert scripts or at least "with clause".

              • 4. Re: Query to get only not null columns
                mathguy

                I think I understand what you are asking - but please confirm:

                 

                You may have a table like the one you show. It has six columns. One of the columns has NULL in every row (COL4).  You want to write a query that will only select all the OTHER columns from the table - but not COL4.

                 

                Is that it?

                 

                If so - the most important answer/question is what Mustafa asked in Reply 3. WHY do you want to do that? Most developers will likely tell you that there is no valid reason to want such a thing.

                 

                Then, assuming that somehow you do have a good reason to do it...  sorry, but it can't be done. Not easily, anyway. Not with standard SQL. What you want is a query that will output only certain columns, depending on the data in the table. Alas, a SQL SELECT statement must include the names of the columns to be returned (except something like SELECT *, which returns all the columns), and those must be hard-coded, they must be known at compilation time (before the data is seen). You could do something silly, like writing a small query to find which columns only have NULL in every row, and based on that to write a query that will output a single string, which is another SQL SELECT statement that selects only the "non-100%-NULL" columns. Then you could copy that output string, paste it back in your query editor, and execute it. It can be done; but again, why? That is not something you should do without a very, very, very good reason, which very likely doesn't exist.

                • 5. Re: Query to get only not null columns
                  MihaiF

                  Hi

                   

                  Can you please explain and show us what is the results you are expected?

                  Do you want to see only rows with all columns with values?

                   

                  Thanks,

                  MihaiF

                  • 6. Re: Query to get only not null columns
                    BluShadow

                    mathguy wrote:

                     

                    I think I understand what you are asking - but please confirm:

                     

                    You may have a table like the one you show. It has six columns. One of the columns has NULL in every row (COL4). You want to write a query that will only select all the OTHER columns from the table - but not COL4.

                     

                    Is that it?

                     

                     

                     

                     

                    Or it could be the other way... maybe he only wants column 6 because it's the only one to contain no nulls whatsoever.  The principle however would be similar.

                     

                    Until the OP replies we can only guess. 

                    • 7. Re: Query to get only not null columns
                      BluShadow

                      If you just want to analyse the data to get some flags for the columns you can do something like:

                       

                      SQL> with t(col1,col2,col3,col4,col5,col6) as (
                        2    select '123',cast(null as varchar(4)),'234',cast(null as varchar(4)),cast(null as varchar(4)),'678' from dual union all
                        3    select null, null, 'tyui', null, '789','456' from dual union all
                        4    select 'yui', '789', '456', null, 'har', '654' from dual
                        5    )
                        6  select case when count(*) = sum(case when col1 is null then 0 else 1 end) then 'Y' else 'N' end as col1_full
                        7        ,case when count(*) = sum(case when col1 is null then 1 else 0 end) then 'Y' else 'N' end as col1_null
                        8        ,case when count(*) = sum(case when col2 is null then 0 else 1 end) then 'Y' else 'N' end as col2_full
                        9        ,case when count(*) = sum(case when col2 is null then 1 else 0 end) then 'Y' else 'N' end as col2_null
                      10        ,case when count(*) = sum(case when col3 is null then 0 else 1 end) then 'Y' else 'N' end as col3_full
                      11        ,case when count(*) = sum(case when col3 is null then 1 else 0 end) then 'Y' else 'N' end as col3_null
                      12        ,case when count(*) = sum(case when col4 is null then 0 else 1 end) then 'Y' else 'N' end as col4_full
                      13        ,case when count(*) = sum(case when col4 is null then 1 else 0 end) then 'Y' else 'N' end as col4_null
                      14        ,case when count(*) = sum(case when col5 is null then 0 else 1 end) then 'Y' else 'N' end as col5_full
                      15        ,case when count(*) = sum(case when col5 is null then 1 else 0 end) then 'Y' else 'N' end as col5_null
                      16        ,case when count(*) = sum(case when col6 is null then 0 else 1 end) then 'Y' else 'N' end as col6_full
                      17        ,case when count(*) = sum(case when col6 is null then 1 else 0 end) then 'Y' else 'N' end as col6_null
                      18  from   t
                      19  /

                      C C C C C C C C C C C C
                      - - - - - - - - - - - -
                      N N N N Y N N Y N N Y N

                       

                      As mathguy indicates though, if you want a query that somehow magically only returns a dynamic set of columns based on the data, then you're out of luck, without writing dynamic code.

                      For that, you need to understand about SQL Projection, and we have a community document that details just that: PL/SQL 101 : Cursors and SQL Projection

                      • 8. Re: Query to get only not null columns
                        jaramill

                        BluShadow wrote:

                         

                        mathguy wrote:

                         

                        I think I understand what you are asking - but please confirm:

                         

                        You may have a table like the one you show. It has six columns. One of the columns has NULL in every row (COL4). You want to write a query that will only select all the OTHER columns from the table - but not COL4.

                         

                        Is that it?

                         

                         

                         

                         

                        Or it could be the other way... maybe he only wants column 6 because it's the only one to contain no nulls whatsoever. The principle however would be similar.

                         

                        Until the OP replies we can only guess.

                        You and I were thinking alike (as I presented him that scenario, along with the one mathguy mentioned) but unfortunately these newbie OPs expect us to be able to read minds like Professor Xavier from the X-Men

                        • 9. Re: Query to get only not null columns
                          4016797

                          Hello All,

                           

                          I am sorry, I just got to see all replies. Thanks a lot for all your replies. Here is what my expectation.

                           

                          From the result set I want to see below output.

                           

                          Row1: col1, col3, col6

                          Row2: col3, col5, col6

                          Row3: col1,col2,col3,col5,col6

                          • 11. Re: Query to get only not null columns
                            L. Fernigrini

                            That's not possible using SQL languaje. All rows returned by a query must have the same number of columns.

                             

                            Why do you need that?

                            How would you know that the 1st value shown in the second row is from col3 and not from col1?

                            • 12. Re: Query to get only not null columns
                              L. Fernigrini

                              That way OP will get 1 column with N concatenated values, while the request is to show "columns".

                               

                              Now I want to return columns in the select query which will have values (Not null).

                               

                              Your suggestion may be a valid workaround, but does not solves the exact (probably wrong) requirement).

                              • 13. Re: Query to get only not null columns
                                jaramill

                                As L. Fernigrini said....that's not possible.

                                 

                                SQL projection (which BluShadow mentioned an article earlier above) needs to have it's columns fixed when running your query.

                                The only other way is NDS (Native Dynamic SQL) and even then you'd be running separate queries.

                                In your example, you'd be building 3 dynamic SQL statements, with different number of columns.

                                • 14. Re: Query to get only not null columns
                                  4016797

                                  Hi Fernigrini,

                                   

                                  Here is my requirement. We have an excel template which will be used to update an existing customer entries. Lets say I have 10 customers in system and each and every customer has his own information in around 100 columns like customer location information, contact details, etc..

                                   

                                  Now we have an  standard template with all 100 columns and user can enter information whatever he wants to update in the template against customer number. After entering information he will submit template and data will be sitting in staging table. Now my program should read the records from staging table and should identify only those columns which are having values (not null), so that I will update only those columns not all. If I pass columns including null program will update null values for the existing customer records which I don't want. So I want to identify records which are having values.

                                   

                                  And in the template user may enter multiple records for multiple customers in the single upload. Please let me know if requirement is not clear.

                                  1 2 Previous Next