Forum Stats

  • 3,757,532 Users
  • 2,251,243 Discussions
  • 7,869,857 Comments

Discussions

Generate extra rows based on conditions

user650888
user650888 Member Posts: 502 Bronze Badge
edited Jan 22, 2021 7:22PM in SQL & PL/SQL

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;

Tagged:

Best Answer

Answers

  • mathguy
    mathguy Member Posts: 10,069 Blue Diamond
    edited Jan 22, 2021 8:03PM

    Perhaps something like this?

    with   helper (lvl) as (select level from dual connect by level <= 3)
    select gtest1.*
    from   gtest1 join helper
             on lvl = 1 or 
                mytype = 'YES' and substr(formula1, 1, instr(formula1 || '|', '|', 1, 2)) !=
                                   substr(formula2, 1, instr(formula2 || '|', '|', 1, 2))
    order  by x  -- if needed
    ;
    
         X FORMULA1                    FORMULA2                      FACT_COL MYTYPE
    ------ --------------------------- --------------------------- ---------- ------
       250 [AB|TEST/DATA|NOVEMBER_M]   [AB|TEST/DATARR|NOVEMBER_D]         31 NO   
       345 [AB|TEST/DATA|NOVEMBER_M]   [AB|TEST/DATARR|NOVEMBER_D]        100 YES  
       345 [AB|TEST/DATA|NOVEMBER_M]   [AB|TEST/DATARR|NOVEMBER_D]        100 YES  
       345 [AB|TEST/DATA|NOVEMBER_M]   [AB|TEST/DATARR|NOVEMBER_D]        100 YES  
       500 [AB|TESTDATA2|NOVEMBER_M]   [AB|TESTDATA2|NOVEMBER_D]          100 YES 
    

    Note about the number of pipe symbols in the formulae: If a formula has at least one pipe symbol (meaning: at least two "words"), the SUBSTR...INSTR thing will return those two words (possibly terminated by a second pipe, if there was one in the input). However, if a formula only has one pipe (meaning, it doesn't contain more than one word), my combination of SUBSTR and INSTR will return NULL, and any row with that kind of formula (if it's even possible in the inputs) will be returned exactly once, never three times. If you need a different handling, that can be arranged easily.

  • user650888
    user650888 Member Posts: 502 Bronze Badge

    Omg let me try this, i thought this would be impossible, so looks like helper is a new function that i am unaware of

  • mathguy
    mathguy Member Posts: 10,069 Blue Diamond
    edited Jan 22, 2021 8:08PM

    No, "helper" is a small, helper SUBQUERY that generates three rows. You already had something similar in the older solution.

    Essentially this uses a "cross join" of your data with this helper subquery - generating EVERY original row three times instead of just once. But, in the output, we only keep one copy, UNLESS ........ , in which case we keep all three rows. This is the join condition.

  • mathguy
    mathguy Member Posts: 10,069 Blue Diamond

    Oh, I see - you were also doing something with FACT_COL.

    You can add that to the solution I proposed:

    with   helper (lvl) as (select level from dual connect by level <= 3)
    select x, formula1, formula2, mytype
         , case lvl when 1 then fact_col when 2 then -fact_col else 0 end as fact_col    --  ADD THIS LINE
    from   gtest1 join helper
             on lvl = 1 or 
                mytype = 'YES' and substr(formula1, 1, instr(formula1 || '|', '|', 1, 2)) !=
                                   substr(formula2, 1, instr(formula2 || '|', '|', 1, 2))
    order  by x  -- if needed
    ;
    


    There is still no reason to use a CASE expression in the join condition (even if you do need one in SELECT).