Select is not valid at this position expecting (

Question

I have written a query with CASE but I am getting () problem.

select SM.subscriber_name as name ,
       SM.accountType as accountType,
       SM.middlename as middleName,
       SM.lastname as lastName,
       SM.title as title,
       SM.email as email,
       SM.company as company,
       SM.email1 as aEmail,
       ,
       SM.zipcode as zipcode,
       SM.phone_no as phoneNumber,
       SM.landlinenumber as landlineNumber,
       SM.login_id as loginId, 
       SD.subscriberType as subscriptionType,
       SD.product_id as productType,
       case SM.state when 'null' then '' as state else STDD.state_name as state end,
       case SM.city when 'null' then '' as city else  CDD.city_name as city end,
       case SM.country when 'null' then '' as country else CD.country_name as country end,
       SD.fulldownloadaccess as fullDownloadAccess,
       SD.emailid_limit as emailLimit,
       SD.acessTime as planTime
from subscriber_master SM , 
     subsciber_details SD,
     city_details CDD,
     state_details STDD, 
     country_details CD
where SM.subscriber_id=16704 and 
      SM.subscriber_id=SD.subscriber_id and 
      SM.country = CD.country_id and
      SM.state = STDD.state_id and
      SM.city = CDD.city_id;

Please help me where I can put the brackets

[CASE statement syntax in MS-SQL](https://stackoverflow.com/a/5487936/7124761) — Prashant Pimpale, Dec 26 '18 at 11:42
might be check for syntax --> `SM.email1 as aEmail` after this two `,,` — Prashant Pimpale, Dec 26 '18 at 11:44

Nick · Answer 1 · 2018-12-26T11:51:39.630

There are a couple of problems with your query. Firstly your CASE expressions are written incorrectly, the expression must be complete before any AS part (see the manual) so for example,

case SM.state when 'null' then '' as state else STDD.state_name as state end,

should be written as

case SM.state when 'null' then '' else STDD.state_name end as state,

Additionally if you are checking for a NULL value, rather than a string value of 'null', you need to write the CASE expression as:

case when SM.state IS NULL then '' else STDD.state_name end as state

Also part way down you have an extra , (between SM.email1 as aEmail, and SM.zipcode as zipcode,).

However none of these problems will give you the message in your title, is there some code you haven't shown us?

Angel M. · Accepted Answer · 2018-12-28T20:38:03.920

I would say you are interested to show the NULL's as empty values, so, you need to rebuild your query like:

  CASE sm.state 
          WHEN 'null' THEN ''  AS state 
          ELSE stdd.state_name AS state 
                                  end,

To be rewritten as:

  CASE  
          WHEN sm.state IS NULL THEN ''  
          ELSE stdd.state_name END AS state,

And the same for all 'NULL'. Also, the AS should be written after the END of the CASE

REVIEWED: Just realised that the sentence below should return the same but is much more compact and easier to read:

ISNULL(sm.state, '') AS state,

score 0 · Answer 3 · answered Dec 26 '18 at 12:17

You can change your case statement as shown below it will check both null and blank values

case ISNULL(SM.[state],'') when '' then '' else STDD.state_name end as [state],
       case ISNULL(SM.city,'') when '' then '' else CDD.city_name end as city,
       case ISNULL(SM.country,'') when '' then '' else CD.country_name end as country

You should use join for providing where conditions between different tables in select statement. Alternatively you can use the below modified query

select SM.subscriber_name as name ,
       SM.accountType as accountType,
       SM.middlename as middleName,
       SM.lastname as lastName,
       SM.title as title,
       SM.email as email,
       SM.company as company,
       SM.email1 as aEmail,       
       SM.zipcode as zipcode,
       SM.phone_no as phoneNumber,
       SM.landlinenumber as landlineNumber,
       SM.login_id as loginId, 
       SD.subscriberType as subscriptionType,
       SD.product_id as productType,
       case ISNULL(SM.[state],'') when '' then '' else STDD.state_name end as [state],
       case ISNULL(SM.city,'') when '' then '' else CDD.city_name end as city,
       case ISNULL(SM.country,'') when '' then '' else CD.country_name end as country,
       SD.fulldownloadaccess as fullDownloadAccess,
       SD.emailid_limit as emailLimit,
       SD.acessTime as planTime
from subscriber_master SM, 
     subsciber_details SD,
     city_details CDD,
     state_details STDD, 
     country_details CD      
where SM.subscriber_id=16704
 and  SM.subscriber_id=SD.subscriber_id and 
      SM.country = CD.country_id and
      SM.[state] = STDD.state_id and
      SM.city = CDD.city_id;

Select is not valid at this position expecting (

3 Answers3