Statement Help

• martin.kerr 34088

  SSCommitted

  Points: 1752

  More actions

  February 17, 2012 at 8:03 am

  #250935

  Hi all, i am having trouble using the below statement:

  SELECT dqid, DQFlag, DQLinkID INTO DMCDUPES

  FROM OrangeDataDQ1

  WHERE UPPER([MANAGING COUNTRY] <> 'UK' OR [MANAGING COUNTRY] <> 'BENELUX'

  OR [MANAGING COUNTRY] <> 'ITALY'

  OR [MANAGING COUNTRY] <> 'SPAIN' OR [MANAGING COUNTRY] <> 'PORTUGAL' OR [MANAGING COUNTRY] <>

  'ISRAEL' OR [MANAGING COUNTRY] <> 'GERMANY' OR [MANAGING COUNTRY] <> 'DENMARK'

  OR [MANAGING COUNTRY] <> 'NORDIC' OR [MANAGING COUNTRY] <> 'FINLAND'

  OR [MANAGING COUNTRY] <> 'ICELAND' OR [MANAGING COUNTRY] <> 'NORWAY'

  OR [MANAGING COUNTRY] <> 'POLAND')

  AND [Salesforce Account ID] IS NULL

  AND DQFlag = 'M'

  i am getting an error of:

  Msg 102, Level 15, State 1, Line 3

  Incorrect syntax near '<'.

  Any help will be much appreciated.

• Recurs1on

  SSCommitted

  Points: 1974

  More actions

  February 17, 2012 at 8:07 am

  #1448538

  How about

  SELECT

  dqid,

  DQFlag,

  DQLinkID

  INTO DMCDUPES

  FROM OrangeDataDQ1

  WHERE UPPER([MANAGING COUNTRY]) NOT IN (

  'UK',

  'BENELUX',

  'ITALY',

  'SPAIN',

  'PORTUGAL',

  'ISRAEL',

  'GERMANY',

  'DENMARK',

  'NORDIC',

  'FINLAND',

  'ICELAND',

  'NORWAY',

  'POLAND'

  ) AND [Salesforce Account ID] IS NULL

  AND DQFlag = 'M'

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  February 17, 2012 at 8:09 am

  #1448540

  Because your WHERE condition are inside UPPER function. By default SQLServer is case insensitive. That should work:

  SELECT dqid, DQFlag, DQLinkID INTO DMCDUPES

  FROM OrangeDataDQ1

  WHERE ([MANAGING COUNTRY] <> 'UK' OR [MANAGING COUNTRY] <> 'BENELUX'

  OR [MANAGING COUNTRY] <> 'ITALY'

  OR [MANAGING COUNTRY] <> 'SPAIN' OR [MANAGING COUNTRY] <> 'PORTUGAL' OR [MANAGING COUNTRY] <>

  'ISRAEL' OR [MANAGING COUNTRY] <> 'GERMANY' OR [MANAGING COUNTRY] <> 'DENMARK'

  OR [MANAGING COUNTRY] <> 'NORDIC' OR [MANAGING COUNTRY] <> 'FINLAND'

  OR [MANAGING COUNTRY] <> 'ICELAND' OR [MANAGING COUNTRY] <> 'NORWAY'

  OR [MANAGING COUNTRY] <> 'POLAND')

  AND [Salesforce Account ID] IS NULL

  AND DQFlag = 'M'

  Consider using NOT IN (...) or have country exclusion table (or temptable) and left join to it. Current query looks like noodles:hehe:

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]