Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2013
    Posts
    11

    Unanswered: MS SQL code conversion to DB2 code

    //Hi All please find the DB2 code i am trying execute this code but i am getting delim..error and declare/ token is not allowed.

    CREATE FUNCTION getFlowSequenceNo(TargetDesignationId Integer, CaseDept Varchar(5), CaseTrack Varchar(5))
    Returns Integer
    LANGUAGE SQL
    CONTAINS SQL
    BEGIN ATOMIC
    DECLARE FlowSeqNo Integer;
    Set FlowSeqNo=0
    IF CaseDept ='BP'
    BEGIN
    IF CaseTrack ='RT'
    BEGIN
    IF TargetDesignationId ='1'
    THEN SET FlowSeqNo = 3
    IF TargetDesignationId = '8'
    THEN SET FlowSeqNo = 2
    IF TargetDesignationId = '3'
    THEN SET FlowSeqNo = 4
    IF TargetDesignationId = '21'
    THEN SET FlowSeqNo = 4

    END IF
    END@

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to say same things as this
    Quote Originally Posted by tonkuma View Post
    IBM Redbooks | Microsoft SQL Server to IBM DB2 UDB Conversion Guide

    You might be especially interesting in "Chapter 4. SQL considerations".
    (For example: 4.3 Date and time considerations , 4.6 SQL language syntax and semantics , 4.7 Built-in SQL functions , so on...)
    The syntax "If TargetDesignationId = '1' THEN ... " might be replaced by
    the case syntax something like,
    "CASE TargetDesignationId WHEN '1' THEN ... END"

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

    I wanr to see your effort to be able to work some MS SQL code on DB2 environment.

    Until now,
    you showed only the codes on MS SQL Serve,
    and I couldn't see no effort to ammendments the codes to work on DB2.


    I (thought perhaps, most of pesistances on this forum) had no reword from the posts or responds on this forum.

    So, my hope was that you showed some efforts by yourself,
    to conform to DB2 syntax and semantics.

  4. #4
    Join Date
    Dec 2013
    Posts
    11

    MS SQL code conversion to DB2 code

    The syntax "If TargetDesignationId = '1' THEN ... " might be replaced by
    the case syntax something like,
    "CASE TargetDesignationId WHEN '1' THEN ... END"

    Hi,
    could you please explain me little bit more, please suggest me how i can write nested if condition because i getting error, please explain more two line of code according to my code.


    Thanks & Regards
    Dharmendra

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ...
    ...
    Set FlowSeqNo=0
    IF CaseDept ='BP'
    BEGIN
    IF CaseTrack ='RT'
    BEGIN
    IF TargetDesignationId ='1'
    THEN SET FlowSeqNo = 3
    IF TargetDesignationId = '8'
    THEN SET FlowSeqNo = 2
    IF TargetDesignationId = '3'
    THEN SET FlowSeqNo = 4
    IF TargetDesignationId = '21'
    THEN SET FlowSeqNo = 4

    END IF
    ...
    In your code, "BEGIN ... END" and "IF ... END IF" were not corresponded.


    Anyway, please try something like...
    Code:
    ...
    ...
    SET FlowSeqNo
      = CASE
        WHEN CaseDept  = 'BP'
         AND CaseTrack = 'RT' THEN
             CASE TargetDesignationId
             WHEN '1'  THEN 3
             WHEN '8'  THEN 2
             WHEN '3'  THEN 4
             WHEN '21' THEN 4
             ELSE           0
             END
        ELSE 0
        END
    ;
    ...

  6. #6
    Join Date
    Dec 2013
    Posts
    11
    //Hi Thanks for adding more line of code defenitely it's help full for me, but i have one doubt i need a return type i.e(FlowSeqNo) mention in code, so i will get return type.

    ...
    ...
    Set FlowSeqNo=0
    IF CaseDept ='BP'
    BEGIN
    IF CaseTrack ='RT'
    BEGIN
    IF TargetDesignationId ='1'
    THEN SET FlowSeqNo = 3
    IF TargetDesignationId = '8'
    THEN SET FlowSeqNo = 2
    IF TargetDesignationId = '3'
    THEN SET FlowSeqNo = 4
    IF TargetDesignationId = '21'
    THEN SET FlowSeqNo = 4

    END IF
    Return FlowSeqNo;
    End
    ...

Posting Permissions

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