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;
/
I don't have the energy to deal with all the joins through all your tables, but the general idea would be to join to the store table twice and check the results.
SELECT f.film_id,
CASE when store1.store_id is not null then 'Item exists in store 1' else 'Item missing from store 1' END as 'Store 1 Status'
FROM film f
LEFT OUTER JOIN store store1 on f.film_id = store1.film_id AND store1.store_id = 1
LEFT OUTER JOIN store store2 on f.film_id = store2.film_id AND store2.store_id = 2
This doesn't scale well if you had 100 stores, but for two it wouldn't be evil.
Hope that helps.
Best Answer
I came up with this solution but would definitely welcome improvements and other answers.
It creates a view, then examines the metadata of that view to build a new query. To support a limited amount of concurrency, it works with a set of 10,000 possible view names. In case the system crashes, it checks to make sure the view doesn't exist before using that name. To clear any possible lint, there's a feature to remove all the views.
Here it is in action:
https://livesql.oracle.com/apex/livesql/s/hvqxaqn9pwi8yk276bg4zt5gn
Output:
Using that output is a matter of copying the result and pasting it back into the editor.
If you can improve this answer, I would like to know if it's possible to bypass the final copy-and-paste step and produce the data via a pipelined function.