How to "get rid of" empty columns in a query 1

[goranm]

Hi!

We have an Address table with a total of nine different name and address fields.
Sometimes every field is filled, but mostly there are several empty columns (NULL).

Now I want to create a SQL-query with a result where I can "move" all fields upwards, so that every empty column appears at the end.

For example:
FIELD
Name1 - John
Name2 -
Name3 - Big Company
Name4 -
Name5 -
Adr1 -
Adr2 - Main street
Adr3 -
Adr4 - Box 55

I want:
AdrInfo1 - John
AdrInfo2 - Big Company
AdrInfo3 - Main street
AdrInfo4 - Box 55
AdrInfo5 -
AdrInfo6 -
AdrInfo7 -
AdrInfo8 -
AdrInfo9 -

Does anyone have a solution for that?
Maybe with a "select case"?

/Goran

 

[SantaMufasa]

I want to create a SQL-query with a result where I can "move" all fields upwards...

I infer from your comments, Goran, that you are outputting the result set vertically (for use in labels, perhaps). In such case, I also infer that you don't really need/want labels "AdrInfo1...AdrInfo9" to appear as part of the result set. Is that a correct presumption on my part? Do you simply want vertical output with the NULL address entries "pushed" downward following the non-NULL entries?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Goran,

Here is code that does what you want:

Section 1 -- Sample Data:

select * from address;

NAME1                NAME2                NAME3                NAME4                NAME5               
-------------------- -------------------- -------------------- -------------------- --------------------
John                                      Big Company                                                   
Oprah                                                                                                   
George               Walker               Bush                 President            Of The United States

ADR1                 ADR2                 ADR3                      ADR4
-------------------- -------------------- ------------------------- -----------------------
                     Main Street                                    Box 55
                                                                    Chicago, IL
Oval Office          White House          1600 Pennsylvania Ave.    Washington, D.C., 20500

Section 2 -- Query Code:

col x Heading "Address Lines" format a25
select decode(name1,null,null,name1||chr(10))
     ||decode(name2,null,null,name2||chr(10))
     ||decode(name3,null,null,name3||chr(10))
     ||decode(name4,null,null,name4||chr(10))
     ||decode(name5,null,null,name5||chr(10))
     ||decode(adr1,null,null,adr1||chr(10))
     ||decode(adr2,null,null,adr2||chr(10))
     ||decode(adr3,null,null,adr3||chr(10))
     ||decode(adr4,null,null,adr4||chr(10))
     ||decode(name1,null,'-'||chr(10))
     ||decode(name2,null,'-'||chr(10))
     ||decode(name3,null,'-'||chr(10))
     ||decode(name4,null,'-'||chr(10))
     ||decode(name5,null,'-'||chr(10))
     ||decode(adr1,null,'-'||chr(10))
     ||decode(adr2,null,'-'||chr(10))
     ||decode(adr3,null,'-'||chr(10))
     ||decode(adr4,null,'-'||chr(10))
     x
  from address
 order by name1,name2   
/

Address Lines
-------------------------
George
Walker
Bush
President
Of The United States
Oval Office
White House
1600 Pennsylvania Ave.
Washington, D.C., 20500

John
Big Company
Main Street
Box 55
-
-
-
-
-

Oprah
Chicago, IL
-
-
-
-
-
-
-

I displayed hyphens in place of the missing fields so that you can easily see the missing items. You can remove the "'-'||" code strings to then produce blanks instead.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[goranm]

Hi!

Thank you SantaMufasa for helping me.
Just now I can't test i, but I will try to do it tomorrow.

Maybe it need some smaller changes.
I actually planned to create a view with nine columns from the query, where all the information was at top (therefore the column name "AdrInfo1" etc) with no spaces between Name and Adress info.
(the view will also have some Id columns).

I will use the view in a Crystal Report, and join it to another table.
I have to put every column in the report, but want to show all filled fields att the top.

I hope you understand my needs.

Thanks
/Goran

 

[SantaMufasa]

Okay then, Goran, here is code to create the view that you want:

