June 15, 2010 at 1:34 pm
Hello fellow SQL juggernauts. I am currently stuck on a very troublesome problem. Here is my code that works… I just don’t like it…
SELECT DISTINCT
pt.FIRST_NAME+ ' '+ pt.LAST_NAME AS 'Patient Name',
pt.PATIENT_ID AS 'Patient ID',
rxo.RX_NUMBER AS 'Rx Number',
rxf.REFILL_NUMBER AS 'Refill Number',
rxo.DESCRIPTION AS 'Drug Description',
rxf.DOSES AS '# of Doses',
rxf.DAYS AS '# of Disp Days',
rxf.SERVICE_START AS 'Service Start',
rxf.SERVICE_END AS 'Service End',
rxf.REFILL_DUE AS 'Refill Due Date',
rxf.SCHED_DELIVERY AS 'Scheduled Del.',
rxf.CREATE_USER AS 'Entered by:'
FROM PATIENT pt
JOIN RX_ORDER rxo ON rxo.PATIENT_ID = pt.PATIENT_ID AND (rxo.DATE_WRITTEN >= '2010-01-01 00:00:00.000')
JOIN RX_FILL rxf ON rxf.RX_ORDER_SYS_ID = rxo.SYS_ID
Like I said, it works... I really don't want the RX Number and Rx Fill information in two separate columns.
rxo.RX_NUMBER AS 'Rx Number',
rxf.REFILL_NUMBER AS 'Refill Number',
What I was hoping for was...
rxo.RX_NUMBER + ' - ' + rxf.REFILL_NUMBER as 'Rx#/Refill#' ,
To show #### - ##, but it does not. It only shows the rxo.RX_NUMBER
Can you help me understand how to concatenate these two fields from separate tables? :hehe:
June 15, 2010 at 1:41 pm
Can't see the datatypes from the code, but if either are int, you'll need to wrap them in a cast or convert before you use string concatenation.
June 15, 2010 at 1:47 pm
Your code looks correct, assuming rxo.RX_NUMBER and rxf.REFILL_NUMBER are both character values. Otherwise SQL Server will assume that you're trying to do some math. So, the result should not be rxo.RX_NUMBER but the sum of both columns. If the two cols are integer data type you need to cast it to character in order to get the expected result. Here's an example:
SET @a=10
SET @b-2=2
SELECT @a + '-' + @b-2 -- returns 12
SELECT CAST(@a AS VARCHAR(11)) + '-' + CAST(@b AS VARCHAR(11)) -- returns 10-2
June 15, 2010 at 1:51 pm
A little confused, but starting down the right path I think. The Rx_Number is an INT and refill_number is a smallint
June 15, 2010 at 2:00 pm
The '+' is used as a mathematical operator and a string concatenation operator. If the datatypes of the arguments on both sides are numerical (int, smallint, numeric, float, etc) the execution engine performs math on the arguments. If the dayatypes are strings (char, varchar), the execution engine slaps them together byte by byte. If they're of mixed types, the engine doesn't really know what to do. I'm a little surprised that your original code doesn't produce an error of some sort.
Casting the numerical values into character strings will allow the engine to work with the '+' as a concatenation operator.
June 15, 2010 at 2:09 pm
the information that LUTZ gave worked to some extennt. I added the...
DECLARE @RXNUM INT, @RXFILL INT
SET @RXNUM = 10
SET @RXFILL = 2
Then I added a select statement later on dow the road...
SELECT DISTINCT
pt.FIRST_NAME+ ' '+ pt.LAST_NAME AS'Patient Name',
pt.PATIENT_ID AS 'Patient ID',
(SELECT CAST(@RXNUM AS VARCHAR(11)) +'-'+ CAST(@RXFILL AS VARCHAR(11))),
rxo.DESCRIPTION AS 'Drug Description',
rxf.DOSES AS '# of Doses',
rxf.DAYS AS '# of Disp Days',
I got the Rx number and Refill number to populate; however, my rx number and refill number are not 10-2
There are thousands of rx numbers in the table.
I tried something off the wall, but it failed.
DECLARE @RXNUM INT, @RXFILL INT
SET @RXNUM = rxo.RX_NUMBER
SET @RXFILL = rxf.REFILL_NUMBER
That resulted in...
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "rxo.RX_NUMBER" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "rxf.REFILL_NUMBER" could not be bound.
I'm not trying to be dense, but I'm really having a hard time wrapping my fingure around this...
THANK YOU so far for the help...
June 15, 2010 at 2:15 pm
give this a try:
SELECT DISTINCT
pt.FIRST_NAME+ ' '+ pt.LAST_NAME AS 'Patient Name',
pt.PATIENT_ID AS 'Patient ID',
rxo.RX_NUMBER AS 'Rx Number',
rxf.REFILL_NUMBER AS 'Refill Number',
cast(varchar(11),rxo.RX_NUMBER) + '-' +
cast(varchar(10),rxf.REFILL_NUMBER) AS 'RX and Refill',
rxo.DESCRIPTION AS 'Drug Description',
rxf.DOSES AS '# of Doses',
rxf.DAYS AS '# of Disp Days',
rxf.SERVICE_START AS 'Service Start',
rxf.SERVICE_END AS 'Service End',
rxf.REFILL_DUE AS 'Refill Due Date',
rxf.SCHED_DELIVERY AS 'Scheduled Del.',
rxf.CREATE_USER AS 'Entered by:'
FROM PATIENT pt
JOIN RX_ORDER rxo ON rxo.PATIENT_ID = pt.PATIENT_ID AND (rxo.DATE_WRITTEN >= '2010-01-01 00:00:00.000')
JOIN RX_FILL rxf ON rxf.RX_ORDER_SYS_ID = rxo.SYS_ID
June 15, 2010 at 2:20 pm
David Webb-200187 (6/15/2010)
The '+' is used as a mathematical operator and a string concatenation operator. If the datatypes of the arguments on both sides are numerical (int, smallint, numeric, float, etc) the execution engine performs math on the arguments. If the dayatypes are strings (char, varchar), the execution engine slaps them together byte by byte. If they're of mixed types, the engine doesn't really know what to do. I'm a little surprised that your original code doesn't produce an error of some sort.Casting the numerical values into character strings will allow the engine to work with the '+' as a concatenation operator.
The bold statement needs to be revised:
SQL Server will try to convert all values to the data type with the highest precedence (see BOL for a detailed list). Based on the given scenario SQL Server will try to change all values to integer. Surprisingly, SQL Server seems to change '-' to -0 which equals zero and therefore doesn't influence the result at all (it works with the '+' sign, too).
As long as all values involved in the equation can be converted into an integer value, the equation will be valid. Otherwise it'll throw an conversion error.
So, the SQL Server engine (hopefully) always "know" what to do. 😉
June 15, 2010 at 2:24 pm
Msg 195, Level 15, State 10, Line 6
'varchar' is not a recognized built-in function name.
This error doesn't make sense at all...
Now I changed the CAST to CONVERT and we're all good... Thank you very much!
:-):-D:w00t::hehe:
June 15, 2010 at 2:26 pm
Yes, Lutz is correct (as usual). I didn't expect the implicit conversion of '-' to be a zero, but that's what happens. The execution engine is smarter than I am, thank goodness. 🙂
July 17, 2010 at 2:26 am
The following code was the cause of the error:
SELECT
...
cast(varchar(11),rxo.RX_NUMBER) + '-' +
cast(varchar(10),rxf.REFILL_NUMBER) AS 'RX and Refill',
...
Syntax for CAST:
CAST (expression AS data_type [ (length ) ] )
Syntax for CONVERT:
CONVERT (data_type [ (length ) ] ,expression [ ,style ] )
So the correct expression is :
CAST(rxo.RX_NUMBER AS varchar(11)) + ...
--or--
CONVERT(varchar(11),rxo.RX_NUMBER) + ...
This BOL article explains the details and is a must-read
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Best regards,
Hrvoje
Hrvoje Piasevoli
July 17, 2010 at 9:10 am
Thank you for the post. That is what I ended up doing in the long run was CONVERT both fields to a varchar and concatenating them. Thank you again... 🙂
April 6, 2011 at 2:30 am
it should be
select cast(fill as nvarchar(20))+'-'+ cast(number as nvarchar(20)) from tablename
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply