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;
/
Okay, so here's the data model I came up with.
PRODUCT -- to store product information and maintain parent-child hierarchy
id NUMBER "Primary Key Not Null"
level_code VARCHAR2 Not Null
name VARCHAR2 Not Null
description VARCHAR2
parent_id NUMBER Foreign Key references PRODUCT(id)
ORDERS -- to store orders for products
id NUMBER "Primary Key Not Null"
prod_id NUMBER "Foreign Key references PRODUCT(id) Not Null"
order_type VARCHAR2 "Not Null Default 'Default'"
order_qty NUMBER Not Null
order_date NUMBER Foreign Key references DATE_INFO(date_key)
delivery_date NUMBER "Foreign Key references DATE_INFO(date_key)
Check delivery_date >= order_date"
FORECAST -- to store forecast value for products (store value for higher levels, store value for lower levels after disaggregation from a parent)
id NUMBER "Primary Key Not Null"
product_id NUMBER "Foreign Key references PRODUCT(id) Not Null"
forecast_value NUMBER Not Null
week NUMBER "Foreign Key references DATE_INFO(date_key) Not Null"
DISAGGREGATION_RULES -- to store which method was used for disaggregating a value from a higher level to lower level and how much percentage got distributed to lower level
id NUMBER "Primary Key Not Null"
parent_product_id NUMBER "Foreign Key id references PRODUCT(id) Not Null"
child_product_id NUMBER "Foreign Key id references PRODUCT(id) Not Null"
method VARCHAR2 Not Null
from_week NUMBER "Foreign Key references DATE_INFO(date_key) Not Null"
to_week NUMBER "Foreign Key references DATE_INFO(date_key) Not Null Check end_week >= start_week"
percent_distribution NUMBER Not Null
DATE_INFO -- date dimension, has information about start date (has to be Saturday) and end date corresponding to the week in which a particular date falls
date_key NUMBER "Primary Key
Not Null"
full_date DATE Not Null
week_begin_date DATE Not Null
week_end_date DATE Not Null
As for the bucket number .. I am calculating week start date (date on Saturday, in my case) with the following function
CREATE OR REPLACE FUNCTION get_week_start_date(v_bucket_num IN NUMBER)
RETURN DATE
IS
week_start_date DATE;
BEGIN
SELECT (TRUNC(SYSDATE+2, 'IW')-2) + ((v_bucket_num-1) * 7)
INTO week_start_date FROM dual;
RETURN week_start_date;
END;
Best Answer
If you look at the standard SQL way of constructing a recursive query, you would see something like this:
SQLFiddle: http://sqlfiddle.com/#!4/9eecb/4371/0
As you can see, it starts with selecting one row from the
DUALtable, then recursively applying aUNION ALLto that single row while the limiting condition remains true. TheCONNECT BYconstruct is just hiding this SQL plumbing from you.If you click on the Execution Plan link in the fiddle above, you'll see that.