OraFAQ Forum: SQL & PL/SQL » complex view query with multiple lookups

Home » SQL & PL/SQL » SQL & PL/SQL » complex view query with multiple lookups

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

complex view query with multiple lookups [message #636342]

Wed, 22 April 2015 04:23

natrajdreams
Messages: 10
Registered: January 2009

Junior Member

I have four tables named HOLDER,ADDRESS,CLNT,DETAIL.

ADDRESS table having columns ST_ADDR1,CITY,STATE,ZIPCODE.

we need to write a view on ADDRESS table for the above columns according the logic given by below for each column.

ST_ADDR1

-----------------
Join HOLDER.NUM = ADDRESS.KEY_ID
AND ADDRESS.ADDR_TYPE = 'LA' then
take HOLDER.L_ID
IF HOLDER.L_ID > 0 then look up on DETAIL
CLNT.L_ID = DETAIL.L_CD
AND CLNT.I_CD = DETAIL.I_CD
AND DETAIL.DT <= SYSDATE
AND (DETAIL.EXP_DT is null or DETAIL.EXP_DT >= SYSDATE) then
take DETAIL.AD_DIS

CITY

------------
Join HOLDER.NUM = ADDRESS.KEY_ID
AND ADDRESS.ADDR_TYPE = 'LA' then
take HOLDER.L_ID
IF HOLDER.L_ID > 0 then look up on DETAIL
CLNT.L_ID = DETAIL.L_CD
AND CLNT.I_CD = DETAIL.I_CD
AND DETAIL.DT <= SYSDATE
AND (DETAIL.EXP_DT is null or DETAIL.EXP_DT >= SYSDATE) then
take DETAIL.CITY_D

STATE

------------
Join HOLDER.NUM = ADDRESS.KEY_ID
AND ADDRESS.ADDR_TYPE = 'LA' then
take HOLDER.L_ID
IF HOLDER.L_ID > 0 then look up on DETAIL
CLNT.L_ID = DETAIL.L_CD
AND CLNT.I_CD = DETAIL.I_CD
AND DETAIL.DT <= SYSDATE
AND (DETAIL.EXP_DT is null or DETAIL.EXP_DT >= SYSDATE) then
take DETAIL.STATE_CD

ZIPCODE

--------------
Join HOLDER.NUM = ADDRESS.KEY_ID
AND ADDRESS.ADDR_TYPE = 'LA' then
take HOLDER.L_ID
IF HOLDER.L_ID > 0 then look up on DETAIL
CLNT.L_ID = DETAIL.L_CD
AND CLNT.I_CD = DETAIL.I_CD
AND DETAIL.EFF_DT <= SYSDATE
AND (DETAIL.EXP_DT is null or DETAIL.EXP_DT >= SYSDATE) then
take DETAIL.ZIP_CD

Kindly help me to write a view by using the above logic mentioned.

Report message to a moderator

Re: complex view query with multiple lookups [message #636346 is a reply to message #636342]

Wed, 22 April 2015 04:55

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

What have you managed to write so far? And where are you stuck now?

Report message to a moderator

Re: complex view query with multiple lookups [message #636349 is a reply to message #636342]

Wed, 22 April 2015 05:00

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why should we answer to your question when you don't answer to ours and feedback in any way?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: complex view query with multiple lookups [message #636350 is a reply to message #636346]

Wed, 22 April 2015 05:01

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You need to post the structure of each table and explain the relationship between them. If address has a zipcode column why are writing complex joins to get zipcode from another table?
And you need to follow the instructions here:
How to use [code] tags and make your code easier to read?

Report message to a moderator

Re: complex view query with multiple lookups [message #636352 is a reply to message #636349]

Wed, 22 April 2015 05:05

natrajdreams
Messages: 10
Registered: January 2009

Junior Member

Hi Expert,

Cool... I will give feedback to all your questions. Please help me in writing the query

Report message to a moderator

Re: complex view query with multiple lookups [message #636353 is a reply to message #636346]

Wed, 22 April 2015 05:07

natrajdreams
Messages: 10
Registered: January 2009

Junior Member

Hi Lalit, I dont have any idea to write the view with lookups, thats y seeking help. Kindly help me in writing the query

Report message to a moderator

Re: complex view query with multiple lookups [message #636354 is a reply to message #636352]

Wed, 22 April 2015 05:14

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

natrajdreams wrote on Wed, 22 April 2015 12:05

Hi Expert,

Cool... I will give feedback to all your questions. Please help me in writing the query

So provide what cookiemonster asked.

Report message to a moderator

Re: complex view query with multiple lookups [message #636356 is a reply to message #636350]

Wed, 22 April 2015 05:22

natrajdreams
Messages: 10
Registered: January 2009

Junior Member

Hi Lalit , ADDR_TYPE is the Primary key in ADDRESS table and there are no other keys available.

Report message to a moderator

Re: complex view query with multiple lookups [message #636357 is a reply to message #636350]

Wed, 22 April 2015 05:31

natrajdreams
Messages: 10
Registered: January 2009

Junior Member

Hi Lalit, No need to bother about the keys, There are already conditions available, I need to write a view satisfying the given conditions.

thank you

Report message to a moderator

Re: complex view query with multiple lookups [message #636362 is a reply to message #636357]

Wed, 22 April 2015 05:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 22 April 2015 12:14

natrajdreams wrote on Wed, 22 April 2015 12:05
Hi Expert,

Cool... I will give feedback to all your questions. Please help me in writing the query

So provide what cookiemonster asked.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.

Report message to a moderator

Re: complex view query with multiple lookups [message #636374 is a reply to message #636342]

Wed, 22 April 2015 06:51

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

28 Jan 2009 - http://www.orafaq.com/forum/m/383254/#msg_383254 - abandoned thread after being asked for more information
28 Jan 2009 - http://www.orafaq.com/forum/m/383258/#msg_383258 - abandoned thread after being asked for more information
28 Jan 2009 - http://www.orafaq.com/forum/m/383264/#msg_383264 - abandoned thread after being asked for more information

Report message to a moderator

Re: complex view query with multiple lookups [message #636377 is a reply to message #636350]

Wed, 22 April 2015 08:51

natrajdreams
Messages: 10
Registered: January 2009

Junior Member

Hi Lalit,

I have tried the below query.. Please correct me.

CREATE VIEW P_VIEW
AS
SELECT CASE WHEN holder.l_id > 0 
            THEN detail.ad_dis 
            ELSE address.st_addr1 END AS addr1
     , CASE WHEN holder.l_id > 0 
            THEN detail.city_d  
            ELSE address.city END AS city
     , CASE WHEN holder.l_id > 0 
            THEN detail.state_cd 
            ELSE address.state END AS state 
     , CASE WHEN holder.l_id > 0 
            THEN detail.zip_cd 
            ELSE address.zipcode END AS zipcode 
  FROM holder
LEFT OUTER
  JOIN address
    ON address.key_id = holder.num
   AND address.addr_type='LA'
LEFT OUTER
  JOIN clnt
    ON clnt.l_id = holder.l_id    
LEFT OUTER
  JOIN detail
    ON detail.l_cd = clnt.l_cd
   AND detail.i_cd = clnt.i_cd
   AND DETAIL.DT < sysdate
   AND ( 
       detail.exp_dt IS NULL 
    OR detail.exp_dt >= SYSDATE
       )

*BlackSwan added {code} tags. Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/

[Updated on: Wed, 22 April 2015 09:02] by Moderator

Report message to a moderator

Re: complex view query with multiple lookups [message #636378 is a reply to message #636377]

Wed, 22 April 2015 09:00

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

cookiemonster wrote on Wed, 22 April 2015 15:31

And you need to follow the instructions here:
How to use [code] tags and make your code easier to read?

Michel Cadot wrote on Wed, 22 April 2015 15:30

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

You posted code is unreadable. Help us to get help.

Edit : Oh, by the way, it were CM and MC who have had pushed you to post the required details. I just asked you to show your attempt and where you are exactly stuck.

[Updated on: Wed, 22 April 2015 09:02]

Report message to a moderator

Re: complex view query with multiple lookups [message #636381 is a reply to message #636377]

Wed, 22 April 2015 09:04

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

natrajdreams wrote on Wed, 22 April 2015 15:51

I have tried the below query.. Please correct me.

It does not work:

SQL> CREATE VIEW P_VIEW
  2  AS
  3  SELECT CASE WHEN holder.l_id > 0
  4              THEN detail.ad_dis
  5              ELSE address.st_addr1 END AS addr1
  6       , CASE WHEN holder.l_id > 0
  7              THEN detail.city_d
  8              ELSE address.city END AS city
  9       , CASE WHEN holder.l_id > 0
 10              THEN detail.state_cd
 11              ELSE address.state END AS state
 12       , CASE WHEN holder.l_id > 0
 13              THEN detail.zip_cd
 14              ELSE address.zipcode END AS zipcode
 15    FROM holder
 16  LEFT OUTER
 17    JOIN address
 18      ON address.key_id = holder.num
 19     AND address.addr_type='LA'
 20  LEFT OUTER
 21    JOIN clnt
 22      ON clnt.l_id = holder.l_id
 23  LEFT OUTER
 24    JOIN detail
 25      ON detail.l_cd = clnt.l_cd
 26     AND detail.i_cd = clnt.i_cd
 27     AND DETAIL.DT < sysdate
 28     AND (
 29         detail.exp_dt IS NULL
 30      OR detail.exp_dt >= SYSDATE
 31         )
 32  /
  JOIN detail
       *
ERROR at line 24:
ORA-00942: table or view does not exist

Report message to a moderator

Re: complex view query with multiple lookups [message #636387 is a reply to message #636377]

Wed, 22 April 2015 09:17

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

natrajdreams wrote on Wed, 22 April 2015 14:51

Hi Lalit,

I have tried the below query.. Please correct me.

Lets be clear here - we know nothing about your system other than what you've told us.
We don't know the structure of the tables.
We don't know the relationship between the tables.
We don't know what data you have.
We don't know if your view gives an error or the wrong result.
We don't know what the correct result would be.

You need to supply a lot more details for anyone to be able to help you.

Report message to a moderator

Previous Topic:	SQL query for count
Next Topic:	inner query use outer query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:22:26 CDT 2024