I'm new to SQL and I am really struggling to put a specific query together. Let me start my question by explaining what fields & tables I am dealing with.
The unique key is the combination of SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, and B1_PER_ID3.
What I am trying to return is a list of distinct a.B1_ALT_ID along with the most recent SD_PRO_DES attached to the record where:
-B1_APPL_STATUS LIKE PENDING
-B1_ALT_ID LIKE BFN
-B1_PER_GROUP LIKE LICENSES
-B1_PER_SUB_TYPE LIKE MARIJ
And also from Table B -Return the SD_PRO_DES with the Max SD_APP_DD OR if more than one Max SD_APP_DD the record with the Max SD_STP_NUM for the Max SD_APP_DD Date -Its important to note that a B1_ALT_ID will typically have multiple SD_PRO_DES records, many with the same SD_APP_DD date.
I have spent many, many hours trying to get to a solution but the closest I've been able to come is this:
SELECT a.B1_ALT_ID, a.B1_PER_SUB_TYPE, (SELECT Phase.* FROM ( SELECT b.SD_PRO_DES ,b.SD_APP_DD ,ROW_NUMBER() OVER (PARTITION BY b.SD_PRO_DES ORDER BY b.SD_APP_DD) AS seq FROM GPROCESS_HISTORY b) Phase WHERE Phase.seq = 1 AND a.serv_prov_code = b.serv_prov_code(+) AND a.b1_per_id1 = b.b1_per_id1(+) AND a.b1_per_id2 = b.b1_per_id2(+) AND a.b1_per_id3 = b.b1_per_id3(+)) FROM B1PERMIT A, GPROCESS_HISTORY B WHERE a.B1_PER_GROUP LIKE 'Licenses' AND a.B1_APPL_STATUS LIKE '%Pending%' AND a.B1_ALT_ID LIKE '%BFN%' AND a.B1_PER_SUB_TYPE LIKE '%Marij%' AND a.serv_prov_code = b.serv_prov_code(+) AND a.b1_per_id1 = b.b1_per_id1(+) AND a.b1_per_id2 = b.b1_per_id2(+) AND a.b1_per_id3 = b.b1_per_id3(+)
Which returns this error:
ORA-00913: too many values
00913. 00000 - "too many values"
*Action: Error at Line: 56 Column: 2
I'm at a complete loss for how to fix this from there. If anyone has any thoughts, I would REALLY appreciate it. Also, for the record I am working off of Oracle SQL Developer. If you need to know anything else related to this question, please let me know.