[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 817
  • Last Modified:

Stored procedure converter

Does anyone know of a tool to convert MS Sql scripts into Interbase Firebird scripts? I have a bunch of Stored Procedures in MS Sql that I would like to convert to be used in a Interbase Firebird database.
0
carmodyk
Asked:
carmodyk
  • 2
  • 2
1 Solution
 
NickUpsonCommented:
I've been using firebird & interbase for years and I've never heard of such a thing, so you may be out of luck
0
 
carmodykAuthor Commented:
Well this sucks,  I don't suppose you have any procedure code for creating a table by transposing another?

Example:

Table A
Date, Product, Volume
1/1/2007, ProdA, 23
1/1/2007, ProdB, 42
1/3/2007, ProdB, 6
1/1/2007, ProdC, 19
1/3/2007, ProdC, 25

Output Table
Date, ProdA, ProdB, ProdC
1/1/2007, 23, 42, 19
1/3/2007 0, 6, 25
0
 
Gary BenadeCommented:
This SP creates a temporary table containing the data you require, I'm sure kakor could do it half the code but it will do the job :)

Regards
Gary

SET TERM ^^ ;
CREATE PROCEDURE P_TRANSPOSE
AS
DECLARE VARIABLE PROD VARCHAR(32);
DECLARE VARIABLE SQL1 VARCHAR(1024);
DECLARE VARIABLE SQL2 VARCHAR(5000);
DECLARE VARIABLE GRP VARCHAR(128);
DECLARE VARIABLE GCNT INT;
BEGIN
     SQL1 = '';
     SQL2 = '';
     GRP = '';
     GCNT = 1;
     FOR SELECT DISTINCT(PRODUCT) FROM TABLEA
     INTO :PROD
     DO
     BEGIN
          IF( GRP != '') THEN
          BEGIN
               GRP = GRP || ',';
          END
          GRP = GRP || GCNT;
          GCNT = GCNT + 1;
          IF( SQL1 != '') THEN
          BEGIN
               SQL1 = SQL1 || ',';
          END
          SQL1 = SQL1 || PROD || ' VARCHAR(32)';
          IF( SQL2 != '') THEN
          BEGIN
               SQL2 = SQL2 || ',';
          END
          SQL2 = SQL2 || 'COALESCE((SELECT SUM(VOLUME) FROM TABLEA WHERE A."DATE" = "DATE" AND PRODUCT = ''' || PROD || '''),0) ' || PROD || '';
     END
     EXECUTE STATEMENT 'RECREATE TABLE TABLEA_TRANSPOSED("DATE" DATE, ' || SQL1 || ')';
     SQL2 = 'SELECT "DATE",' || SQL2 || ' FROM TABLEA A GROUP BY ' || GRP || ' ORDER BY "DATE"';
     EXECUTE STATEMENT 'INSERT INTO TABLEA_TRANSPOSED ' || SQL2;
/*     SELECT SUM(VOLUME)
     FROM TABLEA A WHERE A."DATE" = '01-JAN-2007' AND A.PRODUCT = 'PRODA'
     SELECT "DATE",
     COALESCE((SELECT SUM(VOLUME) FROM TABLEA WHERE A."DATE" = "DATE" AND PRODUCT = 'PRODA'),0) PRODA,
     COALESCE((SELECT SUM(VOLUME) FROM TABLEA WHERE A."DATE" = "DATE" AND PRODUCT = 'PRODB'),0) PRODB,
     COALESCE((SELECT SUM(VOLUME) FROM TABLEA WHERE A."DATE" = "DATE" AND PRODUCT = 'PRODC'),0) PRODC
     FROM TABLEA A
     GROUP BY 1,2,3
     ORDER BY "DATE" */
END
^^
SET TERM ; ^^
0
 
Gary BenadeCommented:
Sorry, didnt test that properly, it works if you do it 2 steps

SET TERM ^^ ;
CREATE PROCEDURE P_TRANSPOSE_STEP1
AS
DECLARE VARIABLE PROD VARCHAR(32);
DECLARE VARIABLE SQL1 VARCHAR(1024);
BEGIN
     SQL1 = '';
     FOR SELECT DISTINCT(PRODUCT) FROM TABLEA
     INTO :PROD
     DO
     BEGIN
          IF( SQL1 != '') THEN
          BEGIN
               SQL1 = SQL1 || ',';
          END
          SQL1 = SQL1 || PROD || ' VARCHAR(32)';
     END
     EXECUTE STATEMENT 'RECREATE TABLE TABLEA_TRANSPOSED("DATE" DATE, ' || SQL1 || ')';
END
^^
SET TERM ; ^^

SET TERM ^^ ;
CREATE PROCEDURE P_TRANSPOSE_STEP2
AS
DECLARE VARIABLE PROD VARCHAR(32);
DECLARE VARIABLE SQL2 VARCHAR(5000);
DECLARE VARIABLE GRP VARCHAR(1024);
DECLARE VARIABLE GCNT INT;
BEGIN
     SQL2 = '';
     GRP = '';
     GCNT = 1;
     FOR SELECT DISTINCT(PRODUCT) FROM TABLEA
     INTO :PROD
     DO
     BEGIN
          IF( GRP != '') THEN
          BEGIN
               GRP = GRP || ',';
          END
          GRP = GRP || GCNT;
          GCNT = GCNT + 1;
          IF( SQL2 != '') THEN
          BEGIN
               SQL2 = SQL2 || ',';
          END
          SQL2 = SQL2 || 'COALESCE((SELECT SUM(VOLUME) FROM TABLEA WHERE A."DATE" = "DATE" AND PRODUCT = ''' || PROD || '''),0) ' || PROD || '';
     END
     SQL2 = 'SELECT "DATE",' || SQL2 || ' FROM TABLEA A GROUP BY ' || GRP || ' ORDER BY "DATE"';
     EXECUTE STATEMENT 'INSERT INTO TABLEA_TRANSPOSED ' || SQL2;
END
^^
SET TERM ; ^^

0
 
carmodykAuthor Commented:
Thanks a bunch, you're the best!!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now