Home » SQL & PL/SQL » SQL & PL/SQL » Query Problem (merged)
Query Problem (merged) [message #573073] Thu, 20 December 2012 09:18 Go to next message
jimmym
Messages: 7
Registered: December 2012
Location: GA
Junior Member
I want to expect the output records(updated range_code column) in zip_dir_t table based on lookup table(zip_lookup).
But there are multiple records comes up based on my query. Would you help me or give me advice.

ZIP_DIR_T
Start_State_cd    start_zip_cd       end_state_cd        end_zip_cd             range_code
CO                       80212        CA                       98123
CO                       80212        NULL                   NULL
CO                       NULL         NULL                  NULL
NULL                    NULL         MI                      34122

ZIP_LOOKUP
Start_state_cd   start_from_zip      start_to_zip   end_state_cd  end_from_zip   end_to_zip  lookrange_cd
CO                                                                                                  001
CO                      80000               80400       CA       98100                  98200       002
CO                     80000                 80400                                                  003
CO                                                      CA                                          004
                                                        MI      34100             34200             005

EXPECTED OUTPUT
Start_State_cd    start_zip_cd       end_state_cd               end_zip_cd            range_code
CO                         80212                  CA                       98123                  002
CO                          80212                NULL                   NULL                     003
CO                         NULL                    NULL                  NULL                     001
NULL                    NULL                      MI                      34122                   005



MY QUERY

WITH zip_dir_t AS (
SELECT 'co' start_state_cd, 80212 start_zip_cd, 'ca' end_state_cd, 98123 end_zip_cd, NULL range_coed FROM dual UNION ALL
SELECT 'co', 80212, NULL,NULL,NULL FROM dual UNION ALL
SELECT 'co', NULL,NULL,NULL,NULL  FROM dual UNION ALL
SELECT NULL, NULL, 'mi',34122 ,NULL FROM dual )
   ,  
  zip_lookup AS (
SELECT 'co' start_state_cd, NULL start_from_zip, NULL start_to_zip, NULL end_state_cd, NULL end_from_zip, NULL end_to_zip,001 lookrange_cd FROM dual UNION ALL 
SELECT 'co',80000,80400,'CA',98100,98200,002 FROM dual UNION ALL
SELECT 'co',80000,80400,NULL,NULL,NULL,003 FROM dual UNION ALL
SELECT 'co',NULL,NULL,'CA',NULL,NULL,004 FROM dual UNION ALL
SELECT NULL,NULL,NULL,'MI',34100,34200,005 FROM dual  )     
     SELECT A.start_state_cd,A.start_zip_cd ,A.end_state_cd,A.end_zip_cd, b.lookrange_cd FROM zip_dir_t A ,zip_lookup b
     WHERE (A.start_state_cd = b.start_state_cd
        and A.start_zip_cd BETWEEN b.start_from_zip AND b.start_to_zip)
       OR    (a.end_state_cd = b.end_state_cd
        and A.end_zip_cd   BETWEEN b.end_from_zip   AND b.end_to_zip)

Re: Query-need help [message #573079 is a reply to message #573073] Thu, 20 December 2012 10:32 Go to previous messageGo to next message
flyboy
Messages: 1779
Registered: November 2006
Senior Member
Thank you for providing sample SQL. However I wonder what are rules for obtaining the expected result set as they are not present in your post. If it is simple join on either contained values or NULL on (START_STATE_CD, END_STATE_CD, START_ZIP_CD, END_ZIP_CD) columns in both tables with including A.*_ZIP_CD columns to range even when B.*_TO_ZIP is null, it would look like:
SELECT A.start_state_cd,A.start_zip_cd ,A.end_state_cd,A.end_zip_cd, b.lookrange_cd
FROM zip_dir_t A ,zip_lookup b
WHERE ( A.start_state_cd = b.start_state_cd
        or ( A.start_state_cd is null and b.start_state_cd is null ) )
  and ( ( A.start_zip_cd >= b.start_from_zip AND ( A.start_zip_cd <= b.start_to_zip or b.start_to_zip is null ) )
        or ( A.start_zip_cd is null and b.start_from_zip is null ) )
  and ( upper(A.end_state_cd) = upper(b.end_state_cd)
        or ( A.end_state_cd is null and b.end_state_cd is null ) )
  and ( ( A.end_zip_cd >= b.end_from_zip AND ( A.end_zip_cd <= b.end_to_zip or b.end_to_zip is null ) )
        or ( A.end_zip_cd is null and b.end_from_zip is null ) )

ST START_ZIP_CD EN END_ZIP_CD LOOKRANGE_CD
-- ------------ -- ---------- ------------
co                                       1
                mi      34122            5
co        80212 ca      98123            2
co        80212                          3

Note the use of UPPER function as your END_STATE_CD column values do not match case.
icon14.gif  Re: Query-need help [message #573110 is a reply to message #573079] Thu, 20 December 2012 23:47 Go to previous messageGo to next message
jimmym
Messages: 7
Registered: December 2012
Location: GA
Junior Member
Thanks for your help. You teach me a lot.
if I need your help one more time, I will let you know.

Thanks for sharing your knowledge
Query Problem [message #574934 is a reply to message #573073] Wed, 16 January 2013 22:37 Go to previous messageGo to next message
jimmym
Messages: 7
Registered: December 2012
Location: GA
Junior Member
Still need help to figure out this query. (Flyboy helped me this query).
how can i get 5 records including not match record(last one). I got only 4 records.
I tried left out join. it doesn't work. would you help me how to get the output?

ZIP_DIR_T
Start_State_cd    start_zip_cd       end_state_cd        end_zip_cd             range_code
CO                       80212        CA                       98123
CO                       80212        NULL                   NULL
CO                       NULL         NULL                  NULL
NULL                    NULL         MI                      34122
NULL                     NULL         NULL                   35120

ZIP_LOOKUP
Start_state_cd   start_from_zip      start_to_zip   end_state_cd  end_from_zip   end_to_zip  lookrange_cd
CO                                                                                                  001
CO                      80000               80400       CA       98100                  98200       002
CO                     80000                 80400                                                  003
CO                                                      CA                                          004
                                                        MI      34100             34200             005

EXPECTED OUTPUT
Start_State_cd    start_zip_cd       end_state_cd               end_zip_cd            range_code
CO                         80212                  CA                       98123                  002
CO                          80212                NULL                   NULL                     003
CO                         NULL                    NULL                  NULL                     001
NULL                    NULL                      MI                      34122                   005
NULL                      NULL                     NULL                    35120


My Query
WITH zip_dir_t AS (
SELECT 'co' start_state_cd, 80212 start_zip_cd, 'ca' end_state_cd, 98123 end_zip_cd, NULL range_coed FROM dual UNION ALL
SELECT 'co', 80212, NULL,NULL,NULL FROM dual UNION ALL
select 'co', null,null,null,null  from dual union all
SELECT null, NULL,NULL,35120,NULL  FROM dual UNION ALL
SELECT NULL, NULL, 'mi',34122 ,NULL FROM dual )
   ,  
  zip_lookup AS (
SELECT 'co' start_state_cd, NULL start_from_zip, NULL start_to_zip, NULL end_state_cd, NULL end_from_zip, NULL end_to_zip,001 lookrange_cd FROM dual UNION ALL 
SELECT 'co',80000,80400,'CA',98100,98200,002 FROM dual UNION ALL
SELECT 'co',80000,80400,NULL,NULL,NULL,003 FROM dual UNION ALL
select 'co',null,null,'CA',null,null,004 from dual union all
select null,null,null,'MI',34100,34200,005 from dual  )     
SELECT A.start_state_cd,A.start_zip_cd ,A.end_state_cd,A.end_zip_cd, b.lookrange_cd
from zip_dir_t a ,zip_lookup b
where ( a.start_state_cd = b.start_state_cd
       or ( a.start_state_cd is null and b.start_state_cd is null )  )
       --a.start_state_cd = b.start_state_cd(+)
  and ( ( A.start_zip_cd >= b.start_from_zip AND ( A.start_zip_cd <= b.start_to_zip or b.start_to_zip is null ) )
        or ( a.start_zip_cd is null and b.start_from_zip is null ) )
  and ( upper(a.end_state_cd) = upper(b.end_state_cd)
       or ( a.end_state_cd is null and b.end_state_cd is null )   )
     --  upper(a.end_state_cd) = upper(b.end_state_cd(+))
  and ( ( a.end_zip_cd >= b.end_from_zip and ( a.end_zip_cd <= b.end_to_zip or b.end_to_zip is null ) )
        or ( A.end_zip_cd is null and b.end_from_zip is null ) )
Re: Query Problem [message #574935 is a reply to message #574934] Wed, 16 January 2013 22:44 Go to previous messageGo to next message
jimmym
Messages: 7
Registered: December 2012
Location: GA
Junior Member
Addition Explain;
The expected output records is based on zip_dir_t table. Bylookup table(zip_lookup)updating range_code
Re: Query Problem [message #574949 is a reply to message #574935] Thu, 17 January 2013 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59757
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also you have not to create a new topic for a follow-up of an existing one.
The 2 topics are merged.

And find a MEANINGFUL title.

Regards
Michel
Re: Query Problem [message #574963 is a reply to message #574934] Thu, 17 January 2013 03:54 Go to previous messageGo to next message
flyboy
Messages: 1779
Registered: November 2006
Senior Member
jimmym wrote on Thu, 17 January 2013 05:37
I tried left out join. it doesn't work. would you help me how to get the output?

Instead of trying, why do you not simply use it? Instead of adding new join conditions, modify the current ones - add Oracle outer join operator (+) after each occurrence of B column.
It is clearly stated in chapter of SQL Language Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
You may benefit from consulting it.

If you are on Oracle 9i or greater, you may save this typing and use ANSI join syntax instead:
FROM zip_dir_t A left join zip_lookup b on <all conditions>


[Edit: plural]

[Updated on: Thu, 17 January 2013 03:54]

Report message to a moderator

Re: Query Problem [message #574995 is a reply to message #574949] Thu, 17 January 2013 10:00 Go to previous messageGo to next message
jimmym
Messages: 7
Registered: December 2012
Location: GA
Junior Member
Here is table DDL and insert statements
CREATE TABLE ZIP_DIR_T
  (
    "START_STATE_CD" VARCHAR2(2 BYTE),
    "START_ZIP_CD"   NUMBER(5,0),
    "END_STATE_CD"   VARCHAR2(2 BYTE),
    "END_ZIP_CD"     NUMBER(5,0),
    "RANGE_CODE"     NUMBER(3,0)
  )


CREATE TABLE ZIP_LOOKUP
  (
    "START_STATE_CD" VARCHAR2(2 BYTE),
    "START_FROM_ZIP" VARCHAR2(5 BYTE),
    "START_TO_ZIP"   VARCHAR2(5 BYTE),
    "END_STATE_CD"   VARCHAR2(2 BYTE),
    "END_FROM_ZIP"   VARCHAR2(5 BYTE),
    "END_TO_ZIP"     VARCHAR2(5 BYTE),
    "LOOKRANGE_CD"   VARCHAR2(3 BYTE)
  )


Insert into ZIP_LOOKUP (START_STATE_CD,START_FROM_ZIP,START_TO_ZIP,END_STATE_CD,END_FROM_ZIP,END_TO_ZIP,LOOKRANGE_CD) values ('CO','80000','80400','CA','98100','98200','002');
Insert into ZIP_LOOKUP (START_STATE_CD,START_FROM_ZIP,START_TO_ZIP,END_STATE_CD,END_FROM_ZIP,END_TO_ZIP,LOOKRANGE_CD) values ('CO','80000','80400',null,null,null,'003');
Insert into ZIP_LOOKUP (START_STATE_CD,START_FROM_ZIP,START_TO_ZIP,END_STATE_CD,END_FROM_ZIP,END_TO_ZIP,LOOKRANGE_CD) values ('CO',null,null,'CA',null,null,'004');
Insert into ZIP_LOOKUP (START_STATE_CD,START_FROM_ZIP,START_TO_ZIP,END_STATE_CD,END_FROM_ZIP,END_TO_ZIP,LOOKRANGE_CD) values (null,null,null,'MI','34100','34200','005');


Insert into ZIP_DIR_T (START_STATE_CD,START_ZIP_CD,END_STATE_CD,END_ZIP_CD,RANGE_CODE) values ('CO',80212,'CA',98123,null);
Insert into ZIP_DIR_T (START_STATE_CD,START_ZIP_CD,END_STATE_CD,END_ZIP_CD,RANGE_CODE) values ('CO',80212,null,null,null);
Insert into ZIP_DIR_T (START_STATE_CD,START_ZIP_CD,END_STATE_CD,END_ZIP_CD,RANGE_CODE) values ('CO',null,null,null,null);
Insert into ZIP_DIR_T (START_STATE_CD,START_ZIP_CD,END_STATE_CD,END_ZIP_CD,RANGE_CODE) values (null,null,null,35120,null);
Insert into ZIP_DIR_T (START_STATE_CD,START_ZIP_CD,END_STATE_CD,END_ZIP_CD,RANGE_CODE) values (null,null,'MI',34122,null);


to get all records with RANGE_CODE info in zip_dir_t, I have to left outer join.
I did as follows; the output has only two records. I don't know how to handle a.start_state_cd has null b.start_state_cd has null a.end_state_cd has null b.end_state_cd has null in the tables..
SELECT A.start_state_cd,A.start_zip_cd ,A.end_state_cd,A.end_zip_cd, b.lookrange_cd
from zip_dir_t a ,zip_lookup b
where 
   ( a.start_state_cd = b.start_state_cd(+) ) 
  and ( ( A.start_zip_cd >= b.start_from_zip AND ( A.start_zip_cd <= b.start_to_zip or b.start_to_zip is null ) )
        or ( a.start_zip_cd is null and b.start_from_zip is null ) )
 and (a.end_state_cd = b.end_state_cd(+))
    
  and ( ( a.end_zip_cd >= b.end_from_zip and ( a.end_zip_cd <= b.end_to_zip or b.end_to_zip is null ) )
        or ( a.end_zip_cd is null and b.end_from_zip is null ) )


Here is my expect output
EXPECTED OUTPUT
Start_State_cd    start_zip_cd       end_state_cd               end_zip_cd            range_code
CO                         80212                  CA                       98123                  002
CO                          80212                NULL                   NULL                     003
CO                         NULL                    NULL                  NULL                     001
NULL                    NULL                      MI                      34122                   005
NULL                      NULL                     NULL                    35120
Re: Query Problem [message #575019 is a reply to message #574995] Thu, 17 January 2013 22:56 Go to previous messageGo to next message
jimmym
Messages: 7
Registered: December 2012
Location: GA
Junior Member
would you give me advice. for example, in this case, i can not use left outer join or use union all ......
I don't know how can i get the output?
Re: Query Problem [message #575032 is a reply to message #575019] Fri, 18 January 2013 01:36 Go to previous messageGo to next message
flyboy
Messages: 1779
Registered: November 2006
Senior Member
jimmym wrote on Fri, 18 January 2013 05:56
for example, in this case, i can not use left outer join or use union all ......

What can you use then? OUTER join is surely the way how to get the required result.
jimmym wrote on Fri, 18 January 2013 05:56
I don't know how can i get the output?

What about Quote:
Instead of adding new join conditions, modify the current ones - add Oracle outer join operator (+) after each occurrence of B column.

? You were able to add new conditions into WHERE clause, so you should be able to modify all of them.

Did you try to read the documentation (as you did not post your Oracle version, I did not post the direct list to Outer join chapter; you should be able to find it yourself anyway)?
Re: Query Problem [message #575064 is a reply to message #575032] Fri, 18 January 2013 13:54 Go to previous message
jimmym
Messages: 7
Registered: December 2012
Location: GA
Junior Member
Thanks for your advice.
I may figure out my problem.
Thanks
Previous Topic: Employee summary
Next Topic: delete table if exist
Goto Forum:
  


Current Time: Mon Nov 24 10:52:23 CST 2014

Total time taken to generate the page: 0.12506 seconds