Home » SQL & PL/SQL » SQL & PL/SQL » Query Problem
Query Problem Thu, 24 April 2008 09:23
 hedonist123 Messages: 119Registered: August 2007 Senior Member
Hi,

```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.

Sharath
Re: Query Problem [message #316302 is a reply to message #316297] Thu, 24 April 2008 09:43
 joy_division Messages: 4758Registered: 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
 hedonist123 Messages: 119Registered: August 2007 Senior Member
Hi Joy,

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.

Thanks,

Sharath
Re: Query Problem [message #316344 is a reply to message #316328] Thu, 24 April 2008 12:52
 joy_division Messages: 4758Registered: 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: Fri Jul 21 11:57:12 CDT 2017

Total time taken to generate the page: 0.17146 seconds