Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

update issue in ms sql server 2005

Posted on 2011-02-26
30
Medium Priority
?
193 Views
Last Modified: 2012-05-11
I get an error on this....
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='.



UPDATE C
set TIME_SENT_RANGE = CASE
 WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Eastern%' THEN 'Nat_Eastern'
 WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Central%' THEN 'Nat_Central'
 WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Pacific%' THEN 'Nat_Pacific'
FROM Campaign C
JOIN Area_Code_Time_Zone AC
WHERE TC_ID = 'TC_ID_SD9FJG3K' and CAMPAIGN_NAME = 'School_lunch'
0
Comment
Question by:GlobaLevel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
  • 4
  • +2
30 Comments
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 668 total points
ID: 34988192
You need to change your code:

WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Eastern%' THEN 'Nat_Eastern'

to

WHEN (LEFT(c.TO_NUMBER,3) = ac.AREA_CODE) like '%Eastern%' THEN 'Nat_Eastern'

do it for the rest of the lines
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988200
END seems to be missing
UPDATE C
set TIME_SENT_RANGE = CASE
.......
END -- missing
FROM Campaign C
JOIN Area_Code_Time_Zone AC
WHERE TC_ID = 'TC_ID_SD9FJG3K' and CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34988202
actually that was incorrect.

0
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34988203
I meant my post, sorry
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988210
As sammy mentioned something wrong with your case statement
I think, it could be something like
WHEN (LEFT(c.TO_NUMBER,3) = ac.AREA_CODE AND ac.AREA_CODE like '%Eastern%')  THEN 'Nat_Eastern'
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988211
Check your requirement. What conditions to satisfy. Give it properly
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988226
There are many issues found with your query
1.
LEFT(c.TO_NUMBER,3

Open in new window

is wrong. It should be closed like
LEFT(c.TO_NUMBER,3)

Open in new window


2.
(LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Eastern%' 

Open in new window

This is not valid CASE statement. It should be splitted off as I mentioned before or as you want.

3.

Open in new window

END is missing for CASE statement.

Raj
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34988290
I saw that unvalid case statementand know I have this..still errors:

UPDATE C
set TIME_SENT_RANGE = CASE
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC --ON c.TC_ID = ac.Country
WHERE c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988434
What is the error message now ?
Raj
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34988559
JOIN condition is missing. Change the WHERE clause to JOIN condition.
UPDATE C 
set TIME_SENT_RANGE = CASE 
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC --ON c.TC_ID = ac.Country
ON c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34988569
if you really have the JOIN condition on Country and just commented out in your code, then uncomment that.
UPDATE C 
set TIME_SENT_RANGE = CASE 
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC ON c.TC_ID = ac.Country
AND c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988581
Or ?
UPDATE C 
set TIME_SENT_RANGE = CASE 
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC ON c.TC_ID = ac.Country
WHERE c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34988985
the code attached is working...yet returning no results...

after doing some tests....this portion is not working...
LEFT(c.TO_NUMBER,3) = ac.AREA_CODE

TO_NUMBER column contains data like this:
21285474125

AREA_CODE cotains data like this:
212

yet its now working for some reason....




UPDATE C 
set TIME_SENT_RANGE = CASE 
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC ON c.TC_ID = ac.Country
WHERE c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 664 total points
ID: 34989416
try this.
UPDATE C 
   SET TIME_SENT_RANGE = CASE 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Eastern%' THEN 'Nat_Eastern' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Central%' THEN 'Nat_Central' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Mountain%' THEN 'Nat_Mountain' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Pacific%' THEN 'Nat_Pacific' 
                         END 
  FROM Campaign C 
       JOIN Area_Code_Time_Zone AC 
         ON c.TC_ID = ac.Country 
 WHERE c.TC_ID = 'TC_ID_SD9FJG3K' 
       AND c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34989542
Sharath_123,

I got this error....

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.TO_NUMBER" could not be bound.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34990639
That errro says that the column 'TO_NUMBER' is not in the table with alias 'c' - It is Campaign - Verify it

Let us know
Raj
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 668 total points
ID: 34990640
My updated query - but check the issue I mentioned in my previous comment
UPDATE  C
SET     TIME_SENT_RANGE = CASE WHEN LEFT(LTRIM(CAST(C.TO_NUMBER AS VARCHAR)), 3) = LTRIM(CAST(ac.AREA_CODE AS VARCHAR))
                                    AND ac.TIME_ZONE LIKE '%Eastern%'
                               THEN 'Nat_Eastern'
                               WHEN LEFT(LTRIM(CAST(C.TO_NUMBER AS VARCHAR)), 3) = LTRIM(CAST(ac.AREA_CODE AS VARCHAR))
                                    AND ac.TIME_ZONE LIKE '%Central%'
                               THEN 'Nat_Central'
                               WHEN LEFT(LTRIM(CAST(C.TO_NUMBER AS VARCHAR)), 3) = LTRIM(CAST(ac.AREA_CODE AS VARCHAR))
                                    AND ac.TIME_ZONE LIKE '%Mountain%'
                               THEN 'Nat_Mountain'
                               WHEN LEFT(LTRIM(CAST(C.TO_NUMBER AS VARCHAR)), 3) = LTRIM(CAST(ac.AREA_CODE AS VARCHAR))
                                    AND ac.TIME_ZONE LIKE '%Pacific%'
                               THEN 'Nat_Pacific'
                          END
FROM    Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'

Open in new window


Raj
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34991742
update c
set time_sent_range
 = case when ac.time_zone like '%Eastern%' then 'Nat_eastern'
            when ac.time_zone like '%Central%' then 'Nat_Central'
            when ac.time_zone like '%Mountain%' then 'Nat_Mountain'
            when ac_time_zone like '%Pacific%' then 'Nat_Pacific'
            else NULL
            end
from campaign as c
inner join area_code_time_zone as ac
on ac.country=c.tc_id
where C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'
and  left(c.to_number,3)=ac.area_code
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994417
Raj...I ran yours...though it executed with no errors...


(0 row(s) affected)

...when 3 should have been...
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994444
Sharath...

ran yours....
(0 row(s) affected)

...when 3 should have been...
UPDATE C 
   SET TIME_SENT_RANGE = CASE 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Eastern%' THEN 'Nat_Eastern' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Central%' THEN 'Nat_Central' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Mountain%' THEN 'Nat_Mountain' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Pacific%' THEN 'Nat_Pacific' 
                         END 
  FROM Campaign C 
       JOIN Area_Code_Time_Zone AC 
         ON c.TC_ID = ac.Country 
 WHERE c.TC_ID = 'TC_ID_SD9FJG3K' 
       AND c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34994448
This could be because there is no records mathcing your WHERE conditon
Try this query and check whether any results ?
SELECT * FROM
FROM    Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'

Open in new window


Raj
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994479
I wanted to provided sample data...so we're all looking at the same thing...obviously two tables in play here

Area_Code_Time_Zone
area_code                                                time_zone
 201        US        New Jersey        Eastern        Nat_Eastern


campaign
to_number
8479222700
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994481
forgot to attach....
[To_Number] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

------------

CREATE TABLE [dbo].[Area_Code_Time_Zone](
	[AREA_CODE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[COUNTRY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[STATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TIME_ZONE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NATIONAL_CODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34994497
I meant for that update query to work - there should be some records matching that WHERE condition
So you are saying - no records, right ?
Raj
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994499
Raj...

ran this:
SELECT * FROM
FROM    Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'

got this:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34994583
Oops! It is a simple error - See two 'FROM' :)

Try this query and please let me know
SELECT * FROM
Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch' 

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35003927
Raj,

THis ran fine....yet pulled no results....just empty columns..


SELECT * FROM
Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'  
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35003964
That means you do not have matching records. What do you want to update?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35004013
GlobaLevel,

That is what I am saying - http:#34994497

There is no records to update based on that WHERE condition.

Raj
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35008264
Sharath..Raj...

got it...here is the issue....there are no common columns between these two tables...

I need
1) to get the area code from the TO_NUMBER column off the Campaign table...this where I just need the first 3 digits...so all numbers are listed as 7144568973..so I need to get the 714...
2) after I get just the areacode...I need to go to the area code table and compare to see if that area code(714) is found in the AREA_CODE column....get the value on the same row in the NATIONAL
3) if there is a match...then write the value from the NATIONAL_CODE column on the area_code table and write it to the TIME_SENT_RANGE on the campaign table...

any questions, please let me know...
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question