SQL & PL/SQL

Hello all,

I’m hoping to get some help with my SQL code. I’m relatively new with SQL, and this is my first time using subqueries, and I’m getting some issues. Here’s what I’m looking to do, and I’ll try to make it as least confusing as possible.

My table contains multiple shipments each with multiple status codes for the shipment. I’m filtering for shipments containing a status code of SMRV. There might be multiple SMRV codes per shipment. Now, the problem is that an SMRV is sometimes associated with a SPLN code. If so, these codes will be assigned on the same day. My goal is to REMOVE all SMRV codes within a shipment in which the date the SMRV and SPLN codes are equal.

So my methodology is as such.
1. Subquery x retrieves all shipments with an SMRV code, along with the code date
2. Subquery y looks at those shipments and retrieves the SPLN dates
3. Outer query returns shipment #, all SMRV status codes and dates in which SMRV dates aren’t equal to an SPLN date

Issues
1. Running my subquery y returns a duplicate set of dates. I took care of this by using a SELECT DISTINCT function, but I’m not sure if my code is just wrong somehow.
2. Outer query just gives me an error I don’t understand.

ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'
06553. 00000 - "PLS-%s: %s"
*Cause:
*Action:
Error at Line: 164 Column: 29

Maybe there is an easier way to do this, but I’ve been studying online examples and can’t find out what I’m doing wrong. Thanks in advance!


select
cmt.SHPMT_NUM "Shipment_Number",
cmt.EVENT_CATG_TYPE_ID "Status Code",
cmt.EVENT_DT "SRMV_Date"

from
table.shpmt shp,
table.cmnt_and_event cmt,
(select distinct
cmt.EVENT_DT "SPLN_Date"
from
table.cmnt_and_event cmt,
(select
cmt.SHPMT_NUM "Shipment_Number",
cmt.EVENT_DT "SRMV_Date"
from
table.cmnt_and_event cmt
where cmt.SHPMT_NUM = '254151301'
and cmt.EVENT_CATG_TYPE_ID IN ('SRMV')) x
where cmt.SHPMT_NUM = x."Shipment_Number"
and cmt.EVENT_CATG_TYPE_ID IN ('SPLN')) y

where x."Shipment_Number" = cmt.SHPMT_NUM
and cmt.SHPMT_NUM = shp.SHPMT_NUM
and x."SRMV_Date" <> y."SPLN_Date" (ERROR seems to happen here)
and cmt.EVENT_CATG_TYPE_ID IN ('SRMV')