Oracle. If record exists by the first statement, ignore second one

Question

I'm having a question how to resolve my problem.

I have procedure where inside i'm having this SELECT inside LEFT OUTER JOIN witch are returning me some values:

SELECT * FROM database1 data1
                        JOIN database2 data2 ON data2.id = data1.attr_id
                        JOIN database3 data3 ON data3.attr_id = data2.id
                        JOIN database4 data4 ON data4.objt_attr_id = data3.id
                        JOIN database5 data5 ON data5.stya_id = data4.id
                            AND data5.value = 1
        JOIN database6 data6 ON data6.id = data5.sero_id
        JOIN database7 data7 ON srv.id = data6.srv_id
        JOIN database8 data8 ON data8.code IN ('CALC1','CALC2')
        WHERE data1.ordet_id IN (data8.id)

As you can see he is looking for CALC1 and CALC2 And then search by their ID's in data1.ordet_id. Naturally, he return me 2 entries witch is not correct.

How to make the check. When script will find record with CALC1, then he skips the check on CALC2, so the script is returning only 1 record by CALC1(not like now for both). And vice versa, if by CALC1 record wasn't found, he check the CALC2

Answer 1

I think you can take advantage of the analytical function row_number as following:

select * from
(SELECT data1.*, data2.*, .. data8.*, 
        row_number() over (partition by data1.ordet_id order by data8.code ) as rn 
        FROM database1 data1
        JOIN database2 data2 ON data2.id = data1.attr_id
        JOIN database3 data3 ON data3.attr_id = data2.id
        JOIN database4 data4 ON data4.objt_attr_id = data3.id
        JOIN database5 data5 ON data5.stya_id = data4.id
                            AND data5.value = 1
        JOIN database6 data6 ON data6.id = data5.sero_id
        JOIN database7 data7 ON srv.id = data6.srv_id
        JOIN database8 data8 ON data8.code IN ('CALC1','CALC2')
        WHERE data1.ordet_id IN (data8.id))
where rn = 1

-- Update

As per your below comment, You can also use NOT EXISTS and combination of CASE..WHEN as following:

SELECT  *
FROM
    DATABASE1 DATA1
    JOIN DATABASE2 DATA2 ON DATA2.ID = DATA1.ATTR_ID
    JOIN DATABASE3 DATA3 ON DATA3.ATTR_ID = DATA2.ID
    JOIN DATABASE4 DATA4 ON DATA4.OBJT_ATTR_ID = DATA3.ID
    JOIN DATABASE5 DATA5 ON DATA5.STYA_ID = DATA4.ID
                            AND DATA5.VALUE = 1
    JOIN DATABASE6 DATA6 ON DATA6.ID = DATA5.SERO_ID
    JOIN DATABASE7 DATA7 ON SRV.ID = DATA6.SRV_ID
    JOIN DATABASE8 DATA8 ON DATA8.CODE IN (
        'CALC1',
        'CALC2'
    )
WHERE
    CASE
        WHEN DATA8.CODE = 'CALC1' THEN DATA1.ORDET_ID
        WHEN DATA8.CODE = 'CALC2' THEN CASE
            WHEN NOT EXISTS (
                SELECT 1 FROM
                    DATABASE8 D8
                WHERE DATA1.ORDET_ID = D8.ID
                  AND D8.CODE = 'CALC1'
            ) THEN DATA1.ORDET_ID
        END
    END = DATA8.ID

Cheers!!

Answer 2

Join twice with OUTER JOIN (i.e. you loose no record if the matched code doesn't exists, but you will not duplicate the result) and the get the priorized code with COALESCE from the first or second join.

SELECT 
   data1.ordet_id ,
   COALESCE(data81.code, data82.code) code
FROM data1.ordet_id 
LEFT OUTER JOIN data81 ON data1.ordet_id = data81.id and data81.code = 'CALC1'
LEFT OUTER JOIN data82 ON data1.ordet_id = data82.id and data82.code = 'CALC2'