CREATE TABLE GTEST1(X NUMBER,FORMULA1 VARCHAR2(500), FORMULA2 VARCHAR2(500),FACT_COL NUMBER, mytype varchar2(20));
insert into gtest1 values(345,'[AB|TEST/DATA|NOVEMBER_M]','[AB|TEST/DATARR|NOVEMBER_D]',100, 'YES');
insert into gtest1 values(500,'[AB|TESTDATA2|NOVEMBER_M]','[AB|TESTDATA2|NOVEMBER_D]',100,'YES');
insert into gtest1 values (250,'[AB|TEST/DATA|NOVEMBER_M]','[AB|TEST/DATARR|NOVEMBER_D]',31,'NO') ;
commit;
Formula1 and formula2 has data seperated with |, I need to compare the first 2 words of each
example for 345, AB|TEST/DATA and AB|TEST/DATARR...if they are not same, generate extra 2 rows if they are same, only one row, but do it only for mytype YES.
With Frank's help from my previous post this was possible to generate extra row, but now i need to generate a 3rd row, with case statement how is it possible ?
So 345 should have 3 rows , 500 only one row as first 2 portions of formula match, 250 has mytype NO so no need to compare. Dont worry about what fact_col should be populated with, my problem here is how to get this extra 3rd row ?
the problem with below statement is that since its a case statement which is evaluated one after another, 3rd row is not getting generated, if the 1st two words of formula don't match, generate 2 extra rows
WITH cntr AS
(
SELECT LEVEL AS n
FROM dual
CONNECT BY LEVEL <= 3
)
SELECT c.n
, g.x, g.formula1, g.formula2, g.mytype
, CASE
WHEN c.n = 1
THEN g.fact_col
when c.n = 2 then -g.fact_col
when c.n = 3 then 0
END AS fact_col
FROM gtest1 g
JOIN cntr c ON c.n <= CASE
WHEN g.mytype <>'YES'
OR SUBSTR (formula1 || '||', 1, INSTR (formula1 || '||', '|', 1, 2))
= SUBSTR (formula2 || '||', 1, INSTR (formula2 || '||', '|', 1, 2))
THEN 1
when SUBSTR (formula1 || '||', 1, INSTR (formula1 || '||', '|', 1, 2))
<> SUBSTR (formula2 || '||', 1, INSTR (formula2 || '||', '|', 1, 2)) then
2
when
SUBSTR (formula1 || '||', 1, INSTR (formula1 || '||', '|', 1, 2))
<> SUBSTR (formula2 || '||', 1, INSTR (formula2 || '||', '|', 1, 2)) then
3
END
ORDER BY g.x, c.n;