April 20, 2011 at 12:43 pm
I have two fields that I need to concatenate together. They are both character fields. I've tried using the "+" and I've tried using "||". Any suggestions.
April 20, 2011 at 1:05 pm
it should be col1 + col2
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2011 at 2:09 pm
This is my code. I am getting this error message: The data type of an operand of an arithmetic function or operation "+" is invalid.
SELECT QO01ACCIDENT_NO, QO01HIGHWAY_CLASS + QO01ACCIDENT_LOCATION_HWY AS Expr1
FROM xtech.TR10TBACCIDENT
WHERE (QO01ACCIDENT_YEAR = '2008' OR
QO01ACCIDENT_YEAR = '2009' OR
QO01ACCIDENT_YEAR = '2010') AND (QO01SUBMIT_AGENCY_ORI = 'XXXXXXXXX) OR
(QO01ACCIDENT_YEAR = '2008' OR
QO01ACCIDENT_YEAR = '2009' OR
QO01ACCIDENT_YEAR = '2010') AND (QO01MUNICIPALITY = 'XXXX')
April 20, 2011 at 2:28 pm
One of those fields must not be a character type field so you will have to cast it.
cast(QO01HIGHWAY_CLASS as (n)varchar(whatever length is appropriate here)) + cast(QO01ACCIDENT_LOCATION_HWY as (n)varchar(appropriate length))AS Expr1
Also your where clause can be made a lot simpler.
WHERE QO01ACCIDENT_YEAR in ('2008', '2009', '2010')
and (QO01SUBMIT_AGENCY_ORI = 'XXXXXXXXX' OR QO01MUNICIPALITY = 'XXXX')
What about the column QO01ACCIDENT_YEAR? Is it really a character datatype or is it numeric (int). If it is a numeric type you can take of the single ticks so it doesn't have to do an implicit conversion over and over.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 20, 2011 at 2:33 pm
One or both of the fields in the + (concatonation) is a numeric type at the table level. Wrap both of them in converts.
ie: CONVERT(VARCHAR(100), fld) + CONVERT( VARCHAR(100), fld2).
Or do what Sean mentioned above that he posted between me opening this and finally posting. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 20, 2011 at 2:47 pm
the chaps already helped out 😉
the complex and/or/and/or where clause
The suggested:
WHERE QO01ACCIDENT_YEAR in ('2008','2009','2010')
AND
(QO01SUBMIT_AGENCY_ORI = 'XXXXXXXXX'
OR
QO01MUNICIPALITY = 'XXXX')
provides the same predicates, but is easier to interpret by Average Joe
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2011 at 2:54 pm
Or do what Sean mentioned above that he posted between me opening this and finally posting. 🙂
If I could count how many times I have done that myself...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 21, 2011 at 7:46 am
I've tried both ways so I must be doing something wrong because I still get an error. First, I looked again at the table and both fields are character fields. Below are the fields from the table that I'm pulling data from.
Name Data Type Length Nullable
QO01ACCIDENT_LOCATION_HWYCHARACTER4Yes
QO01HIGHWAY_CLASS CHARACTER1Yes
Here's the latest code I tried.
SELECT QO01ACCIDENT_NO, CONVERT("VARCHAR"(1), QO01HIGHWAY_CLASS) + CONVERT("VARCHAR"(4), QO01ACCIDENT_LOCATION_HWY) AS Expr1
FROM xtech.TR10TBACCIDENT
WHERE (QO01ACCIDENT_YEAR = '2008' OR
QO01ACCIDENT_YEAR = '2009' OR
QO01ACCIDENT_YEAR = '2010') AND (QO01SUBMIT_AGENCY_ORI = 'XXXXXXXXX') OR
(QO01ACCIDENT_YEAR = '2008' OR
QO01ACCIDENT_YEAR = '2009' OR
QO01ACCIDENT_YEAR = '2010') AND (QO01MUNICIPALITY = 'XXXX')
April 21, 2011 at 11:30 am
db2mo (4/21/2011)
I've tried both ways so I must be doing something wrong because I still get an error. First, I looked again at the table and both fields are character fields. Below are the fields from the table that I'm pulling data from.Name Data Type Length Nullable
QO01ACCIDENT_LOCATION_HWYCHARACTER4Yes
QO01HIGHWAY_CLASS CHARACTER1Yes
Here's the latest code I tried.
SELECT QO01ACCIDENT_NO, CONVERT("VARCHAR"(1), QO01HIGHWAY_CLASS) + CONVERT("VARCHAR"(4), QO01ACCIDENT_LOCATION_HWY) AS Expr1FROM xtech.TR10TBACCIDENT
WHERE (QO01ACCIDENT_YEAR = '2008' OR
QO01ACCIDENT_YEAR = '2009' OR
QO01ACCIDENT_YEAR = '2010') AND (QO01SUBMIT_AGENCY_ORI = 'XXXXXXXXX') OR
(QO01ACCIDENT_YEAR = '2008' OR
QO01ACCIDENT_YEAR = '2009' OR
QO01ACCIDENT_YEAR = '2010') AND (QO01MUNICIPALITY = 'XXXX')
Not sure about the double quotes in there. convert(varchar(1), FieldName)
At any rate. What is the error?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 21, 2011 at 1:45 pm
Well see, that's the thing. When I type the code in, I don't put quotes around the VARCHAR. SSRS automatically puts them in when I run it.
The error I'm getting is
No authorized routine named "VARCHAR" of type "FUNCTION " having compatible arguments was found.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply