Home » SQL & PL/SQL » SQL & PL/SQL » Sql Help, please! - could I have come up with a less helpful title?
Sql Help, please! - could I have come up with a less helpful title? [message #253602] Tue, 24 July 2007 06:41 Go to next message
syang
Messages: 30
Registered: February 2007
Member
Hi All,

I have a table that has data value like this:

1. Data for bidder 1000088

BIDDER_ PROD_CTGRY_ID PROD_ITM_ID BID_PRICE_AMT
------- ------------- ----------- -------------
1000088 1 1 12.00
1000088 1 990 12.00
1000088 1 991 12.00
1000088 1 992 10.00

SQl>select bidder_num,PROD_CTGRY_ID,PROD_ITM_ID,BID_PRICE_AMT
from dmepos_supplier_bid_prod_item
where PROD_ITM_ID in ('1','990','991','992') and
bidder_num='1000088';

2. Data for bidder 0000001

BIDDER_ PROD_CTGRY_ID PROD_ITM_ID BID_PRICE_AMT
------- ------------- ----------- -------------
0000001 1 1 8.00
0000001 1 990 7.00
0000001 1 991 7.00
0000001 1 992 7.00

SQl>select bidder_num,PROD_CTGRY_ID,PROD_ITM_ID,BID_PRICE_AMT
from dmepos_supplier_bid_prod_item
where PROD_ITM_ID in ('1','990','991','992') and
bidder_num='0000001';

I will need to come up with a sql that compare this one cloumn bid_price_amt within this table among product item id 1, 990, 991,and 992, and find the bid_price amount that has different value among the 4 product item id (1,990, 991, 992)

In other words, how can I combine the above 2 sql statements and find the bid price 10.00 for product item 992 (bidder 100088) and 8.00 for product item 1 (bidder 0000001) in one sql statement.

According to the business rule, for the product item id 1,990,991, and 992, the bid price must be the same for these 4 prodcut item id. If not, the data is considered invaid. we need a sql to identify these invalid data.

Thank you for your help!

[Updated on: Tue, 24 July 2007 12:26] by Moderator

Report message to a moderator

Re: Sql Help, please! [message #253616 is a reply to message #253602] Tue, 24 July 2007 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then,
select bidder_num,
       sum(decode(item_id,1,bid_price_amt)) price_1,
       sum(decode(item_id,990,bid_price_amt)) price_990,
       sum(decode(item_id,991,bid_price_amt)) price_991,
       sum(decode(item_id,992,bid_price_amt)) price_992
from mytable
group by biffer_num
having sum(decode(item_id,1,bid_price_amt)) != sum(decode(item_id,990,bid_price_amt)) 
    or sum(decode(item_id,1,bid_price_amt)) != sum(decode(item_id,991,bid_price_amt)) 
    or sum(decode(item_id,1,bid_price_amt)) != sum(decode(item_id,992,bid_price_amt)) 
    or sum(decode(item_id,990,bid_price_amt)) != sum(decode(item_id,991,bid_price_amt)) 
    or sum(decode(item_id,990,bid_price_amt)) != sum(decode(item_id,992,bid_price_amt)) 
    or sum(decode(item_id,991,bid_price_amt)) != sum(decode(item_id,992,bid_price_amt)) 
/

Regards
Michel
Re: Sql Help, please! [message #254040 is a reply to message #253616] Wed, 25 July 2007 10:41 Go to previous messageGo to next message
syang
Messages: 30
Registered: February 2007
Member
Michel,

Thanks for your reply. I really appreciate it.

I kind of modified the script and ran it in one of the databases. The result looks good.

BIDDER_ CMPT LAST_CHG_ PRICE_1     PRICE_990   PRICE_991   PRICE_992
------- ---- --------- ----------- ----------- ----------- -----------
1000203 1002 12-JUL-07      158.00      158.00       65.00      158.00
1000866 1000 13-JUL-07      178.56      178.56      178.56       69.71
1000474 1000 23-JUN-07      178.56      178.56                   69.71
1000678 1000 05-JUL-07      178.56      178.56      178.56       69.71


However, it looks that we have some data missing for the bidder 1000474 under the column PRICE_991 when I added a select criteria on last_chg_dt. Any idea why this happens?

Here is the sql I used to get the above result:

select bidder_num,CMPTV_BID_AREA_NUM,last_chg_dt,
       sum(decode(prod_itm_id,1,bid_price_amt)) price_1,
       sum(decode(prod_itm_id,990,bid_price_amt)) price_990,
       sum(decode(prod_itm_id,991,bid_price_amt)) price_991,
       sum(decode(prod_itm_id,992,bid_price_amt)) price_992
from dmepos_supplier_bid_prod_item
group by bidder_num,CMPTV_BID_AREA_NUM,last_chg_dt
having sum(decode(prod_itm_id,1,bid_price_amt)) != sum(decode(prod_itm_id,990,bid_price_amt)) 
    or sum(decode(prod_itm_id,1,bid_price_amt)) != sum(decode(prod_itm_id,991,bid_price_amt)) 
    or sum(decode(prod_itm_id,1,bid_price_amt)) != sum(decode(prod_itm_id,992,bid_price_amt)) 
    or sum(decode(prod_itm_id,990,bid_price_amt)) != sum(decode(prod_itm_id,991,bid_price_amt)) 
    or sum(decode(prod_itm_id,990,bid_price_amt)) != sum(decode(prod_itm_id,992,bid_price_amt)) 
    or sum(decode(prod_itm_id,991,bid_price_amt)) != sum(decode(prod_itm_id,992,bid_price_amt))
/


Thanks!
sy

Re: Sql Help, please! [message #254046 is a reply to message #254040] Wed, 25 July 2007 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you don't have any data for 991 for this date I think but I don't have your data.

Regards
Michel
Re: Sql Help, please! [message #254051 is a reply to message #254046] Wed, 25 July 2007 11:36 Go to previous messageGo to next message
syang
Messages: 30
Registered: February 2007
Member
Michel,

We saw data for that date for 991. Please see below:

SQl>select BIDDER_NUM,CMPTV_BID_AREA_NUM,PROD_CTGRY_ID,PROD_ITM_ID,BID_PRICE_AMT,LAST_CHG_DT 
from cbss.dmepos_supplier_bid_prod_item
  2* where BIDDER_NUM='1000474' and CMPTV_BID_AREA_NUM='1000' and PROD_ITM_ID='991'
cbssqry@CBSSP> /

BIDDER_ CMPT PROD_CTGRY_ID PROD_ITM_ID BID_PRICE_AMT LAST_CHG_
------- ---- ------------- ----------- ------------- ---------
1000474 1000             1         991        178.56 23-JUN-07


Thanks!
sy

[Updated on: Wed, 25 July 2007 11:43] by Moderator

Report message to a moderator

Re: Sql Help, please! [message #254053 is a reply to message #254051] Wed, 25 July 2007 11:44 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't help you without a test case that I can reproduce.
Please post one (not thousand lines just a dozen).
Post create table and insert statement not a select output.

Regards
Michel

[Updated on: Wed, 25 July 2007 11:44]

Report message to a moderator

Previous Topic: EXCHANGE PARTITION
Next Topic: how to reove spaces and it becomes standard
Goto Forum:
  


Current Time: Sun Dec 04 16:24:23 CST 2016

Total time taken to generate the page: 0.06000 seconds