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.