0

I need to convert SQL to transbase because when I use the below script in Transbase I get a pretty ugly error.

My script:

SELECT  STR_ID, TEX_TEXT AS STR_DES_TEXT,   IF( EXISTS( SELECT * FROM   tof_SEARCH_TREE AS SEARCH_TREE2
  WHERE SEARCH_TREE2.STR_ID_PARENT <> tof_SEARCH_TREE.STR_ID
  ), 1, 0) AS DESCENDANTS
  FROM  tof_SEARCH_TREE
  INNER JOIN tof_DESIGNATIONS ON DES_ID = STR_DES_ID
  INNER JOIN tof_DES_TEXTS ON TEX_ID = DES_TEX_ID
WHERE   STR_ID_PARENT <> NULL AND   DES_LNG_ID = 21 AND EXISTS (
SELECT * FROM tof_LINK_GA_STR 
  INNER JOIN tof_LINK_LA_TYP ON LAT_TYP_ID = 3822 AND LAT_GA_ID = LGS_GA_ID
  INNER JOIN tof_LINK_ART ON LA_ID = LAT_LA_ID
WHERE   LGS_STR_ID = STR_ID
);

My error is:

compile error: ')' expected: at line >2<: FROM tof_SEARCH_TREE AS SEARCH_TREE2

Andreea Becker
  • 1
  • 3

1 Answers1

1

I have not used Transbase, but it claims to support ANSI SQL functionality. if() is not an ANSI function, so you might try:

SELECT STR_ID, TEX_TEXT AS STR_DES_TEXT,
       (CASE WHEN EXISTS (SELECT 1
                          FROM tof_SEARCH_TREE AS SEARCH_TREE2
                          WHERE SEARCH_TREE2.STR_ID_PARENT <> tofSEARCH_TREE.STR_ID
                         )
             THEN 1 ELSE 0
        END) AS DESCENDANTS
FROM tof_SEARCH_TREE INNER JOIN
     tof_DESIGNATIONS
     ON DES_ID = STR_DES_ID INNER JOIN
     tof_DES_TEXTS
     ON TEX_ID = DES_TEX_ID
WHERE STR_ID_PARENT IS NOT NULL AND
      DES_LNG_ID = 21 AND
      EXISTS (SELECT 1
              FROM tof_LINK_GA_STR INNER JOIN
                   tof_LINK_LA_TYP
                   ON LAT_TYP_ID = 3822 AND LAT_GA_ID = LGS_GA_ID INNER JOIN
                   tof_LINK_ART
                   ON LA_ID = LAT_LA_ID
              WHERE LGS_STR_ID = STR_ID
             );

I think this is ANSI compliant. And, it should also work in MySQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786