create or replace view addrs
as
select substr(substr(x,instr(x,'~',1,1)+1,instr(x,'~',1,2)-instr(x,'~',1,1)-1),1,25) adrinfo1
      ,substr(substr(x,instr(x,'~',1,2)+1,instr(x,'~',1,3)-instr(x,'~',1,2)-1),1,25) adrinfo2
      ,substr(substr(x,instr(x,'~',1,3)+1,instr(x,'~',1,4)-instr(x,'~',1,3)-1),1,25) adrinfo3
      ,substr(substr(x,instr(x,'~',1,4)+1,instr(x,'~',1,5)-instr(x,'~',1,4)-1),1,25) adrinfo4
      ,substr(substr(x,instr(x,'~',1,5)+1,instr(x,'~',1,6)-instr(x,'~',1,5)-1),1,25) adrinfo5
      ,substr(substr(x,instr(x,'~',1,6)+1,instr(x,'~',1,7)-instr(x,'~',1,6)-1),1,25) adrinfo6
      ,substr(substr(x,instr(x,'~',1,7)+1,instr(x,'~',1,8)-instr(x,'~',1,7)-1),1,25) adrinfo7
      ,substr(substr(x,instr(x,'~',1,8)+1,instr(x,'~',1,9)-instr(x,'~',1,8)-1),1,25) adrinfo8
      ,substr(substr(x,instr(x,'~',1,9)+1,instr(x,'~',1,10)-instr(x,'~',1,9)-1),1,25) adrinfo9
  from (select '~'
            ||decode(name1,null,null,name1||'~')
            ||decode(name2,null,null,name2||'~')
            ||decode(name3,null,null,name3||'~')
            ||decode(name4,null,null,name4||'~')
            ||decode(name5,null,null,name5||'~')
            ||decode(adr1,null,null,adr1||'~')
            ||decode(adr2,null,null,adr2||'~')
            ||decode(adr3,null,null,adr3||'~')
            ||decode(adr4,null,null,adr4||'~')
            ||decode(name1,null,'~')
            ||decode(name2,null,'~')
            ||decode(name3,null,'~')
            ||decode(name4,null,'~')
            ||decode(name5,null,'~')
            ||decode(adr1,null,'~')
            ||decode(adr2,null,'~')
            ||decode(adr3,null,'~')
            ||decode(adr4,null,'~')
            x
         from address) y
/

View created.

SQL> desc addrs

 Name                    Null?    Type
 ----------------------- -------- ------------
 ADRINFO1                         VARCHAR2(25)
 ADRINFO2                         VARCHAR2(25)
 ADRINFO3                         VARCHAR2(25)
 ADRINFO4                         VARCHAR2(25)
 ADRINFO5                         VARCHAR2(25)
 ADRINFO6                         VARCHAR2(25)
 ADRINFO7                         VARCHAR2(25)
 ADRINFO8                         VARCHAR2(25)
 ADRINFO9                         VARCHAR2(25)

select * from addrs;

ADRINFO1                  ADRINFO2                  ADRINFO3                  ADRINFO4                  ADRINFO5                
------------------------- ------------------------- ------------------------- ------------------------- --------------------
John                      Big Company               Main Street               Box 55
Oprah                     Chicago, IL
George                    Walker                    Bush                      President                 Of The United States

ADRINFO6                  ADRINFO7                  ADRINFO8                  ADRINFO9
------------------------- ------------------------- ------------------------- -----------------------


Oval Office               White House               1600 Pennsylvania Ave.    Washington, D.C., 20500

3 rows selected.

Let us know if that's what you wanted.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[goranm]

Hi again!

You are really super.

I have tried the script and it was just perfect (but I don't understand how it works).

It also seems that I need your help again.

I tried to add additional fields in the begin of the view but it failed.
Evidently it wasn't just to put them there.
Does it need a special syntax?

I want to add this three fields first in the view.

AddressId,
Address_qualifier,
OwnerId

Another question.
Does the "1,25" in the end of each row extract the 25 first characters in each field?
In that case, I don't need that, because I need to have the entire field since it can contain up to 40 characters.

Once again thank you for all your help.

/Goran

 

[SantaMufasa]

Does the "1,25" in the end of each row extract the 25 first characters in each field?

Well, actually, it extracts the first 25 characters of the calculated maximum of "x", which is the total maximum length of all "name<n>" columns, plus the total maximum length of all "adr<n>" columns, plus the total length of all literal "~" delimiter characters. So, if your "name<n>" columns and your "adr<n>" columns are each 40 characters in length, then the calculated length of the concatenation of all of the expressions forming column "x", is:[ul][li]9 (columns) x 40 (maximum length of each column) = 360, plus[/li][li]19 (iterations of "~") x 1 (length of each "~") = 19[/li][/ul]...totaling 379 characters maximum for each of "ADRINFO1" thru "ADRINFO9".



So, if you remove the "substr(...1,25)" functions, then each of "ADRINFO1" thru "ADRINFO9" will show as "VARCHAR2(379)". I propose that if your columns are each "VARCHAR2(40)", then you modify my code, above, from "1,25" to "1,40".

I tried to add additional fields in the begin of the view but it failed.

I have added those three columns, and sample values for each, to my copy of your base table, "ADDRESS":

desc address

 Name              Null?    Type
 ----------------- -------- ------------
 ADDRESSID                  NUMBER
 ADDRESS_QUALIFIER          VARCHAR2(5)
 OWNERID                    NUMBER
 NAME1                      VARCHAR2(40)
 NAME2                      VARCHAR2(40)
 NAME3                      VARCHAR2(40)
 NAME4                      VARCHAR2(40)
 NAME5                      VARCHAR2(40)
 ADR1                       VARCHAR2(40)
 ADR2                       VARCHAR2(40)
 ADR3                       VARCHAR2(40)
 ADR4                       VARCHAR2(40)

SELECT * FROM ADDRESS;

ADDRESS ADDRESS_
     ID QUALIFIER OWNERID NAME1       NAME2       NAME3       NAME4       NAME5               
------- --------- ------- ----------- ----------- ----------- ----------- --------------------
      1 ABC           101 John                    Big Company                                   
      2 ABC           201 Oprah                                                                 
      3 ABC           301 George      Walker      Bush        President   Of The United States

ADR1        ADR2        ADR3                      ADR4
----------- ----------- ------------------------- -------------------------
            Main Street                           Box 55
                                                  Chicago, IL
Oval Office White House 1600 Pennsylvania Ave.    Washington, D.C., 20500

I have also modified the code, from above, to accommodate the three additional columns in your resulting view:

create or replace view addrs
as
select AddressID,Address_Qualifier, OwnerID
      ,substr(substr(x,instr(x,'~',1,1)+1,instr(x,'~',1,2)-instr(x,'~',1,1)-1),1,40) adrinfo1
      ,substr(substr(x,instr(x,'~',1,2)+1,instr(x,'~',1,3)-instr(x,'~',1,2)-1),1,40) adrinfo2
      ,substr(substr(x,instr(x,'~',1,3)+1,instr(x,'~',1,4)-instr(x,'~',1,3)-1),1,40) adrinfo3
      ,substr(substr(x,instr(x,'~',1,4)+1,instr(x,'~',1,5)-instr(x,'~',1,4)-1),1,40) adrinfo4
      ,substr(substr(x,instr(x,'~',1,5)+1,instr(x,'~',1,6)-instr(x,'~',1,5)-1),1,40) adrinfo5
      ,substr(substr(x,instr(x,'~',1,6)+1,instr(x,'~',1,7)-instr(x,'~',1,6)-1),1,40) adrinfo6
      ,substr(substr(x,instr(x,'~',1,7)+1,instr(x,'~',1,8)-instr(x,'~',1,7)-1),1,40) adrinfo7
      ,substr(substr(x,instr(x,'~',1,8)+1,instr(x,'~',1,9)-instr(x,'~',1,8)-1),1,40) adrinfo8
      ,substr(substr(x,instr(x,'~',1,9)+1,instr(x,'~',1,10)-instr(x,'~',1,9)-1),1,40) adrinfo9
  from (select AddressID,Address_Qualifier, OwnerID,
               '~'
             ||decode(name1,null,null,name1||'~')
             ||decode(name2,null,null,name2||'~')
             ||decode(name3,null,null,name3||'~')
             ||decode(name4,null,null,name4||'~')
             ||decode(name5,null,null,name5||'~')
             ||decode(adr1,null,null,adr1||'~')
             ||decode(adr2,null,null,adr2||'~')
             ||decode(adr3,null,null,adr3||'~')
             ||decode(adr4,null,null,adr4||'~')
             ||decode(name1,null,'~')
             ||decode(name2,null,'~')
             ||decode(name3,null,'~')
             ||decode(name4,null,'~')
             ||decode(name5,null,'~')
             ||decode(adr1,null,'~')
             ||decode(adr2,null,'~')
             ||decode(adr3,null,'~')
             ||decode(adr4,null,'~')
               x
         from address) y
/

View created.

desc addrs

 Name              Null?    Type
 ----------------- -------- ------------
 ADDRESSID                  NUMBER
 ADDRESS_QUALIFIER          VARCHAR2(5)
 OWNERID                    NUMBER
 ADRINFO1                   VARCHAR2(40)
 ADRINFO2                   VARCHAR2(40)
 ADRINFO3                   VARCHAR2(40)
 ADRINFO4                   VARCHAR2(40)
 ADRINFO5                   VARCHAR2(40)
 ADRINFO6                   VARCHAR2(40)
 ADRINFO7                   VARCHAR2(40)
 ADRINFO8                   VARCHAR2(40)
 ADRINFO9                   VARCHAR2(40)

select * from addrs;

ADDRESS ADDRESS_
     ID QUALIFIER OWNERID ADRINFO1    ADRINFO2    ADRINFO3    ADRINFO4    ADRINFO5                 
------- --------- ------- ----------- ----------- ----------- ----------- -------------------------
      1 ABC           101 John        Big Company Main Street Box 55
      2 ABC           201 Oprah       Chicago, IL
      3 ABC           301 George      Walker      Bush        President   Of The United States     

ADRINFO6    ADRINFO7    ADRINFO8                  ADRINFO9
----------- ----------- ------------------------- -------------------------


Oval Office White House 1600 Pennsylvania Ave.    Washington, D.C., 20500

3 rows selected.

...I don't understand how it works...

I'll give a step-by-step explanation of the "CREATE OR REPLACE VIEW..." code, in order of execution:

(Beginning with the innermost SELECT...)
1) "[b]...from address[/b]" -- Read each row from the ADDRESS table.
2) "[b]SELECT AddressID,Address_Qualifier, OwnerID,[/b]" -- Display the contents of each listed expression.
   "[b]...'~'||decode(name1,null,null,name1||'~')...[/b]" -- concatenate into a single string, a tilde ('~'), plus[ul][li]a NULL if "NAME1" is NULL,[/li][li]else the contents of "NAME1" plus a tilde if "NAME1" is not NULL.[/li][/ul]Repeat the same concatenation logic also for columns "NAME2" through "NAME5", and columns "ADR1" through "ADR4".


         "[b]...||decode(name1,null,'~')
             ||decode(name2,null,'~')
             ||decode(name3,null,'~')
             ||decode(name4,null,'~')
             ||decode(name5,null,'~')
             ||decode(adr1,null,'~')
             ||decode(adr2,null,'~')
             ||decode(adr3,null,'~')
             ||decode(adr4,null,'~')...[/b]" -- To the end of the string thus far, concatenate a tilde ('~') for each of the above columns that were NULL (for which nothing was concatenated due to the columns NULLness).

    The result, so far, of this first, inner SELECT appears like this:

