Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: Conditional return from SQL table function possible ? (9.7)

    I'm trying to get something like this to work and not having any luck. Either it's not supported or I'm missing some bit of syntax.

    My goal is to return a single record from a table.
    The case is there could be several candidate records in the table, and based on the attributes there is a priority for which of the records to return.
    I want to use this function in a join in a larger view and don't want to have to wire in the messy conditional logic in that view.

    Here is a simple test case illustrating what I want to do. It seems to not like having multiple RETURN statements.

    ----------------
    CREATE FUNCTION TEST_TAB_FUNC (PKEY varchar(32))
    RETURNS TABLE
    (
    RETVAR varchar(32)
    )
    LANGUAGE SQL
    --READS SQL DATA
    --NO EXTERNAL ACTION
    --DETERMINISTIC
    BEGIN ATOMIC
    IF EXISTS (SELECT * FROM SYSIBM.SYSDUMMY1 )
    THEN
    RETURN
    SELECT * FROM TABLE(VALUES ('YES')) ;
    ELSE
    RETURN
    SELECT * FROM TABLE(VALUES ('NO')) ;
    END IF;
    END
    ----------------

    In the real case, I want to do something like:
    IF EXISTS (SELECT * FROM MYTABLE WHERE COLUMNX = 'SOMEVALUE' AND FKEY = xxx )
    THEN
    RETURN SELECT * FROM MYTABLE WHERE COLUMNX = 'SOMEVALUE' AND FKEY = xxx ;
    ELSE
    RETURN SELECT * FROM MYTABLE WHERE COLUMNX = 'SOMEOTHERVALUE' AND FKEY = xxx ;
    END IF;


    Is something like this possible?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How about
    Code:
    RETURN 
      SELECT * FROM MYTABLE WHERE COLUMNX = 'SOMEVALUE' AND FKEY = xxx 
      union all
      SELECT * FROM MYTABLE WHERE COLUMNX = 'SOMEOTHERVALUE' AND FKEY = xxx 
        and not EXISTS (SELECT * FROM MYTABLE WHERE COLUMNX = 'SOMEVALUE' AND FKEY = xxx );
    The table function will most likely perform worse than "the messy conditional logic in that view".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Thanks n_i. And you're probably right, the UNION ALL will work directly in the view. I hadn't considered that. I always forget about the UNION operations for some reason..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sometimes,
    UNION(or UNION ALL) may be replaced by a single sub-select with inventions in WHERE clause,
    if "from table" were same.

    I thought that your query might be re-wrttien like the following,
    even if it might be not optimal.
    Code:
    RETURN 
    SELECT col_a , col_b , ...
     FROM  (SELECT t.*
                 , MAX(CASE COLUMNX WHEN 'SOMEVALUE' THEN 'Yes' ELSE '' END)
                      OVER() AS somevalue
             FROM  MYTABLE AS t
             WHERE FKEY = xxx
              AND  COLUMNX IN ('SOMEVALUE' , 'SOMEOTHERVALUE')
           )
     WHERE COLUMNX   =  'SOMEVALUE'
      OR   somevalue <> 'Yes
      AND  COLUMNX   =  'SOMEOTHERVALUE'

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    you can try something like this:
    Code:
    SELECT *
    FROM (
    SELECT 
      DENSE_RANK() OVER (ORDER BY P.ORD) R_
    , T.*
    FROM 
      MYTABLE t
    , TABLE (VALUES
      (1, 'SOMEVALUE')
    , (2, 'SOMEOTHERVALUE')
    ) P (ORD, COLUMNX)
    WHERE t.FKEY=xxx and t.COLUMNX=p.COLUMNX
    )
    WHERE R_=1
    The idea is to order your input constants appropriately (column ORD), then join to your table, and return only rows corresponding to the 1-st constant found.
    If you want to return only the 1-st row, add FETCH FIRST 1 ROW ONLY at the end of the query.
    Regards,
    Mark.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Mark,

    DENSE_RANK is a nice idea!
    (RANK may work too.)

    Here is another example using DENSE_RANK.
    pro: join operation is unnecessary.
    con: need to repeat input constants(if considered performance).
    Code:
    SELECT *
     FROM (SELECT t.*
                , DENSE_RANK()
                     OVER(ORDER BY CASE COLUMNX
                                   WHEN 'SOMEVALUE'      THEN 1
                                   WHEN 'SOMEOTHERVALUE' THEN 2
                                   END  ASC                     ) AS d_rank
            FROM  MYTABLE t
            WHERE FKEY = xxx
             AND  COLUMNX IN ('SOMEVALUE' , 'SOMEOTHERVALUE')
          )
     WHERE d_rank = 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •