Home » SQL & PL/SQL » SQL & PL/SQL » select query
select query [message #295001] Mon, 21 January 2008 02:27 Go to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,

I have 2 tables with following scripts :

CREATE TABLE PRODUCT_EG
(
PRODUCT_ID VARCHAR2(10 BYTE),
RATE NUMBER(10,2)
)

CREATE TABLE PROMOTION_EG
(
PROMOTION_ID VARCHAR2(10 BYTE),
PRODUCT_ID VARCHAR2(10 BYTE),
SPEC_RATE NUMBER(10,3),
START_DATE DATE,
END_DATE DATE
)

My requirement is that to check whether product_id in PRODUCT_EG
exist in PROMOTION_EG
If it exist then return spec_rate else rate itself

Please help me out in writing the query
Re: select query [message #295005 is a reply to message #295001] Mon, 21 January 2008 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ How is this an expert question?
2/ please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
3/ Always post your Oracle version (4 decimals)
4/ Post what you already tried.

Regards
Michel
Re: select query [message #295017 is a reply to message #295001] Mon, 21 January 2008 02:49 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
select ptroduct,rate from 
(select product_id as product,rate from 
product_eg where product_id not in 
(select product_id from promotion_eg)
union
select a.product_id as product,b.spec_rate as rate from 
product_eg a,promotion_eg b
where a.product_id=b.product_id);
Re: select query [message #295026 is a reply to message #295001] Mon, 21 January 2008 03:03 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

This is also one type of solution which u ask
select a.product_id,
case nvl(spec_rate,0) 
when 0 then rate 
else spec_rate end rate1 
from product_eg a,promotion_eg b
where a.product_id = b.product_id(+);

by
Kanish

[Edit: Keep your lines in 80 characters]

[Updated on: Mon, 21 January 2008 03:05] by Moderator

Report message to a moderator

Re: select query [message #295035 is a reply to message #295001] Mon, 21 January 2008 03:15 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
select a.product_id,
case nvl(spec_rate,0)
when 0 then rate
else spec_rate end rate1
from product_eg a,promotion_eg b
where a.product_id = b.product_id(+);


Here


case nvl(spec_rate,0) 
when 0 then rate 
else spec_rate end rate1 


could be replaced with

nvl(spec_rate , rate) 


Moreover

case nvl(spec_rate,0) 
when 0 then rate 
else spec_rate end rate1


will give wrong result in some scenarios like
Quote:
rate = 10 and spec_rate = 0



Thumbs Up
Rajuvan

[Updated on: Mon, 21 January 2008 03:21]

Report message to a moderator

Re: select query [message #295044 is a reply to message #295001] Mon, 21 January 2008 03:32 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi

Thanks

i wrote the query like this

select a.product_id,nvl(spec_rate,rate) from product_eg a,promotion_eg b WHERE a.product_id = b.product_id(+)
and i got the result

one ques

Cud u pls explain me this query
bcoz i was also try to use nvl and all but i was getting no data found
how the nvl is working here with respect to outer join
Re: select query [message #295046 is a reply to message #295001] Mon, 21 January 2008 03:38 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

This is outjoin two tables

NVl give when null meets for this condition which provide colomn value from other table rate.


Kanish
Re: select query [message #295052 is a reply to message #295001] Mon, 21 January 2008 03:55 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Outer Join


NVL

regards,
Rajuvan
Re: select query [message #295272 is a reply to message #295001] Mon, 21 January 2008 23:34 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi

One more modification required

That is

Now the query shud return an o/p like the product_id entered shud also luk whether sysdate is in between start date and end date thn only promotion allowed for this item and its special rate shud be returned else rate itself to be returned
Re: select query [message #295274 is a reply to message #295001] Mon, 21 January 2008 23:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

It is based on your business rule. I think it is simple for you to build the logic and make the modification on the current query . Am i right ?

Thumbs Up
Rajuvan
Re: select query [message #295351 is a reply to message #295001] Tue, 22 January 2008 03:20 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,


I have tried as follows but its wrong

select a.product_id,nvl(b.spec_rate,rate) from product_eg a,promotion_eg b where sysdate in(Sel
sysdate between start_date and end_date) and a.product_id = b.product_id(+)

I need all the product_ids from product_eg and if the date range not satisfied then it shud return rate in product_eg
not that of promotion_eg
Re: select query [message #295354 is a reply to message #295001] Tue, 22 January 2008 03:24 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

And ur Code also wrong

select a.product_id,nvl(b.spec_rate,rate) from product_eg a,promotion_eg b where sysdate in(Sel
sysdate between start_date and end_date) and a.product_id = b.product_id(+)


wbr
kanish
Re: select query [message #295355 is a reply to message #295001] Tue, 22 January 2008 03:28 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Sorry, here is the formatted SQL:

SELECT a.product_id, NVL (b.spec_rate, rate)
  FROM product_eg a, promotion_eg b
 WHERE SYSDATE IN (SELECT SYSDATE
                     FROM DUAL
                    WHERE SYSDATE BETWEEN start_date AND end_date)
   AND a.product_id = b.product_id(+)
Re: select query [message #295357 is a reply to message #295001] Tue, 22 January 2008 03:32 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

Did u understand the query

   SELECT SYSDATE
FROM DUAL
WHERE SYSDATE BETWEEN [B]start_date[/B] AND [B]end_date[/B]


start_date and end_date is it belong to dual table.

Tel me What is ur actual requirements.

wbr

Kanish
Re: select query [message #295361 is a reply to message #295001] Tue, 22 January 2008 03:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Hint :

You need to Outerjoin the Start and Endates with sysdate with AND Clause . No need for Seperate subquery .

SELECT  ....
FROM    ....
WHERE   ....
AND     ....


Thumbs Up
Rajuvan
Re: select query [message #295363 is a reply to message #295001] Tue, 22 January 2008 03:41 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,

Finally i got it dears
Here goes the query :

SELECT product_eg.product_id, NVL (promotion_eg.spec_rate, rate)
FROM product_eg,
(SELECT promotion_id, product_id, spec_rate, start_date, end_date,
SYSDATE
FROM promotion_eg
WHERE SYSDATE BETWEEN start_date AND end_date) promotion_eg
WHERE product_eg.product_id = promotion_eg.product_id(+)

Hope yougot me
Re: select query [message #295368 is a reply to message #295001] Tue, 22 January 2008 03:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Good Try .

And Another way would be ,

SELECT A.PRODUCT_ID,
	   NVL(SPEC_RATE , RATE)  RATE1 
FROM   PRODUCT_EG A,PROMOTION_EG B
WHERE  A.PRODUCT_ID = B.PRODUCT_ID(+)
AND    SYSDATE BETWEEN B.START_DATE(+)  AND B.END_DATE(+)


Thumbs Up
Rajuvan.
Re: select query [message #295369 is a reply to message #295001] Tue, 22 January 2008 03:48 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

why dont youtry like this


SELECT product_eg.product_id, NVL (promotion_eg.spec_rate, rate)
FROM product_eg a,promotion_eg b
 WHERE SYSDATE BETWEEN start_date AND end_date a.product_id = b.product_id(+)




wbr
kanish
Re: select query [message #295373 is a reply to message #295001] Tue, 22 January 2008 03:55 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Kanish ,

Your query may be proved wrong as per the expectation from OP.

SQL> select * From PRODUCT_EG order by 1;

PRODUCT_ID       RATE
---------- ----------
A                  10
B                  20
C                  30

SQL> select * From PROMOTION_EG order by 2,1 ;

PROMOTION_ PRODUCT_ID  SPEC_RATE START_DAT END_DATE
---------- ---------- ---------- --------- ---------
1          A                   8 02-JAN-08 31-DEC-08
2          A                   5 01-JAN-07 31-DEC-07
3          B                  15 01-JAN-07 31-DEC-07

SQL> SELECT A.PRODUCT_ID,
  2        NVL(SPEC_RATE , RATE)  RATE1
  3  FROM   PRODUCT_EG A,PROMOTION_EG B
  4  WHERE  A.PRODUCT_ID = B.PRODUCT_ID(+)
  5  AND    SYSDATE BETWEEN B.START_DATE(+)  AND B.END_DATE(+);

PRODUCT_ID      RATE1
---------- ----------
A                   8
B                  20
C                  30

SQL> SELECT PRODUCT_EG.PRODUCT_ID, NVL (PROMOTION_EG.SPEC_RATE, RATE)
  2  FROM PRODUCT_EG,
  3               (SELECT PROMOTION_ID, PRODUCT_ID, SPEC_RATE, START_DATE, END_DATE
  4                FROM PROMOTION_EG
  5                WHERE SYSDATE BETWEEN START_DATE AND END_DATE) PROMOTION_EG
  6  WHERE PRODUCT_EG.PRODUCT_ID = PROMOTION_EG.PRODUCT_ID(+)
  7  ;

PRODUCT_ID NVL(PROMOTION_EG.SPEC_RATE,RATE)
---------- --------------------------------
A                                         8
B                                        20
C                                        30

SQL> 



Kanish's Query

SQL> SELECT A.PRODUCT_ID, NVL (B.SPEC_RATE, RATE) rate1
  2   FROM PRODUCT_EG A,PROMOTION_EG B
  3   WHERE SYSDATE BETWEEN START_DATE AND END_DATE
  4   AND A.PRODUCT_ID = B.PRODUCT_ID(+);

PRODUCT_ID      RATE1
---------- ----------
A                   8

SQL>


OuterJoin is need for Getting desired output

Thumbs Up
Rajuvan.


[Updated on: Tue, 22 January 2008 03:57]

Report message to a moderator

Re: select query [message #295376 is a reply to message #295001] Tue, 22 January 2008 04:04 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

Sorry Rajuhn

Wrong typed i know outjoin must apply to all constants too.

Even though thanks my dear friend



SELECT A.PRODUCT_ID, NVL (B.SPEC_RATE, RATE) rate1
   FROM PRODUCT_EG A,PROMOTION_EG B
   WHERE SYSDATE BETWEEN START_DATE(+) AND END_DATE(+)
   AND A.PRODUCT_ID = B.PRODUCT_ID(+);


correct now.

wbr
kanish
Re: select query [message #295380 is a reply to message #295001] Tue, 22 January 2008 04:11 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes. Now there you are Smile

Thumbs Up
Rajuvan.
Previous Topic: narration
Next Topic: Update
Goto Forum:
  


Current Time: Sat Dec 10 20:35:09 CST 2016

Total time taken to generate the page: 0.19810 seconds