ID QUALIFIER OWNERID X
-- --------- ------- ---------------------------------------------------------------
 1 ABC           101 ~John~Big Company~Main Street~Box 55~~~~~~
 2 ABC           201 ~Oprah~Chicago, IL~~~~~~~~
 3 ABC           301 ~George~Walker~Bush~President~Of The United States~Oval Office~...

3) The results, thus far, of this inner SELECT are an "in-line" VIEW of the above results. The in-line VIEW, then, by virtue of the "FROM (SELECT..." clause, becomes the input that the outer SELECT reads for its query, as though it were a real TABLE.

4) "[b]select AddressID,Address_Qualifier, OwnerID...[/b]" -- Display the contents of each listed expression that derives from the in-line VIEW SELECTs.

Then, for VIEW columns, "adrinfo1" through "adrinfo9", produce values from the following code model:

"[b],substr(substr(x,instr(x,'~',1,<col-n>)+1,instr(x,'~',1,<col-n+1>)-instr(x,'~',1,<col-n>)-1),1,40) adrinfo<col-n>[/b]"

With this code model, here is the step-by-step interpretation, in order of execution:

   a) [b]...instr(x,'~',1,<col-n>)...[/b] -- in the string "x", beginning at character 1, identify the character position of the "n-th" occurrence of '~'.
   b) [b]...+1...[/b] -- add "1" to the character position identified by the previous function. The result of this operation identifies the starting position for SUBSTRinging the characters that will occupy "adrinfo<col-n>".
   c) Next, to identify the number of characters to SUBSTRing, [b]...instr(x,'~',1,<col-n+1>)-instr(x,'~',1,<col-n>)-1...[/b] -- Locate the position of the "nth+1" tilde, then subtract the position of the "nth" tilde, and subtract "1" more character, thus producing the length to SUBSTRing the "nth" address expression.

      The inner SUBSTR function then results in a 379-character-maximum string of just the "nth" adrinfo extraction.
   d) Lastly, to reduce the data-dictionary column-length-maximum for "adrinfo<col-n>" from "379" to "40", "[b]...substr(<379-char expression>,1,40)...[/b]".
   e) "[b]...CREATE OR REPLACE VIEW ADDRS AS...[/b]" -- Assign the above query, and its accompanying results, to a data-dictionary object named, "ADDRS".

Let me know if the above explanation helps you to better understand the "CREATE OR REPLACE VIEW..." code, above.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[KenCunningham]

Wow, Goran, Santa's really gone the extra mile for you above - perhaps he deserves some kind of recognition in the form of 'a little purple star'?

I want to be good, is that not enough?

 

[goranm]

Hi, and thank you very much.

Your solution worked great, and I think I followed your explanation how it works.

I really appreciate all your help.

Many thanks.

/Goran

 

[SantaMufasa]

Thanks to both Ken and to Goran. I'm glad, Goran that the code worked for you and that you follow the logic/explanation.

Good luck with your implementation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”