This uses the same basic technique as jonearles (+1) (since removed) but eliminates the PIVOT
and would require only one function that turns the string into a CASE
statement.
SELECT substr(
MIN (
CASE WHEN Blank=1 THEN '1 Blank'
WHEN Error=1 THEN '2 Error'
WHEN InProgress=1 THEN '3 InProgress'
WHEN Completed=1 THEN '4 Completed'
END
)
,3) state_code
FROM m_object mo
JOIN m_form mf ON mo.id = mf.id
WHERE mo.v = :2 AND mf.formlayout_id = :1;
I assume the data looks something like this:
drop table m_object;
create table m_object as
(select 10 id, 0 blank, 0 error, 0 inprogress, 1 completed, 99 v from dual);
insert into m_object values (11,1,0,0,0,99);
insert into m_object values (12,0,0,0,1,99);
insert into m_object values (13,0,1,1,0,99);
drop table m_form;
create table m_form as (select 10 id, 20 formlayout_id from dual);
insert into m_form values (11,21);
insert into m_form values (12,22);
insert into m_form values (13,20);
Here is what the function might look like if it were done in PL/SQL. It was just thrown together and is only meant to illustrate building the SQL statement and is not meant to be an example of good coding.
set serveroutput on format wrapped
DECLARE
vPassed Varchar2(500) := 'Blank,Error,InProgress,Completed';
Function MakeCase(pOriginal In Varchar2) Return Varchar2 Is
vBuilt Varchar2(500);
vWord Varchar2(100);
vChar Char(1);
vWordCount Number(1) := 1;
Begin
For vLoop In 1..Length(pOriginal) Loop
vChar := substr(pOriginal,vLoop,1);
If (vChar = ',') Then
If (vBuilt IS NULL) Then
vBuilt := 'CASE WHEN ';
End If;
vBuilt := vBuilt || vWord || '=1 THEN '''
|| to_char(vWordCount,'FM0') || ' ' || vWord || ''' WHEN ';
vWord := '';
vWordCount := vWordCount + 1;
Else
vWord := vWord || vChar;
End If;
End Loop;
vBuilt := vBuilt || vWord || '=1 THEN '''
|| to_char(vWordCount,'FM0') || ' ' || vWord || ''' ';
vBuilt := vBuilt || ' END ';
Return vBuilt;
End;
BEGIN
vPassed := MakeCase(vPassed);
DBMS_Output.Put_Line(vPassed);
--Use vPassed here to build SQL statement.
END;
/
Logically the joins are resolved in the order of the ON
clauses from left to right.
The output of each join is a virtual table that goes into the next join.
So for the query in your question the virtual table result of A LJ B
is then right joined onto C
. The join condition of B.Col3 = C.Col4
will lose any null extended rows preserved by the original left join effectively turning the first join back into a inner join and the resulting virtual table (which preserves all rows from C
) is then inner joined onto D
.
So your initial query can be simplified as
SELECT *
FROM TBLA A
INNER JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
JOIN TBLD D ON C.Col5 = D.Col6
Which is effectively the same as (A IJ B) ROJ (C IJ D)
The order of the ON
clauses is not necessarily the same as the order the tables appear in the query. This could also be rewritten as (C IJ D) LOJ (A IJ B)
SELECT *
FROM TBLC C
INNER JOIN TBLD D
ON C.Col5 = D.Col6
LEFT JOIN TBLA A
INNER JOIN TBLB B
ON A.Col1 = B.Col2
ON B.Col3 = C.Col4
The position of the on clauses means that the outer join is carried out between the two virtual tables resulting from (C IJ D)
and (A IJ B)
rather than just on a single table.
In your second query conceptually the virtual table A x C
is left joined onto B preserving the entire cartesian product then the result of that is joined onto D
with the predicate C.Col5 = D.Col6
. This eliminates any rows from the final result that do not inner join between C
and D
meaning it is equivalent to
SELECT *
FROM TBLC C
JOIN TBLD D
ON C.Col5 = D.Col6
CROSS JOIN TBLA A
LEFT JOIN TBLB B
ON A.Col1 = B.Col2
AND B.Col3 = C.Col4
Best Answer
You could try the following restructured query which uses joins instead of the sub-selects;
Another couple of possibilities;
OR
This really depends on your data and requirements and only you can answer that.