GlobaLevel
asked on
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'
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
actually that was incorrect.
I meant my post, sorry
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'
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'
Check your requirement. What conditions to satisfy. Give it properly
There are many issues found with your query
1.
2.
Raj
1.
LEFT(c.TO_NUMBER,3
is wrong. It should be closed likeLEFT(c.TO_NUMBER,3)
2.
(LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Eastern%'
This is not valid CASE statement. It should be splitted off as I mentioned before or as you want.3.
END is missing for CASE statement.Raj
ASKER
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'
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'
What is the error message now ?
Raj
Raj
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'
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'
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'
ASKER
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....
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'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I got this error....
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.TO_NUMBER" could not be bound.
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
Let us know
Raj
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.are a_code
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.are
ASKER
Raj...I ran yours...though it executed with no errors...
(0 row(s) affected)
...when 3 should have been...
(0 row(s) affected)
...when 3 should have been...
ASKER
Sharath...
ran yours....
(0 row(s) affected)
...when 3 should have been...
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'
This could be because there is no records mathcing your WHERE conditon
Try this query and check whether any results ?
Raj
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'
Raj
ASKER
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
Area_Code_Time_Zone
area_code time_zone
201 US New Jersey Eastern Nat_Eastern
campaign
to_number
8479222700
ASKER
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
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
So you are saying - no records, right ?
Raj
ASKER
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'.
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'.
Oops! It is a simple error - See two 'FROM' :)
Try this query and please let me know
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'
ASKER
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'
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'
That means you do not have matching records. What do you want to update?
GlobaLevel,
That is what I am saying - http:#34994497
There is no records to update based on that WHERE condition.
Raj
That is what I am saying - http:#34994497
There is no records to update based on that WHERE condition.
Raj
ASKER
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...
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...
Open in new window