Home » SQL & PL/SQL » SQL & PL/SQL » Query Problem
Query Problem [message #316297] Thu, 24 April 2008 09:23 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

Could you please help me out with this query?

SELECT 
  PL.CONTAINER_LPN, 
  PL.REF_LPN, 
  PLOC.LOCATION_NAME, 
  RS.RMA_STAT_NAME, 
  POB.POB_NAME, 
  D.DISPOSITION_NAME, 
  PVC.POB_VENDOR_CLAIM_NUM, 
  PVR.RGA_NUM
FROM 
  POB_LPN PL, 
  POB_VENDOR_RGA PVR, 
  POB_STORE_TRANSFER PST, 
  POB_VENDOR_CLAIM PVC, 
  POB_DISPOSITION PD, 
  RMA_STAT RS, 
  POB, 
  DISPOSITION D, 
  POB_LOCATION PLOC 
WHERE 
  MIN(PL.RMA_STAT_ID) = 1260 AND 
  PL.RMA_STAT_ID = RS.RMA_STAT_ID AND 
  PL.POB_DISPOSITION_ID = PD.POB_DISPOSITION_ID AND 
  PD.DISPOSITION_ID = D.DISPOSITION_ID AND 
  PL.VENDOR_POB_ID = POB.POB_ID AND 
  PL.REF_LPN IS NOT NULL AND 
  PD.DISPOSITION_ID IN (30,50,110,120) AND 
  PL.POB_VENDOR_RGA_ID = PVR.POB_VENDOR_RGA_ID (+) AND 
  PL.POB_STORE_TRANSFER_ID = PST.POB_STORE_TRANSFER_ID (+) AND 
  PVC.POB_VENDOR_CLAIM_ID (+) = PST.POB_VENDOR_CLAIM_ID AND 
  PL.POB_LOCATION_ID = PLOC.POB_LOCATION_ID (+) AND 
  PL.POB_ID = 30000 
GROUP BY 
  PL.CONTAINER_LPN, 
  PL.REF_LPN, 
  PLOC.LOCATION_NAME, 
  RS.RMA_STAT_NAME, POB.POB_NAME, 
  D.DISPOSITION_NAME, 
  PVC.POB_VENDOR_CLAIM_NUM, 
  PVR.RGA_NUM 
ORDER BY 
  PL.CONTAINER_LPN


The min function that I have used after the where clause is not allowed. I understand why.

However, my requirement is like this. PL.CONTAINER_LPN will have several RMA_STAT_IDs and I wish to compare 1260 with the minimum of the rma_stat_ids for that PL.CONTAINER_LPN.

Could you please help me out on how to construct this query?

Sharath
Re: Query Problem [message #316302 is a reply to message #316297] Thu, 24 April 2008 09:43 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
use HAVING.
Re: Query Problem [message #316328 is a reply to message #316302] Thu, 24 April 2008 11:32 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi Joy,

Thank you so much for your reply.

Use of the having clause solved one of my problems.

The other issue is that in this code

SELECT 
  PL.CONTAINER_LPN, 
  PL.REF_LPN, 
  PLOC.LOCATION_NAME, 
  RS.RMA_STAT_NAME,
  RS.RMA_STAT_ID,
  POB.POB_NAME, 
  D.DISPOSITION_NAME, 
  PVC.POB_VENDOR_CLAIM_NUM, 
  PVR.RGA_NUM
FROM 
  POB_LPN PL, 
  POB_VENDOR_RGA PVR, 
  POB_STORE_TRANSFER PST, 
  POB_VENDOR_CLAIM PVC, 
  POB_DISPOSITION PD, 
  RMA_STAT RS, 
  POB, 
  DISPOSITION D, 
  POB_LOCATION PLOC 
WHERE 
  PL.POB_DISPOSITION_ID = PD.POB_DISPOSITION_ID AND 
  PD.DISPOSITION_ID = D.DISPOSITION_ID AND 
  PL.VENDOR_POB_ID = POB.POB_ID AND 
  PL.REF_LPN IS NOT NULL AND 
  PD.DISPOSITION_ID IN (30,50,110,120) AND 
  PL.POB_VENDOR_RGA_ID = PVR.POB_VENDOR_RGA_ID (+) AND 
  PL.POB_STORE_TRANSFER_ID = PST.POB_STORE_TRANSFER_ID (+) AND 
  PVC.POB_VENDOR_CLAIM_ID (+) = PST.POB_VENDOR_CLAIM_ID AND 
  PL.POB_LOCATION_ID = PLOC.POB_LOCATION_ID (+) AND 
  PL.POB_ID = 30000 
GROUP BY 
  PL.CONTAINER_LPN, 
  PL.REF_LPN, 
  PLOC.LOCATION_NAME, 
  RS.RMA_STAT_NAME, 
  RS.RMA_STAT_ID,
  POB.POB_NAME, 
  D.DISPOSITION_NAME, 
  PVC.POB_VENDOR_CLAIM_NUM, 
  PVR.RGA_NUM
HAVING
  MIN(PL.RMA_STAT_ID) = RS.RMA_STAT_ID AND
  MIN(PL.RMA_STAT_ID) = 1260
ORDER BY 
  PL.CONTAINER_LPN


There are multiple records in pob_lpn table with different rma_stat_ids for one container_lpn.

I wish to return multiple records, however, I need it to show me the min(rma_stat_id) for all the records, i.e. even if the rma_stat_ids are 500 and 400 for two records, it should return me 400 as the rma_stat_id for both records.

Your thoughts?

Thanks,

Sharath
Re: Query Problem [message #316344 is a reply to message #316328] Thu, 24 April 2008 12:52 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Hard to follow without any tables or data, but what's wrong with using min(rma_stat_id) in your SELECT?
Previous Topic: Calling pipelined function from pipelined function.
Next Topic: SQL Plus script question
Goto Forum:
  


Current Time: Wed Dec 07 07:10:05 CST 2016

Total time taken to generate the page: 0.14728 seconds