Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2017
    Posts
    1

    Question Answered: Function returns table

    Hello all!
    I'm new with DB2 and i'm stuck on a stupid query

    Let me explain my goal.
    I have a table TABLE_TEST which can contains one or no row for EMPLOYEEID='XXXXXXX'
    If there is no row, I want to return NO
    If there is a row, I want to return YES an NO

    Below the code, it's logical for me, but DB2 doesn't seems to do not understand my logical

    Someone can help me to solve that?

    Thanks a lot !!

    Code:
    CREATE OR REPLACE FUNCTION FTEST()
        RETURNS TABLE (ACTION VARCHAR(10))
      BEGIN
      IF (SELECT COUNT(*) 
           FROM TABLE_TEST 
           WHERE EMPLOYEEID = 'XXXXXXX') > 0 
      THEN 
        RETURN SELECT 'NO' as ACTION FROM DUAL
      ELSE
        RETURN SELECT 'NO' as ACTION FROM DUAL UNION SELECT 'YES' as ACTION FROM DUAL
      END IF;
    END

  2. Best Answer
    Posted by mark.bb

    "Hello,

    Try this:
    Code:
    CREATE OR REPLACE FUNCTION FTEST()
    RETURNS TABLE (ACTION VARCHAR(10))
    RETURN
    SELECT 'NO' as ACTION FROM SYSIBM.SYSDUMMY1
      UNION ALL
    SELECT 'YES' as ACTION
    FROM 
    (  
      SELECT COUNT(*) cnt
      FROM TABLE_TEST 
      WHERE EMPLOYEEID = 'XXXXXXX'
    ) t 
    where cnt<>0;
    "


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    Hello,

    Try this:
    Code:
    CREATE OR REPLACE FUNCTION FTEST()
    RETURNS TABLE (ACTION VARCHAR(10))
    RETURN
    SELECT 'NO' as ACTION FROM SYSIBM.SYSDUMMY1
      UNION ALL
    SELECT 'YES' as ACTION
    FROM 
    (  
      SELECT COUNT(*) cnt
      FROM TABLE_TEST 
      WHERE EMPLOYEEID = 'XXXXXXX'
    ) t 
    where cnt<>0;
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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