Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013
    Posts
    1

    Unanswered: T-SQL to DB2 SQL

    Hi,

    I'm trying to convert a T-SQL statement to DB2 SQL and I am not having any luck at all. It's a simple query in T-SQL, but the online resources I'm finding don't seem to offer simple answers to my questions.

    Any help would be greatly appreciated!

    Thanks,
    Steve.

    declare @fiscper varchar(7)
    declare @fiscyear varchar(4)
    declare @fiscper3 varchar(3)
    declare @currentdate datetime

    select @currentdate = getdate()

    if datepart(MM, @currentdate) > 7
    begin
    select @fiscyear = datepart(yyyy, @currentdate) + 1
    end
    else
    begin
    select @fiscyear = datepart(yyyy, @currentdate)
    end

    select @fiscper3 =
    case datepart(MM, dateadd(MM, -1, @currentdate))
    when 7 then '001'
    when 8 then '002'
    when 9 then '003'
    when 10 then '004'
    when 11 then '005'
    when 12 then '006'
    when 1 then '007'
    when 2 then '008'
    when 3 then '009'
    when 4 then '010'
    when 5 then '011'
    when 6 then '012'
    end

    select @fiscper = @fiscyear + @fiscper3

    select dateadd(dd, -1, convert(datetime, datename(yyyy, getdate()) + '/' + convert(varchar, datepart(MM, getdate())) + '/01')),
    DATAPAKID
    FROM SAPTABLE
    WHERE FISCPER = @fiscper

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

    assuming that you set a statement termination character to '/' and use db2 9.7 or newer.
    You can run it as is using db2 command line processor:
    db2 -td/ -f my_file.sql

    Code:
    set serveroutput on/
    
    -- begin atomic ... end 
    -- is a compound statement
    begin atomic
    
    declare @fiscper varchar(7);
    declare @fiscyear varchar(4);
    declare @fiscper3 varchar(3);
    declare @currentdate timestamp;
    
    set @currentdate = current timestamp;
    
    -- It's better to use a case expression as for @fiscper3 below
    -- but this demonstrates the IF statement use.
    if month(@currentdate) > 7 then
      set @fiscyear = year(@currentdate) + 1;
    else
      set @fiscyear = year(@currentdate);
    end if;
    
    set @fiscper3 =
    case month(@currentdate - 1 month)
    when 7 then '001'
    when 8 then '002'
    when 9 then '003'
    when 10 then '004'
    when 11 then '005'
    when 12 then '006'
    when 1 then '007'
    when 2 then '008'
    when 3 then '009'
    when 4 then '010'
    when 5 then '011'
    when 6 then '012'
    end;
    
    set @fiscper = @fiscyear || @fiscper3;
    
    -- just for test
    call dbms_output.put_line('@fiscper: ' || @fiscper);
    
    -- db2 CLP can't print the result of a select statement 
    -- in a compound statement 
    -- so you can do it yourself in a for loop, for example.
    -- Here I amended your SAPTABLE table
    -- to a "virtual" table for demonstration purpose.
    
    -- As far as I understood you want do get the last day of the previous 
    -- month relative to the current date as I timestamp.
    -- Correct me if I'm wrong.
    for c1 as 
    select timestamp(current date - day(current date) days) as col, DATAPAKID
    FROM TABLE(VALUES 
      (1, '2014001') 
    , (2, '2014001') 
    ) SAPTABLE (DATAPAKID, FISCPER)
    WHERE FISCPER = @fiscper
    do 
      call dbms_output.put_line('col      : ' || c1.col);
      call dbms_output.put_line('datapakid: ' || c1.datapakid);
    end for;
    
    end/
    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
  •