5 Replies Latest reply: Jan 5, 2013 10:18 PM by user503635 RSS

    column threshold

    903224
      here i want to select the rows from dataset t but the column threshold are to be compared with the dataset t1,
      Ex.in table t1 , c1 theshold is 1 and hence from dataset 2 all 4 rows should be selected as these are greater than the threshold.
      
      pls. can it be written in sql?
      
      with t as
      (select 2 c1 ,3 c2 from dual union all
      select 3,3 from dual union all
      select 8,3 from dual union all
      select 4,3 from dual 
      ),
      with t1 as
      (
      select 'c1' k, 1 l from dual union all
      select 'c2', 1 from dual union all
      select 'c3', 1 from dual 
      )
        • 1. Re: column threshold
          user503635
          Try to understand your question. You need an parameter to indicate which colum you want to compare. Your t and t1 are not designed in a sound way, the relationship is quite messy, where is your c3 in t?

          Try below
          Select * from t
          Where decode(&param, 'c1', t.c1, 'c2', t.c2) > t1.l and t1.k = &param

          Edited by: user503635 on Jan 5, 2013 5:02 AM
          • 2. Re: column threshold
            Manik
            Are you looking for this : -- ( you may extend the where condition based on your requirement.)
            WITH t
                 AS (SELECT 2 c1, 3 c2 FROM DUAL
                     UNION ALL
                     SELECT 3, 3 FROM DUAL
                     UNION ALL
                     SELECT 8, 3 FROM DUAL
                     UNION ALL
                     SELECT 4, 3 FROM DUAL),
                 t1
                 AS (SELECT 'c1' k, 1 l FROM DUAL
                     UNION ALL
                     SELECT 'c2', 1 FROM DUAL
                     UNION ALL
                     SELECT 'c3', 1 FROM DUAL),
                 t2
                 AS (SELECT MAX (DECODE (UPPER (K), 'C1', L)) AS c1,
                            MAX (DECODE (UPPER (K), 'C2', L)) AS c2,
                            MAX (DECODE (UPPER (K), 'C3', L)) AS C3
                       FROM t1)
            SELECT t.*
              FROM t2, t
             WHERE (t.c1 > t2.c1 OR t.c2 > t2.c2);
            Cheers,
            Manik.
            • 3. Re: column threshold
              903224
              Hi Thanks for your reply...

              my requirement should be like below...

              DECLARE
              BEGIN
                 FOR i IN (SELECT 'c1' k, 1 l
                             FROM DUAL
                           UNION ALL
                           SELECT 'c2', 1
                             FROM DUAL
                           UNION ALL
                           SELECT 'c3', 1
                             FROM DUAL)
                 LOOP
                    INSERT INTO t
                                (create_user_id)
                       SELECT p.c1
                         FROM (SELECT 'c1' c1, 3 c2
                                 FROM DUAL
                               UNION ALL
                               SELECT 'c2', 3
                                 FROM DUAL
                               UNION ALL
                               SELECT 'c3', 3
                                 FROM DUAL
                               UNION ALL
                               SELECT 'c4', 3
                                 FROM DUAL) p
                        WHERE i.k > p.c2;
                 END LOOP;
              END;
              
              i am getting an error ,invalid number because i.k will be column c1 which should be column name as per my requirement...but its taking as value , how to invlke this as a column ?
              • 4. Re: column threshold
                SomeoneElse
                You can't do this with static SQL. You have to use dynamic SQL.
                • 5. Re: column threshold
                  user503635
                  Your I.k is char data type, your c2 is numer data type, thus cause error.

                  Are i.k and p.c1 the same ? If yes, try Where clause
                  i.k = p.c1 and p.c2 > i.l

                  If your c1, c2 in p store number value, then use decode, decode(I.k, 'c1', p.c1, 'c2', p.c2) > I.l. As mentioned, the structure is rigid, how about in future, you have c5, c6 ? Then you need modify table p and all relate SQLs.

                  Edited by: user503635 on Jan 5, 2013 8:10 PM