Converting SQL Server code to Firebird produces "Token unknown" error

Question

I have my SQL Server code available, but I couldn't convert it to Firebird no matter how hard I tried. I would like your help in this matter. SQL Server and Firebird all tables are the same and the data types are the same.

SQL Server Database

SQL Server "STOK_FIYAT" Table Design

SQL Server "STOK_FIYAT" Table Data

SQL Server "STOK" Table Design

SQL Server "STOK" Table Data

My query string:

DECLARE @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @ColumnNameCollection AS NVARCHAR(MAX),
  @HeaderNameCollection AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT ',' + QUOTENAME(SF.FIYAT_NO)
            FROM STOK_FIYAT SF
      GROUP BY SF.FIYAT_NO
      ORDER BY SF.FIYAT_NO ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

-- Get header list. A DISTINCT list of CompMesoIDs
SELECT @HeaderNameCollection= ISNULL(@HeaderNameCollection + ',','') 
       + QUOTENAME(FIYAT_NO) + ' as FIYAT_' + CAST(FIYAT_NO AS VARCHAR(16))
FROM (SELECT FIYAT_NO FROM STOK_FIYAT) AS STFIYAT 
GROUP BY STFIYAT.FIYAT_NO
ORDER BY STFIYAT.FIYAT_NO

set @query = 'SELECT BLKODU,STOKKODU,STOK_ADI,'+@HeaderNameCollection+'  FROM (SELECT STOK.[BLKODU], [STOKKODU], [STOK_ADI], [FIYAT_NO], [FIYATI] FROM STOK LEFT JOIN STOK_FIYAT ON STOK.BLKODU = STOK_FIYAT.BLSTKODU) AS SourceTable PIVOT(MAX([FIYATI]) FOR [FIYAT_NO] IN('+@cols+')) AS PivotTable ORDER BY BLKODU ASC;'
execute(@query)

SQL output:

I have to do all the operations for Firebird.

Firebird Database:

Firebird "STOK" Table Design

Firebird "STOK_FIYAT" Table Design

I am not experienced with Firebird. The code I tried myself:

SET TERM ^ ;

EXECUTE BLOCK
AS
DECLARE cols VARCHAR(255);
DECLARE query VARCHAR(255);
DECLARE ColumnNameCollection VARCHAR(255);
DECLARE HeaderNameCollection VARCHAR(255);

BEGIN

cols = STUFF((SELECT ',' + QUOTENAME(SF.FIYAT_NO)
            FROM STOK_FIYAT SF
      GROUP BY SF.FIYAT_NO
      ORDER BY SF.FIYAT_NO ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(255)')
        ,1,1,'')

SELECT :HeaderNameCollection= ISNULL(:HeaderNameCollection + ',','') 
       + QUOTENAME(FIYAT_NO) + ' as FIYAT_' + CAST(FIYAT_NO AS VARCHAR(16))
FROM (SELECT FIYAT_NO FROM STOK_FIYAT) AS STFIYAT 
GROUP BY STFIYAT.FIYAT_NO
ORDER BY STFIYAT.FIYAT_NO

set @query = 'SELECT BLKODU,STOKKODU,STOK_ADI,'+:HeaderNameCollection+'  FROM (SELECT STOK.[BLKODU], [STOKKODU], [STOK_ADI], [FIYAT_NO], [FIYATI] FROM STOK LEFT JOIN STOK_FIYAT ON STOK.BLKODU = STOK_FIYAT.BLSTKODU) AS SourceTable PIVOT(MAX([FIYATI]) FOR [FIYAT_NO] IN('+:cols+')) AS PivotTable ORDER BY BLKODU ASC;'
execute(@query)

END
^

set term ; ^

The error I get:

Engine Error (code = 335544569):
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 14, column 13.
FOR.

SQL Error (code = -104):
Invalid token.

You have to understand what the original query does, not try to convert it blindly. The `XML PATH` trick is used in SQL Server versions before 2017 to aggregate strings. Since 2017 the `STRING_AGG` function is used instead. In Firebird, the equivalent is `LIST()`. In MySQL it's `GROUP_CONCAT` — Panagiotis Kanavos, Mar 04 '21 at 09:18
Firebird doesn't have `STUFF` nor `FOR XML PATH`, and especially the `FOR` in `FOR XML PATH` trips up that parser as it is a reserved word, resulting in a token unknown error (as would some of your subsequent statements which don't conform to the Firebird PSQL syntax). It would be more helpful if you explain what your original code is supposed to do, instead of expecting the SO community to provide a mechanical translation of your code. It would also be helpful to have the necessary Firebird DDL and some insert statements for a representative sample of data. — Mark Rotteveel, Mar 04 '21 at 09:19
Seems like you need a dynamic-pivot for FireBird.I guess you could try to google it — Sergey, Mar 04 '21 at 09:26

Converting SQL Server code to Firebird produces "Token unknown" error

0 Answers0