-1

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 A Field & Values

Table B 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!

  • Why's this tagged with mysql if you're using Oracle? – Philᵀᴹ Mar 24 '15 at 21:12
  • Sorry about that - like I said, I'm incredibly new to this. Im using SQL Developer Freeware, which I thought was also called MySQL. I have drivers installed so that I cam tapping into both Oracle and Microsoft Server databases. Do I have my semantics wrong? If so, can you recommend the proper tag I should be posting this under? Also, can you give me any feedback as to whether or not I added the information that would be necessary for someone to help me with this issue? Thanks! – Netia Ingram Mar 24 '15 at 23:14
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.