Simon Leung
asked on
Oracle SQL Query
I find that the Oracle sql can't filter the date properly. I suppose any effective date equal or smaller than the input date with lowest cost should be displayed. However, it doesn't. Any idea ?
Result
Table
SQL
Result
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
1 01-SEP-18 600 1
1
Table
----------------------------------
ITEMNO SUPPLIERID EFFECTIVE UNITCOST
---------- ---------- --------- ----------
1 1 01-SEP-18 600
1 1 01-JAN-19 650
1 1 01-JAN-20 700
1 2 01-JAN-19 645SQL
with t as (
select b.itemNo, b.effectiveDate, b.unitCost,b.SupplierID, c.itemName,
row_number() over (partition by b.ItemNo order by b.unitCost ) rn
from ToBePurchased a left join itemCost b on a.itemNo = b.itemNo
left join item c on a.itemNo = c.itemNo and b.effectiveDate <= to_date(20200101,'yyyy-mm-dd')
)
select *
from t
where rn=1
order by itemNo;
ASKER
seem like doesn't match for >= or <=... Any further advise ? Thx again.
SQL> with t as (
select b.itemNo, b.effectiveDate, b.unitCost,b.SupplierID, c.itemName,
row_number() over (partition by b.ItemNo order by b.unitCost ) rn
from ToBePurchased a left join itemCost b on a.itemNo = b.itemNo
left join item c on a.itemNo = c.itemNo and b.effectiveDate >= to_date('20170101','yyyy-mm-dd')
)
select *
from t
where rn=1
order by itemNo; 2 3 4 5 6 7 8 9 10
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
1 01-SEP-18 600 1
Panasonic Induction cooker 1
2 02-FEB-19 4200 2
Rasonic air conditioner 1
3 01-JAN-18 3490 2
ABC washer 1
SQL> with t as (
select b.itemNo, b.effectiveDate, b.unitCost,b.SupplierID, c.itemName,
row_number() over (partition by b.ItemNo order by b.unitCost ) rn
from ToBePurchased a left join itemCost b on a.itemNo = b.itemNo
left join item c on a.itemNo = c.itemNo and b.effectiveDate <= to_date('20170101','yyyy-mm-dd')
)
select *
from t
where rn=1
order by itemNo; 2 3 4 5 6 7 8 9 10
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
1 01-SEP-18 600 1
1
2 02-FEB-19 4200 2
1
3 01-JAN-18 3490 2
1
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
4 01-JAN-20 738 2
1
run this and see what's wrong...
with t as (
select b.itemNo, b.effectiveDate, b.unitCost,b.SupplierID, c.itemName,
row_number() over (partition by b.ItemNo order by b.unitCost ) rn
from ToBePurchased a left join itemCost b on a.itemNo = b.itemNo
left join item c on a.itemNo = c.itemNo and b.effectiveDate <= to_date('20200101','yyyymmdd')
)
select * from t;
you should pass correct form/format
to_date('20170101','fmyyyy-mm-dd')
>>>
to_date('20170101','fmyyyymmdd')
or
to_date('2017-01-01','fmyyyy-mm-dd')
but I guess your issue is with data...
what do you get from my previous query?...
to_date('20170101','fmyyyy-mm-dd')
>>>
to_date('20170101','fmyyyymmdd')
or
to_date('2017-01-01','fmyyyy-mm-dd')
but I guess your issue is with data...
what do you get from my previous query?...
oops, I see something strange here!
with t as (
select b.itemNo, b.effectiveDate, b.unitCost,b.SupplierID, c.itemName,
row_number() over (partition by b.ItemNo order by b.unitCost ) rn
from ToBePurchased a
left join itemCost b on a.itemNo = b.itemNo
and b.effectiveDate <= to_date('20200101','fmyyyymmdd')
left join item c on a.itemNo = c.itemNo
)
select * from t;ASKER
SQL> with t as (
select b.itemNo, b.effectiveDate, b.unitCost,b.SupplierID, c.itemName,
row_number() over (partition by b.ItemNo order by b.unitCost ) rn
from ToBePurchased a
left join itemCost b on a.itemNo = b.itemNo
and b.effectiveDate <= to_date('20200101','fmyyyymmdd')
left join item c on a.itemNo = c.itemNo
)
select * from t; 2 3 4 5 6 7 8 9
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
1 01-SEP-18 600 1
Panasonic Induction cooker 1
1 01-SEP-18 600 1
Panasonic Induction cooker 2
1 01-JAN-19 645 2
Panasonic Induction cooker 3
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
1 01-JAN-19 645 2
Panasonic Induction cooker 4
1 01-JAN-19 650 1
Panasonic Induction cooker 5
1 01-JAN-19 650 1
Panasonic Induction cooker 6
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
1 01-JAN-20 700 1
Panasonic Induction cooker 7
1 01-JAN-20 700 1
Panasonic Induction cooker 8
2 02-FEB-19 4200 2
Rasonic air conditioner 1
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
2 02-FEB-19 4200 2
Rasonic air conditioner 2
2 01-JAN-18 4250 1
Rasonic air conditioner 3
2 01-JAN-18 4250 1
Rasonic air conditioner 4
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
2 11-OCT-18 4300 1
Rasonic air conditioner 5
2 11-OCT-18 4300 1
Rasonic air conditioner 6
3 01-JAN-18 3490 2
ABC washer 1
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
3 01-JAN-18 3490 2
ABC washer 2
3 01-JAN-18 3500 1
ABC washer 3
3 01-JAN-18 3500 1
ABC washer 4
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
ITEMNAME RN
-------------------------------------------------- ----------
3 01-JAN-20 3500 1
ABC washer 5
3 01-JAN-20 3500 1
ABC washer 6
4 01-JAN-20 738 2
Loyala Rice cooker 1
21 rows selected.
SQL>
is anything wrong here?
and then we select the rows where rn=1
and then we select the rows where rn=1
ASKER
I guess on the inner part.
I want to ensure all rows in ToBePurchased, that any matching with ItemCost and Item table. However can I do a inner join for Item and ItemCost first, before apply ToBePurchased to "left join" the result...
Thx again.
I want to ensure all rows in ToBePurchased, that any matching with ItemCost and Item table. However can I do a inner join for Item and ItemCost first, before apply ToBePurchased to "left join" the result...
Thx again.
ASKER
select b.itemNo, b.effectiveDate, b.unitCost,b.SupplierID
from ToBePurchased a left join itemCost b on a.itemNo = b.itemNo
and b.effectiveDate <= to_date('20180101','YYYY-MM-DD')
I also find that the rows in ToBePurchased will be filter out even though I use left join. Any idea ?
Thx
from ToBePurchased a left join itemCost b on a.itemNo = b.itemNo
and b.effectiveDate <= to_date('20180101','YYYY-MM-DD')
I also find that the rows in ToBePurchased will be filter out even though I use left join. Any idea ?
Thx
ASKER
SQL> select b.itemNo, b.effectiveDate, b.unitCost,b.SupplierID
from ToBePurchased a left join itemCost b on a.itemNo = b.itemNo
and b.effectiveDate <= to_date('20180101','YYYY-MM-DD')
2 3 4 ;
ITEMNO EFFECTIVE UNITCOST SUPPLIERID
---------- --------- ---------- ----------
2 01-JAN-18 4250 1
2 01-JAN-18 4250 1
3 01-JAN-18 3500 1
3 01-JAN-18 3500 1
3 01-JAN-18 3490 2
3 01-JAN-18 3490 2
again, this is wrong!
to_date('20180101','YYYY-MM-DD')
make it
to_date('20180101','YYYYMMDD')
or
to_date('2018-01-01','YYYY-MM-DD')
your last query
... from ToBePurchased a left join itemCost b on a.itemNo = b.itemNo
and b.effectiveDate <= to_date('20180101','YYYY-MM-DD')
will include all from ToBePurchased...
nothing will be filtered out from this table...
or I could not get the issue...
to_date('20180101','YYYY-MM-DD')
make it
to_date('20180101','YYYYMMDD')
or
to_date('2018-01-01','YYYY-MM-DD')
I also find that the rows in ToBePurchased will be filter out even though I use left join. Any idea ?
your last query
... from ToBePurchased a left join itemCost b on a.itemNo = b.itemNo
and b.effectiveDate <= to_date('20180101','YYYY-MM-DD')
will include all from ToBePurchased...
nothing will be filtered out from this table...
or I could not get the issue...
ooo, I got it...
you have to select something from a :)
you get 3 empty rows
since you select all from b!
use
you have to select something from a :)
you get 3 empty rows
since you select all from b!
use
select a.itemNo, b.effectiveDate, b.unitCost,b.SupplierID
from ToBePurchased a
left join itemCost b on a.itemNo = b.itemNo
and b.effectiveDate <= to_date('20180101','YYYY-MM-DD')ASKER
ASKER
Spends hours and finally fine tune the query. However, i still leave one issue. Is it possible to filter only the item with lowest cost ?
Using the row_number grouping doesn't provide the number that I need. Any further advise.
Thx again.
Using the row_number grouping doesn't provide the number that I need. Any further advise.
Thx again.
select a.ItemNo, c.effectiveDate,c.unitCost,a.warehouseID, c.SupplierID, row_number() over (partition by c.ItemNo order by c.unitCost ) rn
from ToBePurchased a
left join (item b inner join itemCost c on b.itemno = c.ItemNo )
on b.ItemNo = a.ItemNo and c.effectiveDate <= to_date('20180101','YYYYMMDD')
order by a.ItemNo, c.unitCost
to select records with rn=1, you need another select
with t as (
select a.ItemNo, c.effectiveDate, c.unitCost, a.warehouseID, c.SupplierID,
row_number() over (partition by c.ItemNo order by c.unitCost ) rn
from ToBePurchased a
left join item b on b.ItemNo = a.ItemNo
inner join itemCost c on c.itemno = a.ItemNo
and c.effectiveDate <= to_date('20180101','YYYYMMDD')
)
select * from t where rn=1
order by a.ItemNoASKER
No all the rows from ToBePurchased (left join table) are included in the result. Any idea ?
SQL Result:
ITEMNO EFFECTIVE UNITCOST WAREHOUSEID SUPPLIERID RN
---------- --------- ---------- ----------- ---------- ----------
1 01-SEP-18 600 2 1 1
2 02-FEB-19 4200 1 2 1
3 01-JAN-18 3490 2 2 1
4 01-JAN-20 738 2 2 1
SQL> select * from ToBePurchased;
ITEMNO WAREHOUSEID ORDERQTY SUPPLIERID LOWESTUNITCOST
---------- ----------- ---------- ---------- --------------
1 2 30
1 3 85
2 1 20
2 3 20
3 2 8
3 3 6
4 2 20
7 rows selected.
SQL> select * from itemcost;
ITEMNO SUPPLIERID EFFECTIVE UNITCOST
---------- ---------- --------- ----------
1 1 01-SEP-18 600
1 1 01-JAN-19 650
1 1 01-JAN-20 700
1 2 01-JAN-19 645
2 1 01-JAN-18 4250
2 1 11-OCT-18 4300
2 2 02-FEB-19 4200
3 1 01-JAN-18 3500
3 2 01-JAN-18 3490
3 1 01-JAN-20 3500
4 2 01-JAN-20 738
ASKER
break out part of the query and display the result, it seems that using row_number() grouping can't get the result that I want. I only need those rows highlighted.
Does it mean we can't achieve that using the simple query ? Is there other way to achieve it (eg. loop through ToBePurchased cusor and link to other tables ) ?
Thx again.
Does it mean we can't achieve that using the simple query ? Is there other way to achieve it (eg. loop through ToBePurchased cusor and link to other tables ) ?
Thx again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
to_date('20200101','yyyy-mm-dd')