Variables using Select
-
3 part Question
1) Can't figure out why I am getting an error with the following script:
ERROR MESSAGE:
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'SET'.
2) Is there a way to Concatenate the values from two columns in a table and declare it as one variable?
3) Whats wrong with this code, grant it I have not tackled the 2nd question yet in the following code.
SQL CODE:
use MI50_Source
go
if exists
(select name from sysobjects where name = 'BCBSAltPCPTemp')
drop table dbo.BCBSAltPCPTemp
go
create table dbo.BCBSAltPCPTemp
(
Record_Count varchar(250)
)
go
-- Declare the value from the TRA record
DECLARE @Trailer1 varchar(250), @Trailer2 varchar(250)
SET @Trailer1 = (SELECT Subscriber FROM BCBSAltPCP WHERE (Subscriber LIKE 'TRA%')
SET @Trailer2 = (SELECT Relation FROM BCBSAltPCP WHERE (Subscriber LIKE 'TRA%')
)
IF Exists(SELECT Subscriber FROM BCBSAltPCP WHERE Subscriber LIKE 'TRA%')
BEGIN
PRINT 'The trailer File does Exist so lets grab it:'
PRINT ' '
INSERT INTO BCBSAltPCPTemp
(Record_Count)
VALUES (@Trailer1)
END
ELSE
PRINT 'No trailer was found.'
Select *
FROM BCBSAltPCPTemp
-
3 part Answer
1) Cant figure out why I am getting an error with the following script:
See 3
2) Is there a way to Concatenate the values from two columns in a table and declare it as one variable?
Yes
Ex.
SELECT Subscriber + Relation AS CombinedCol FROM BCBSAltPCP WHERE (Subscriber LIKE 'TRA%')
3) Whats wrong with this code, grant it I have not tackled the 2nd question yet in the following code.
SET @Trailer1 = (SELECT Subscriber FROM BCBSAltPCP WHERE (Subscriber LIKE 'TRA%')
SET @Trailer2 = (SELECT Relation FROM BCBSAltPCP WHERE (Subscriber LIKE 'TRA%')
) --<<<<<<<<--You have an extra ) right here.
IF Exists(SELECT Subscriber FROM BCBSAltPCP WHERE Subscriber LIKE 'TRA%')
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Caught that error just after I posted, and the Concatenation works. Thanks
Let me bounce another question off ya.
The results returned for that query give me one record that looks like this:
Trailer_Count
------------
TRAILER00001608200
What I now want to accomplish is to grab characters 12 through 16 only = 16082
How could I write this, any ideas?
-
Use the substring function i.e. substring(columnname, 12, 16) first number indicates starting position and second number indicates stop position. BOL has a really good example there as well.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
David is right, except 1 error.
SUBSTRING ( expression , start , length )
last parameter is length not ending position so
substring(TRAILER_COUNT, 12, 5)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Thanks I relized that after the results were a little wacked. Thanks Again.
-
My bad! That is what I get for typing before going to a meeting. Sorry for misleading you!
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply