Skip to content
Advertisement

Will my query works without two dimension array in if else or not

I have confusion since I was looking inside and try to solve the problem. I have PHP application which is running in Oracle DB and so far some expression is written in MySQL and right now I want to migrate those query to Oracle SQL function and call function insted of writing query directly to model. The situation is following I have function line

public function getAtaNumber($data) {

    if( $data["Type"] == 1 ) {
    
        $sql = "SELECT SUM(NextNumber) + 1 AS NextNumber 
                FROM (
                        SELECT COUNT(a1.ATAID)  AS NextNumber 
                        FROM ata AS a1 
                        WHERE a1.ProjectID = '".$data["project"]["id"]."' 
                        AND a1.Ata = 1 
                        AND a1.ParentAta = 0 
                        AND a1.TYPE = 1 
                        AND a1.BecomeExternalAtaFromInternal = 0 
                    UNION ALL
                        SELECT COUNT(a2.ATAID)  AS NextNumber 
                        FROM ata AS a2 
                        WHERE a2.ProjectID = '".$data["project"]["id"]."' 
                        AND a2.Ata = 1 
                        AND a2.ParentAta = 0 
                        AND a2.BecomeExternalAtaFromInternal = 1 ) AS A";
    
    } else {
    
        $sql = "SELECT COUNT(ata.ATAID) + 1 AS NextNumber 
                FROM ata 
                WHERE ProjectID = '".$data["project"]["id"]."' 
                AND Ata = 1 
                AND ParentAta = 0 
                AND Type = 0 
                ORDER BY ATAID DESC 
                LIMIT 1;";
    
        if (!$result = $conn->query($sql)) {
            error_log($conn->error);
            return false;
        }
    }
}

This if make me confusion and I have no idea how to write this peace of code. Since I run SELECT statment both of them and it return correct result, but I have no idea what to do with this IF Since this is two dimension array and I have no idea how to write it in PLSQL Oracle SQL When I run the first SELECT statment

SELECT SUM(NextNumber) + 1 AS NextNumber 
FROM (
        SELECT COUNT(a1.ATAID)  AS NextNumber 
        FROM ata a1 
        WHERE a1.ProjectID = 137 
        AND a1.Ata = 1 
        AND a1.ParentAta = 0 
        AND a1.TYPE = 1 
        AND a1.BecomeExternalAtaFromInternal = 0 
        UNION ALL
        SELECT COUNT(a2.ATAID)  AS NextNumber 
        FROM ata  a2
        WHERE a2.ProjectID = 137 
        AND a2.Ata = 1 
        AND a2.ParentAta = 0 
        AND a2.BecomeExternalAtaFromInternal = 1 
    )  A

NEXTNUMBER
18

And When I run second SELECT query

SELECT COUNT(ata.ATAID) + 1 AS NextNumber 
FROM ata 
WHERE ProjectID = 137 
AND Ata = 1 
AND ParentAta = 0 
AND Type = 0 
ORDER BY ATAID DESC ; 



NEXTNUMBER
        1

Can someone tell me how to solve this two dimension array problem in PLSQL Oracle SQL or Does even this kind of array exist in Oracle SQL ? Just one notation I also try something like

IF(data"Type"==1)
    SELECT
ELSE 
    SELECT

Also

IF(data'Type'=1)
    SELECT
ELSE 
    SELECT

Advertisement

Answer

Here is a function which returns the next ATA Number for a given PROJECT_ID and TYPE.

create or replace function get_ata_number (
    p_project_id in number
   ,p_type       in number
) return number as
   
  l_NextNumber number;

begin  

  if p_type = 1 then

    SELECT SUM(NextNumber) + 1 
    into l_NextNumber
    FROM (
          SELECT COUNT(a1.ATAID)  AS NextNumber 
          FROM ata AS a1 
          WHERE a1.ProjectID = p_project_id
          AND a1.Ata = 1 
          AND a1.ParentAta = 0 
          AND a1.TYPE = 1 
          AND a1.BecomeExternalAtaFromInternal = 0 
          UNION ALL
          SELECT COUNT(a2.ATAID)  AS NextNumber 
          FROM ata AS a2 
          WHERE a2.ProjectID = p_project_id
          AND a2.Ata = 1 
          AND a2.ParentAta = 0 
          AND a2.BecomeExternalAtaFromInternal = 1 );
    
   else 

     SELECT COUNT(ata.ATAID) + 1 
     into l_NextNumber
     FROM ata 
     WHERE ProjectID = p_project_id
     AND Ata = 1 
     AND ParentAta = 0 
     AND Type = 0 ;

  end if;

  return l_NextNumber;
  
end get_ata_number;    

Note my assumption about the data type of PROJECT_ID, and correct it if necessary.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement