Combine tree SELECT statment into one

Question

I am currently in situation that I dont know what to do, and have no idea how to solve problem. The problem is that I have one SELECT statment like

SELECT PaymentType FROM ata WHERE ATAID = $ataId

After that I have IF/ELSE statment like

if ($paymentType != 2 && $paymentType != 3) {
            $sql = "SELECT 
                        SUM(aa.Total) AS total
                    FROM 
                        ata_articles aa
                    INNER JOIN
                        weekly_report wr
                    ON
                        wr.id = aa.wrId
                    WHERE 
                        aa.ProjectId = $projectId 
                    AND 
                        aa.AtaId = $ataId 
                    AND 
                        wr.status = 5";
        } else {
            $sql = "SELECT 
                        SUM(aa.Total) AS total
                    FROM 
                        ata_articles aa
                    INNER JOIN
                        ata a
                    ON
                        a.ATAID = aa.ataId
                    INNER JOIN
                        invoices i
                    ON
                        i.Id = a.financeId
                    WHERE 
                        aa.ProjectId = $projectId 
                    AND 
                        aa.AtaId = $ataId 
                    AND 
                        i.Status = 6";
        }

As you can see the code is write in PHP but I need to write this in Oracle SQL function. So far what I try

           CASE WHEN PaymentType !=  2 and paymentType != 3 THEN 
             SELECT PaymentType FROM ata WHERE ATAID = 1889
               OR
                    SELECT 
                        SUM(aa.Total) AS total
                    FROM 
                        ata_articles aa
                    INNER JOIN
                        weekly_report wr
                    ON
                        wr.id = aa.wrId
                    WHERE 
                        aa.ProjectId = 141
                    AND 
                        aa.AtaId = 1
                    AND 
                        wr.status = 5
            ELSE  
                SELECT 
                    SUM(aa.Total) AS total
                FROM 
                    ata_articles aa
                INNER JOIN
                    ata a
                ON
                    a.ATAID = aa.ataId
                INNER JOIN
                    invoices i
                ON
                    i.Id = a.financeId
                WHERE 
                    aa.ProjectId = 1 
                AND 
                    aa.AtaId = 1 
                AND 
                    i.Status = 6
            END CASE;

I have try a multiple solution but none of these dosn't work. Can someone guide me and tell me what is the best solution to solve this issue ?

Answer 1

The PL/SQL equivalent would be something like this:

create or replace function get_article_total
    ( inProjectId    ata_articles.projectid%type
    , inAtaID        ata_articles.ataid%type
    , inPaymentType  number )
    return number
as
    articleTotal number;
begin
    if inPaymentType in (2,3) then
        select sum(aa.total) into articleTotal
        from   ata_articles aa
               join ata a on a.ataid = aa.ataid
               join invoices i on i.id = a.financeid
        where  aa.projectid = inProjectId
        and    aa.ataid = inAtaID
        and    i.status = 6;

    else
        select sum(aa.total) into articleTotal
        from   ata_articles aa
               join weekly_report wr on wr.id = aa.wrid
        where  aa.projectid = inProjectId
        and    aa.ataid = inAtaID
        and    wr.status = 5;

    end if;

    return articleTotal;
end get_article_total;