Conversion error

Question

Hi All,

I have the following statement

CASE

CAST( CASE FQ.iDivision WHEN 71 THEN '644A1D01-C524-DE11-91AE-0002A53F9555'

ELSE '644B1D01-C524-DE11-91AE-0002A53F9666' END as varchar(36))

WHEN 128 THEN '644E1D01-C524-DE11-91AE-0002A53F9999'

ELSE '644D1D01-C524-DE11-91AE-0002A53F9888'

END

AS uidBusinessAreaId

iStore WHEN 139 THEN

It is throwing me this error:

Operand type clash: uniqueidentifier is incompatible with int                                                                                                                                                   

---

hsbal

My apologies, I copied fragment query. The columns iStore and iDivision are int types.

CASE

iStore WHEN 139 THEN

CASE FQ.iDivision WHEN 71 THEN '644A1D01-C524-DE11-91AE-0002A53F9555'

ELSE '644B1D01-C524-DE11-91AE-0002A53F9666' END

WHEN 128 THEN '644E1D01-C524-DE11-91AE-0002A53F9999'

ELSE '644D1D01-C524-DE11-91AE-0002A53F9888'

END

AS uidBusinessAreaId

Answer 1

What is the exact full statement and what are the types of iDivision column and iStore column ?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

check the datatypes of the  source and target columns
Looks like you are comparing  guid and int 

Answer 3

You have posted a fragment of a query (and not a well-formed one at that) and it isn't possible to  know with any certainty that the part you posted is related to the error message.  The message should be self-explanatory; you are attempting to convert a value of type uniqueidentifier to integer. 

The fragment you posted is written with a number of datatype conversions, only one of which is explicit.  First up is the innermost case expression that compares FQ.iDivision to the integer 71.  This might be the cause.  You then cast the result of that expression to varchar(36) - and compare that value (varchar(36)) to another integer.  And the varchar values you use are obviously GUIDs.  And the name you give to this derived column also suggests that the datatype is uniqueidentifier. 

Answer 4

Hi JR,

yes I am hard coding unique-identifier values in case based on values of int columns.

Thanks,

---

hsbal

Answer 5

Your simple CASE syntax is wrong. Check the syntax here:

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Answer 6

If everything you have said is correct, the error is somewhere else in your query.  If iDivision and iStore are int's that expression will work perfectly fine, for example

Declare @Test Table(iStore int, iDivision int);
Insert @Test(iStore, iDivision) Values (139, 10), (139, 71), (128, 5), (5, 5);
SELECT iStore, IDivision,
CASE

iStore WHEN 139 THEN

CASE FQ.iDivision WHEN 71 THEN '644A1D01-C524-DE11-91AE-0002A53F9555'

ELSE '644B1D01-C524-DE11-91AE-0002A53F9666' END

WHEN 128 THEN '644E1D01-C524-DE11-91AE-0002A53F9999'

ELSE '644D1D01-C524-DE11-91AE-0002A53F9888'

END

AS uidBusinessAreaId
FROM @Test FQ;

Best way for you to help us find your problem is for you to give us something like the above - that is a query that we can run, but which will duplicate your problem.  If we have that, I'm sure we can quickly help you.

Tom

Answer 7

try :

CASE 

WHEN istore = 139 THEN (CASE FQ.iDivision WHEN 71 THEN '644A1D01-C524-DE11-91AE-0002A53F9555'

ELSE '644B1D01-C524-DE11-91AE-0002A53F9666' END)

WHEN istore =128 THEN '644E1D01-C524-DE11-91AE-0002A53F9999'

ELSE '644D1D01-C524-DE11-91AE-0002A53F9888'

END 

Answer 8

I suspect that this part of the query is used in the INSERT statement. But of course, we need to see the whole query to identify the error.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

The CASE syntax is OK.  It's just a simple CASE statement with another simple CASE statement embedded within the first.

Tom

Answer 10

select    iQueue as iQueueID,iStore as iStoreid,
CASE iStore WHEN 139 THEN 
					 CASE FQ.iDivision WHEN 71 THEN '644A1D01-C524-DE11-91AE-0002A53F9555'
												ELSE '644B1D01-C524-DE11-91AE-0002A53F9666' END 
			WHEN 128 THEN '644E1D01-C524-DE11-91AE-0002A53F9999'
			ELSE '644D1D01-C524-DE11-91AE-0002A53F9888'
END   AS uidBusinessAreaId,FQ.iDivision,
iReservation as iReservationId,dtReservationCreated as dtReserEnterDt,dtEnteredQueue as dtEnteredQueue,dtQueueClosed as dtClosedQueue,iQueuedBy as vcQueueOpenedBy,
iClosedBy as vcQueueClosedBy,1 as siStatus,'BC00528'as vcRcdCreateUser,'BC00528'as vcRcdUpdtUser,2112 as iRcdCreateApp,
2112 as iRcdUpdtApp,GETDATE()as dtRcdCreateDateTime,GETDATE()as dtRcdUpdtDateTime,D.uidDivisionId as uidDivisionIdFk,
bf.uidBacklineFunctionsId as uidBacklineFunctionidFk,b.uidBackILineId as uidBacklineIdFk
from dbo.tbl_DAT_FILQ as FQ
INNER JOIN dbo.Division AS D
ON FQ.iDivision=D.iDivisionId
INNER JOIN dbo.BackLine_Functions AS BF
ON FQ.iQueue=BF.iQueueId AND FQ.iStore=BF.uidBusinessAreaIdFk
INNER JOIN dbo.Associate AS A
ON FQ.iClosedBy=A.vcAccountNumber
INNER JOIN dbo.BackLine AS B
ON A.uidAssociateID=B.uidAssociatelnBacklineIdFk 
AND SUBSTRING(CONVERT(varchar(10),FQ.dtQueueClosed,110),1,10)=substring(CONVERT(varchar(10),B.dtStart,110),1,10)
AND BF.uidBacklineFunctionsId=B.uidBacklineFunctionIdFk
AND A.uidAssociateID=B.uidAssociatelnBacklineIdFk
AND D.uidDivisionId=B.uidDivisionIdFk

Hi Tom,

Attached is the full code. The above query that you presented works well on my end. I think there is some issue with this query. So I have posted it in full.

Thanks in advance, 

---

hsbal

Answer 11

This JOIN

AND FQ.iStore=BF.uidBusinessAreaIdFk

looks wrong to me. First field is integer and the second, I suppose, is uniqueidentifier.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 12

yes, you are correct. But I am not figuring out its fix. Explit conversion from either of them is not allowed.

Thanks,

---

hsbal

Answer 13

There is no way to join integer and uniqueidentifier. You can cast both of them to varchar, but you will not get a match this way.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 14

You are comparing apples and oranges, and that will never work out. If I am to guess, you are making a leap between two tables where there is a gap, and you need some glue in between, for instance a table that maps the integer and the guid to each other. Since I don't know your tables, I can't say where this would be.

---

Erland Sommarskog, SQL Server MVP, [e-mail]