May 1, 2002 at 7:22 am
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
May 1, 2002 at 7:29 am
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)
May 1, 2002 at 7:48 am
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?
May 1, 2002 at 7:54 am
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
May 1, 2002 at 8:23 am
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)
May 1, 2002 at 9:07 am
Thanks I relized that after the results were a little wacked. Thanks Again.
May 1, 2002 at 10:19 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy