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  |
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  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 #254051 is a reply to message #254046] |
Wed, 25 July 2007 11:36 |
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 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
Goto Forum:
Current Time: Tue Dec 03 22:04:56 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
