Return Distinct Set of Values Based on MAX Date

Question

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.

Table A

Table B

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" *Cause:
*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.

Thanks!

Answer 1

The sub-select SELECT Phase.* will return more than one column but it is in the SELECT section of the outer query where only one column is allowed. Move this nested select into the WHERE clause as a join.