2 Replies Latest reply on Apr 14, 2009 6:35 AM by Waleed Barakat

    SQL Query problem

    nickyj Level 1

      The following query works correctly using cocamysql and HeidiSQL but fails when I use it to build a recordset in Dreamweaver. I get an error which says " You have ans error in your SQL syntax; check.... use near '' at line 1.Does DW have query restrictions?

       

      Nick

       

      SELECT CONCAT(fn_sp,' ',lname) AS name
           , add1
           , add2
           , city
           , st
           , zip
           , non_US
        FROM CBA AS c1
      INNER
        JOIN DUES_PAID AS d1
          ON c1.CBA_ID = d1.CBA_ID
      WHERE c1.fall = 1
         AND c1.resigned IS NULL
         AND c1.sp_died IS NULL
         AND c1.mbr_died IS NULL
         AND c1.splname IS NULL
         AND (
             d1.dues_year = 2008
          OR ( UCASE(c1.role) LIKE '%LIFE%'
            OR UCASE(c1.role) LIKE '%LIB%'
            OR UCASE(c1.role) LIKE '%HISTORICAL%'
            OR UCASE(c1.role) LIKE '%PRESS%'
            OR UCASE(c1.role) LIKE '%HONOR%'
             )
             )
      UNION
      SELECT CONCAT(fn_sp,' ',lname) AS name
           , add3
           , add4
           , city2
           , st2
           , zip2
           , non_US
        FROM CBA AS c1
      INNER
        JOIN DUES_PAID AS d1
          ON c1.CBA_ID = d1.CBA_ID
      WHERE c1.fall = 0
         AND c1.resigned IS NULL
         AND c1.sp_died IS NULL
         AND c1.mbr_died IS NULL
         AND c1.splname IS NULL
         AND (
             d1.dues_year = 2008
          OR ( UCASE(c1.role) LIKE '%LIFE%'
            OR UCASE(c1.role) LIKE '%LIB%'
            OR UCASE(c1.role) LIKE '%HISTORICAL%'
            OR UCASE(c1.role) LIKE '%PRESS%'
            OR UCASE(c1.role) LIKE '%HONOR%'
             )
             )
      UNION
      SELECT CONCAT(fn_sp,' ',lname,' AND ',spname,' ',splname) AS name
           , add1
           , add2
           , city
           , st
           , zip
           , non_US
        FROM CBA AS c1
      INNER
        JOIN DUES_PAID AS d1
          ON c1.CBA_ID = d1.CBA_ID
      WHERE c1.fall = 1
         AND c1.resigned IS NULL
         AND c1.sp_died IS NULL
         AND c1.mbr_died IS NULL
         AND c1.splname is not null
         AND (
             d1.dues_year = 2008
          OR ( UCASE(c1.role) LIKE '%LIFE%'
            OR UCASE(c1.role) LIKE '%LIB%'
            OR UCASE(c1.role) LIKE '%HISTORICAL%'
            OR UCASE(c1.role) LIKE '%PRESS%'
            OR UCASE(c1.role) LIKE '%HONOR%'
             )
             )
      UNION
      SELECT CONCAT(fn_sp,' ',lname,' AND ',spname,' ',splname) AS name
           , add3
           , add4
           , city2
           , st2
           , zip2
           , non_US
        FROM CBA AS c1
      INNER
        JOIN DUES_PAID AS d1
          ON c1.CBA_ID = d1.CBA_ID
      WHERE c1.fall = 0
         AND c1.resigned IS NULL
         AND c1.sp_died IS NULL
         AND c1.mbr_died IS NULL
         AND c1.splname is not null
         AND (
             d1.dues_year = 2008
          OR ( UCASE(c1.role) LIKE '%LIFE%'
            OR UCASE(c1.role) LIKE '%LIB%'
            OR UCASE(c1.role) LIKE '%HISTORICAL%'
            OR UCASE(c1.role) LIKE '%PRESS%'
            OR UCASE(c1.role) LIKE '%HONOR%'
             )
             )
      UNION
      SELECT (fn_mi & " " & lname) AS name
           , add1
           , add2
           , city
           , st
           , zip
           , non_US
        FROM CBA AS c1
      INNER
        JOIN DUES_PAID AS d1
          ON c1.CBA_ID = d1.CBA_ID
      WHERE c1.fall = 1
         AND c1.resigned IS NULL
         AND c1.mbr_died IS NULL
         AND c1.sp_died is not null
         AND (
             d1.dues_year = 2008
          OR ( UCASE(c1.role) LIKE '%LIFE%'
            OR UCASE(c1.role) LIKE '%LIB%'
            OR UCASE(c1.role) LIKE '%HISTORICAL%'
            OR UCASE(c1.role) LIKE '%PRESS%'
            OR UCASE(c1.role) LIKE '%HONOR%'
             )
             )
      UNION SELECT (fn_mi & " " & lname) AS name
           , add3
           , add4
           , city2
           , st2
           , zip2
           , non_US
        FROM CBA AS c1
      INNER
        JOIN DUES_PAID AS d1
          ON c1.CBA_ID = d1.CBA_ID
      WHERE c1.fall = 0
         AND c1.resigned IS NULL
         AND c1.mbr_died IS NULL
         AND c1.sp_died is not null
         AND (
             d1.dues_year > 2008
          OR ( UCASE(c1.role) LIKE '%LIFE%'
            OR UCASE(c1.role) LIKE '%LIB%'
            OR UCASE(c1.role) LIKE '%HISTORICAL%'
            OR UCASE(c1.role) LIKE '%PRESS%'
            OR UCASE(c1.role) LIKE '%HONOR%'
             )
             )
      ORDER
          BY non_US
           , zip;