# SQL & PL/SQL

## Generate extra rows based on conditions

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;

**Frank Kulash**on Jan 22 2021