Incorrect syntax near ')'. SQL query is wrong while running on both DBeaver and on java mapper files?

Question

  DECLARE @source as NVARCHAR(MAX) = (SELECT md.[source] FROM sports_service.meet.meet_detail md WHERE md.meet_id = #{meetId})

    WHERE reg.is_waitlist = 0 AND reg.cancelled_dt IS NULL 
          AND NOT EXISTS (
            SELECT 1 FROM sports_service.meet.meet_invitations i
            WHERE i.meet_id = pmm.meet_id AND i.sports_person_id = reg.sports_person_id) 
     IF @source != 'MANUAL'
          AND EXISTS (
            SELECT 1 
            FROM sports_service.meet.session session
                INNER JOIN sports_service.meet.event event ON event.session_id = session.id 
                WHERE pmm.meet_id = session.meet_id
                    AND sports_service.dbo.fnGetMeetAge(p.birth_dt, detail.age_up_date, detail.id_format) <![CDATA[>=]]> event.low_age
                    AND sports_service.dbo.fnGetMeetAge(p.birth_dt, detail.age_up_date, detail.id_format) <![CDATA[<=]]> event.high_age
                    AND (event.eligible_gender IS NULL OR event.eligible_gender = p.gender))

looks like you made an error in your sql, but seriously, this is way too vague to check. — Stultuske, Jan 17 '19 at 07:02
ruakh please only focus on my sql condition check statement IF @source != 'MANUAL' is this correct or not? — Chiranjhivi Ghimire, Jan 17 '19 at 07:11
Can you post full error message. Also post SQL code which passed to SQL driver without XML escaping and other stuff. — talex, Jan 17 '19 at 07:14
When can you start an Sql statement with `WHERE` (`WHERE reg.is_waitlist = 0`...`)? — Joakim Danielson, Jan 17 '19 at 07:19
To answer my own question, I now see that the query has been edited in a way that makes it incomplete and incorrect. — Joakim Danielson, Jan 17 '19 at 07:25
That is invalid standard SQL to begin with. Which DBMS product are you using? — , Jan 17 '19 at 07:30

score 2 · Accepted Answer · answered Jan 17 '19 at 15:32

If you want to check some condition (AND EXISTS ... clause) only if some other condition holds (source is MANUAL) and you want to do that in one query instead of:

WHERE (...some condition ...) AND (.. some other condition ... )
IF @source != 'MANUAL'
    AND EXISTS (
        SELECT 1 ... )

which is not supported as it is not a correct sql syntax you can do:

WHERE (...some condition ...) AND (.. some other condition ... )
    AND (
       (
         (SELECT md.[source] 
          FROM sports_service.meet.meet_detail md 
          WHERE md.meet_id = #{meetId}
         ) = 'MANUAL'
       )
      OR EXISTS (
        SELECT 1 
        FROM ...
      )
    )

You need to check if the performance of the resulting query would be satisfactory.

It would be probably better to execute the query that gets the source first and then execute either query with the additional condition or without it depending on the source value. To do that you can generate the query in mybatis using dynamic SQL (including or excluding this additional condition).

score 1 · Answer 2 · answered Jan 18 '19 at 05:34

As you can't use If inside expression, you can write your sql like this:

DECLARE @source as NVARCHAR(MAX) = (SELECT md.[source] FROM sports_service.meet.meet_detail md WHERE md.meet_id = #{meetId})

    WHERE reg.is_waitlist = 0 AND reg.cancelled_dt IS NULL
    AND NOT EXISTS (
    SELECT
    1
    FROM
    sports_service.meet.meet_invitations i
    WHERE
    i.meet_id = pmm.meet_id
    AND i.sports_person_id = reg.sports_person_id)
    AND ( @source <> 'MANUAL' 
    AND 
    EXISTS ( SELECT 1 FROM
    sports_service.meet.session session
    INNER JOIN sports_service.meet.event event ON
    event.session_id = session.id
    WHERE
    pmm.meet_id = session.meet_id
    AND sports_service.dbo.fnGetMeetAge(p.birth_dt,
    detail.age_up_date,
    detail.id_format) >= event.low_age
    AND sports_service.dbo.fnGetMeetAge(p.birth_dt,
    detail.age_up_date,
    detail.id_format) <= event.high_age
    AND (event.eligible_gender IS NULL
    OR event.eligible_gender = p.gender)))

score 0 · Answer 3 · answered Jan 17 '19 at 07:15

0

You can't use IF inside expression.

answered Jan 17 '19 at 07:15

talex

17,973
3
29
66

Incorrect syntax near ')'. SQL query is wrong while running on both DBeaver and on java mapper files?

3 Answers3