Home » SQL & PL/SQL » SQL & PL/SQL » INLINE QUERY
INLINE QUERY [message #195334] Thu, 28 September 2006 01:45 Go to next message
sanjiv_gore2002
Messages: 7
Registered: September 2006
Junior Member
Hi All,

STOCKM table structure(there will be only one row depending on key)
(composit primary key on warehouse and product)
(warehouse,product,description,physical_qty,standard_cost)

STKHSTM table structure(for one product there may be many rows depending trans type(sale,trans,w/o,grn...10) and movement date(WE MAY SALE A PRODUCT EVERY DAY))
(this is a transaction history table)
(warehouse,product,description,movement_qty,transaction_type,movement_date)

I am trying to build report which will show last date when any transcation happened on every product with that transaction type.for one product only one row should be there with last transaction type.

CAN SOME ONE PLEASE REVIEW FOLLOWING QUERY AND SUGGEST BETTER WAY!

SELECT
stk.WAREHOUSE,
stk.PRODUCT,
stk.DESCRIPTION,
stk.PHYSICAL_QTY,
stk.STANDARD_COST,
stk.physical_qty*stk.Standard_Cost,
mdt.LAST_MOVEMENT,
hst.transaction_type
FROM STOCKM stk, STKHSTM hst, (SELECT stk.WAREHOUSE,stk.PRODUCT,MAX(hst.movement_date) "LAST_MOVEMENT"
FROM STKHSTM hst, STOCKM stk
WHERE stk.WAREHOUSE=hst.WAREHOUSE
AND stk.PRODUCT=hst.PRODUCT
AND stk.PHYSICAL_QTY > 0
GROUP BY stk.WAREHOUSE, stk.PRODUCT) mdt
WHERE
stk.WAREHOUSE = '&wh'
AND stk.WAREHOUSE=hst.WAREHOUSE
AND stk.PRODUCT=hst.PRODUCT
AND stk.PHYSICAL_QTY>0
AND stk.WAREHOUSE = mdt.WAREHOUSE
AND stk.PRODUCT = mdt.PRODUCT
AND hst.movement_date = mdt.LAST_MOVEMENT
order by stk.PRODUCT,mdt.LAST_MOVEMENT desc

Thanks in advance,
Sanjiv
Re: INLINE QUERY [message #195367 is a reply to message #195334] Thu, 28 September 2006 04:47 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Would you not be better off using an analytic function ? You could get the most recent transaction date using:

last_value(movement_date) over (partition by stk.WAREHOUSE, stk.PRODUCT order by movement_date)
Re: INLINE QUERY [message #195388 is a reply to message #195367] Thu, 28 September 2006 06:23 Go to previous messageGo to next message
sanjiv_gore2002
Messages: 7
Registered: September 2006
Junior Member
hi Thanks for suggestion i will provide more information.

if i execute following query

SELECT
stk.WAREHOUSE,
stk.PRODUCT,
hst.movement_date,
hst.transaction_type
FROM STOCKM stk, STKHSTM hst
WHERE
stk.WAREHOUSE=hst.WAREHOUSE
AND stk.PRODUCT=hst.PRODUCT
AND stk.PHYSICAL_QTY>0
AND stk.PRODUCT='TEST1CODE'
order by hst.movement_date

out put will be for specific product

WAREHOUSE PRODUCT MOVEMENT_DATE TRANSACTION_TYPE
I3 TEST1CODE 13/07/2006 ADJ
I3 TEST1CODE 13/07/2006 SALE
I3 TEST1CODE 13/07/2006 SALE
I3 TEST1CODE 14/07/2006 ADJ
I3 TEST1CODE 25/07/2006 RECP
I3 TEST1CODE 25/07/2006 RECP
I3 TEST1CODE 26/07/2006 SALE
I3 TEST1CODE 28/07/2006 RECP
I3 TEST1CODE 02/08/2006 RECP
I3 TEST1CODE 03/08/2006 SALE
I3 TEST1CODE 03/08/2006 SALE
I3 TEST1CODE 04/08/2006 SALE
I3 TEST1CODE 07/08/2006 RECP
I3 TEST1CODE 07/08/2006 RECP
I3 TEST1CODE 08/08/2006 SALE
I3 TEST1CODE 08/08/2006 SALE
I3 TEST1CODE 09/08/2006 SALE
I3 TEST1CODE 10/08/2006 RECP
I3 TEST1CODE 29/08/2006 SALE
I3 TEST1CODE 18/09/2006 SALE
I3 TEST1CODE 22/09/2006 RECP

i want to retrive last row marked red for every product i.e. last transaction date and transaction type

i have tried your suggestion but not getting desired result

SELECT
stk.WAREHOUSE,
stk.PRODUCT,
stk.DESCRIPTION,
stk.PHYSICAL_QTY,
stk.STANDARD_COST,
stk.physical_qty*stk.Standard_Cost,
hst.transaction_type,
last_value(movement_date) over (partition by stk.WAREHOUSE, stk.PRODUCT order by movement_date) LAST_MOVEMENT
FROM STOCKM stk, STKHSTM hst
WHERE
stk.WAREHOUSE=hst.WAREHOUSE
AND stk.PRODUCT=hst.PRODUCT
AND stk.PHYSICAL_QTY>0
AND stk.PRODUCT='TEST1CODE'
AND hst.transaction_type NOT IN ('ADJ','W/O','TRAN')

Can you please suggest what must be wrong.

Thanks,
Sanjiv
Re: INLINE QUERY [message #195390 is a reply to message #195334] Thu, 28 September 2006 06:27 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Change it to a RANK() or ROW_NUMBER():

select
WAREHOUSE,
PRODUCT,
DESCRIPTION,
PHYSICAL_QTY,
STANDARD_COST,
physical_qty*stk.Standard_Cost,
transaction_type
from
(
SELECT
stk.WAREHOUSE,
stk.PRODUCT,
stk.DESCRIPTION,
stk.PHYSICAL_QTY,
stk.STANDARD_COST,
stk.physical_qty*stk.Standard_Cost,
hst.transaction_type,
RANK() over (partition by stk.WAREHOUSE, stk.PRODUCT order by movement_date desc) as RN
FROM STOCKM stk, STKHSTM hst
WHERE
stk.WAREHOUSE=hst.WAREHOUSE
AND stk.PRODUCT=hst.PRODUCT
AND stk.PHYSICAL_QTY>0
AND stk.PRODUCT='TEST1CODE'
AND hst.transaction_type NOT IN ('ADJ','W/O','TRAN'))
where rn=1
Re: INLINE QUERY [message #195393 is a reply to message #195390] Thu, 28 September 2006 06:45 Go to previous messageGo to next message
sanjiv_gore2002
Messages: 7
Registered: September 2006
Junior Member
Thanks very much it worked only change i mae i.e. date column and removed the transaction type where clause.

select
WAREHOUSE,
PRODUCT,
DESCRIPTION,
PHYSICAL_QTY,
STANDARD_COST,
physical_qty*Standard_Cost,
transaction_type,
movement_date
FROM (SELECT
stk.WAREHOUSE,
stk.PRODUCT,
stk.DESCRIPTION,
stk.PHYSICAL_QTY,
stk.STANDARD_COST,
stk.physical_qty*stk.Standard_Cost,
hst.transaction_type,
hst.movement_date,
RANK() over (partition by stk.WAREHOUSE, stk.PRODUCT order by

movement_date desc) as RN
FROM STOCKM stk, STKHSTM hst
WHERE
stk.WAREHOUSE=hst.WAREHOUSE
AND stk.PRODUCT=hst.PRODUCT
AND stk.PHYSICAL_QTY>0
AND stk.WAREHOUSE = '&wh')
where rn=1;

Thanks for your help.
Sanjiv
Re: INLINE QUERY [message #195435 is a reply to message #195393] Thu, 28 September 2006 10:41 Go to previous messageGo to next message
sanjiv_gore2002
Messages: 7
Registered: September 2006
Junior Member
Hi,

I have got another thought to achive this type of querys

consideration:

STOCKM - STOCK MASTER TABLE
always has unique row with composit key on column WAREHOUSE AND PRODUCT CODE.

e.g.

WAREHOUSE-PRODUCT-DESCRIPTION-PHYSICAL_QTY-STANDARD_COST (COLUMN)
I3 - TEST1CODE - 25 MM M03S (SO0)- 99.00 - 10,560.00 (DATA)

STKHSTM - STOCK HISTORY TABLE
always has multipal rows with different transaction type on any day for product for which we do transaction

WAREHOUSE-PRODUCT MOVEMENT_DATE TRANSACTION_TYPE
I3- TEST1CODE - 13/07/2006- ADJ
I3 TEST1CODE 13/07/2006 SALE
I3 TEST1CODE 13/07/2006 SALE
I3 TEST1CODE 14/07/2006 ADJ
I3 TEST1CODE 25/07/2006 RECP
I3 TEST1CODE 25/07/2006 RECP
I3 TEST1CODE 26/07/2006 SALE
I3 TEST1CODE 28/07/2006 RECP
I3 TEST1CODE 02/08/2006 RECP
I3 TEST1CODE 03/08/2006 SALE
I3 TEST1CODE 03/08/2006 SALE
I3 TEST1CODE 04/08/2006 SALE
I3 TEST1CODE 07/08/2006 RECP
I3 TEST1CODE 09/08/2006 RECP
I3 TEST1CODE 09/08/2006 GRN
I3 TEST1CODE 09/08/2006 SALE
I3 TEST1CODE 09/08/2006 SALE
I3 TEST1CODE 22/09/2006 ISSU
I3 TEST1CODE 22/09/2006 RWRK
I3 TEST1CODE 22/09/2006 SALE
I3- TEST1CODE - 22/09/2006- RECP

And the possibal transactions that can happen on these products are

TRANSACTION_TYPE
ADJ
BINT
COMP
DESP
DKIT
GRN
ISSU
PROD
RECP
RETN
RINV
RWRK
SALE
SCRP
SRET
TRAN
W/O


So if we want to have report which will show above mentioned all fields from two table and just because tranction type is different rows are repeating

e.g.

I3 TEST1CODE 22/09/2006 ISSU
I3 TEST1CODE 22/09/2006 RWRK
I3 TEST1CODE 22/09/2006 SALE
I3 TEST1CODE 22/09/2006 RECP

then are we able to use decode or case function for all tranction type and make above out put like

I3 TEST1CODE 22/09/2006 ISSU RWRK SALE RECP

so that we can reduce the redundandy in the output without compromising transaction type.

CURRENTLY I AM USING FOLLOWING QUERY BUT IT EXCLUDE SOME DATA WHICH IS REQUIRED.

select
DISTINCT
WAREHOUSE,
PRODUCT,
DESCRIPTION,
PHYSICAL_QTY,
STANDARD_COST,
physical_qty*Standard_Cost,
transaction_type,
movement_date
FROM (SELECT
stk.WAREHOUSE,
stk.PRODUCT,
stk.DESCRIPTION,
stk.PHYSICAL_QTY,
stk.STANDARD_COST,
stk.physical_qty*stk.Standard_Cost,
hst.transaction_type,
hst.movement_date,
RANK() over (partition by trim(stk.WAREHOUSE), trim(stk.PRODUCT) order by movement_date desc) as RN
FROM STOCKM stk, STKHSTM hst
WHERE
trim(stk.WAREHOUSE)=trim(hst.WAREHOUSE)
AND trim(stk.PRODUCT)=trim(hst.PRODUCT)
AND stk.PHYSICAL_QTY>0
AND trim(stk.WAREHOUSE) = '&wh'
AND hst.TRANSACTION_TYPE NOT IN ('W/O','ADJ','COMP'))
where rn=1;

THANKS IN ADVANCE,
SANJIV
Re: INLINE QUERY [message #195438 is a reply to message #195435] Thu, 28 September 2006 11:00 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Are you saying you want to denormalize/crosstab the query so that all the transaction_types are displayed on a single row ? What happens to the other fields like quantity, physical_description etc ? Are they all duplicated, so that you only need to take one of them ?
Re: INLINE QUERY [message #195476 is a reply to message #195438] Thu, 28 September 2006 22:41 Go to previous messageGo to next message
sanjiv_gore2002
Messages: 7
Registered: September 2006
Junior Member
Hi,

Yes you are correct, only thing differ is the transaction type
and that why other fields are getting duplicated.

Thanks,
Sanjiv
Re: INLINE QUERY [message #195521 is a reply to message #195334] Fri, 29 September 2006 03:24 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Something like this should do it:

select
WAREHOUSE,
PRODUCT,
min(description),
min(physical_qty),
min(standard_cost),
min(cost),
max(case when transaction_type = 'ISSU' then transaction_type else null end),
max(case when transaction_type = 'RWRK' then transaction_type else null end),
...
from
(select
DISTINCT
WAREHOUSE,
PRODUCT,
DESCRIPTION,
PHYSICAL_QTY,
STANDARD_COST,
physical_qty*Standard_Cost as cost,
transaction_type,
movement_date
FROM (SELECT
stk.WAREHOUSE,
stk.PRODUCT,
stk.DESCRIPTION,
stk.PHYSICAL_QTY,
stk.STANDARD_COST,
stk.physical_qty*stk.Standard_Cost,
hst.transaction_type,
hst.movement_date,
RANK() over (partition by trim(stk.WAREHOUSE), trim(stk.PRODUCT) order by movement_date desc) as RN
FROM STOCKM stk, STKHSTM hst
WHERE
trim(stk.WAREHOUSE)=trim(hst.WAREHOUSE)
AND trim(stk.PRODUCT)=trim(hst.PRODUCT)
AND stk.PHYSICAL_QTY>0
AND trim(stk.WAREHOUSE) = '&wh'
AND hst.TRANSACTION_TYPE NOT IN ('W/O','ADJ','COMP'))
where rn=1)
group by WAREHOUSE,
PRODUCT

Re: INLINE QUERY [message #195903 is a reply to message #195521] Tue, 03 October 2006 02:01 Go to previous message
sanjiv_gore2002
Messages: 7
Registered: September 2006
Junior Member
Hi Friends,

I found the best solution for this report.

SELECT
WAREHOUSE,
PRODUCT,
DESCRIPTION,
PHYSICAL_QTY,
STANDARD_COST,
STOCK_VALUE,
movement_date,
transaction_type
FROM (SELECT
stk.WAREHOUSE,
stk.PRODUCT,
stk.DESCRIPTION,
stk.PHYSICAL_QTY,
stk.STANDARD_COST,
stk.physical_qty*stk.Standard_Cost as STOCK_VALUE,
hst.movement_date,
hst.transaction_type,
ROW_NUMBER() OVER (PARTITION BY stk.WAREHOUSE,stk.PRODUCT ORDER BY movement_date DESC) TOP
FROM STOCKM stk, STKHSTM hst
WHERE
trim(stk.WAREHOUSE)=trim(hst.WAREHOUSE)
AND trim(stk.PRODUCT)=trim(hst.PRODUCT)
AND stk.PHYSICAL_QTY>0
AND trim(stk.WAREHOUSE) = '&wh'
AND to_date(hst.movement_date,'dd-mon-rr') <= to_date('&sdate','dd-mon-rr'))
WHERE TOP = 1
ORDER BY movement_date DESC

Now if there are 4 transaction happened on any of the product in day it will display the last one only.

Best regards and thanks,
Sanjiv
Previous Topic: sorting the month
Next Topic: desired report
Goto Forum:
  


Current Time: Thu Dec 08 03:58:23 CST 2016

Total time taken to generate the page: 0.24340 seconds