Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

update issue in ms sql server 2005

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
GlobaLevel
Asked:
GlobaLevel
  • 12
  • 10
  • 4
  • +2
3 Solutions
 
sammySeltzerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
sammySeltzerCommented:
actually that was incorrect.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sammySeltzerCommented:
I meant my post, sorry
0
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
Check your requirement. What conditions to satisfy. Give it properly
0
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
What is the error message now ?
Raj
0
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
LowfatspreadCommented:
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
 
GlobaLevelAuthor Commented:
Raj...I ran yours...though it executed with no errors...


(0 row(s) affected)

...when 3 should have been...
0
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
SharathData EngineerCommented:
That means you do not have matching records. What do you want to update?
0
 
Rajkumar GsSoftware EngineerCommented:
GlobaLevel,

That is what I am saying - http:#34994497

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

Raj
0
 
GlobaLevelAuthor Commented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 12
  • 10
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now