problem with CTE

• steal

  SSChasing Mays

  Points: 633

  More actions

  December 20, 2010 at 1:52 pm

  #244498

  I'm working with a table, let's call it PITA, with a varchar field that stores data values for many different form fields. The data type of what is in that varchar field for each record is determined by a link to a field type table.

  We are using CTE to filter data from table PITA for only a certain field code, therefore we have only the data for a certain field (and hence one data type). But when a WHERE clause is applied to the CTE results, a data conversion error is encountered. Why is the WHERE clause looking at data that is not in the CTE result set?

  The queries are quite complex with many joins, and I can not change the table design. I have developed a code snippet to try and illustrate the problem. Run the following code WITHOUT the last line of code (the WHERE clause) to see the results of the CTE. Then run it with the ending WHERE clause and see the error.

  declare @testFieldType table(fieldId int, code varchar(20))

  declare @testDocDataFieldValues table(fieldId int, fieldvalue varchar(50))

  insert into @testFieldType values (1, 'fieldTypeNumeric')

  insert into @testFieldType values (2, 'fieldTypeString')

  insert into @testDocDataFieldValues values (1, 500)

  insert into @testDocDataFieldValues values (2, 'StringValue_Ignore')

  insert into @testDocDataFieldValues values (1, 900)

  --select * from @testFieldType

  --select * from @testDocDataFieldValues

  ;with cte as

  (

  select ddf.FieldValue as ValuesKnownToBeNumeric

  from @testDocDataFieldValues ddf

  join @testFieldType ft on ddf.fieldId = ft.fieldId

  where ft.Code = 'fieldTypeNumeric'

  )

  select *

  from cte

  where (ValuesKnownToBeNumeric >= 400)

• Lamprey13

  SSCertifiable

  Points: 6190

  More actions

  December 20, 2010 at 2:06 pm

  #1264804

  You can't control when SQL will do the evaluation thus the pain of using an EAV table. Since you can't redesign the DB, one option is to use a CASE expression:declare @testFieldType table(fieldId int, code varchar(20))

  declare @testDocDataFieldValues table(fieldId int, fieldvalue varchar(50))

  insert into @testFieldType values (1, 'fieldTypeNumeric')

  insert into @testFieldType values (2, 'fieldTypeString')

  insert into @testDocDataFieldValues values (1, 500)

  insert into @testDocDataFieldValues values (2, 'StringValue_Ignore')

  insert into @testDocDataFieldValues values (1, 900)

  --select * from @testFieldType

  --select * from @testDocDataFieldValues

  ;with cte as

  (

  select CASE WHEN ft.Code = 'fieldTypeNumeric' THEN CAST(ddf.FieldValue AS INT) ELSE NULL END as ValuesKnownToBeNumeric

  from @testDocDataFieldValues ddf

  join @testFieldType ft on ddf.fieldId = ft.fieldId

  where ft.Code = 'fieldTypeNumeric'

  )

  select *

  from cte

  where (ValuesKnownToBeNumeric >= 400)

• The Dixie Flatline

  SSC Guru

  Points: 53353

  More actions

  December 20, 2010 at 4:26 pm

  #1264844

  Can you even change the EAV to use type SQL_VARIANT?

  declare @testFieldType table(fieldId int, code varchar(20))

  declare @testDocDataFieldValues table(fieldId int, fieldvalue SQL_VARIANT)

  insert into @testFieldType values (1, 'fieldTypeNumeric')

  insert into @testFieldType values (2, 'fieldTypeString')

  insert into @testDocDataFieldValues values (1, 500)

  insert into @testDocDataFieldValues values (2, 'StringValue_Ignore')

  insert into @testDocDataFieldValues values (1, 900)

  --select * from @testFieldType

  --select * from @testDocDataFieldValues

  ;with cte as

  (

  select ddf.FieldValue as ValuesKnownToBeNumeric

  from @testDocDataFieldValues ddf

  join @testFieldType ft on ddf.fieldId = ft.fieldId

  where ft.Code = 'fieldTypeNumeric'

  )

  select *

  from cte

  where (ValuesKnownToBeNumeric >= 400)

  Edited to add: I agree 100% that this is an AWFUL setup and will continue to cause you heartache in the long run.

  Edited to further add:

  It is a mistake to think that CTEs produce something like a temporary table that gets passed on. Like views, they are merely symbolic representations that get boiled down to a single execution plan. If you look at the execution plan for the query above, you will see that before the two tables are ever joined, a filter is applied against @testDocDataFieldValues. The only way to avoid this is to store the results of the CTE query in a working table such as a table variable or temporary table THEN query for values >=400 from that working table. Of course, doing all that may hurt performance. Again, you're REALLY better off finding a way to get away from a poor design.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  December 20, 2010 at 10:18 pm

  #1264885

  Though I do have to say that "PITA" is a remarkably accurate name for the table ... 😀

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• SQLRNNR

  SSC Guru

  Points: 281324

  More actions

  December 20, 2010 at 11:34 pm

  #1264896

  I'm not a fan of these kinds of PITAs either. Can you add more conditions to your where clause inside the CTE? When I add the fieldid column in the where clause, the error disappears for me.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  December 21, 2010 at 6:04 am

  #1265017

  I have to agree that this is a PITA, but if you are truly stuck with it and don't want to give up just because it's hard....then...

  declare @testFieldType table(fieldId int, code varchar(20))

  declare @testDocDataFieldValues table(fieldId int, fieldvalue varchar(50))

  insert into @testFieldType values (1, 'fieldTypeNumeric')

  insert into @testFieldType values (2, 'fieldTypeString')

  insert into @testDocDataFieldValues values (1, 500)

  insert into @testDocDataFieldValues values (2, 'StringValue_Ignore')

  insert into @testDocDataFieldValues values (1, 900)

  --select * from @testFieldType

  --select * from @testDocDataFieldValues

  ;with cte as

  (

  select CASE WHEN ft.Code = 'fieldTypeNumeric' then ddf.FieldValue else '' end as ValuesKnownToBeNumeric

  from @testDocDataFieldValues ddf

  join @testFieldType ft on ddf.fieldId = ft.fieldId

  where ft.Code = 'fieldTypeNumeric'

  )

  select *

  from cte

  where (ValuesKnownToBeNumeric >= 400)

  This will prevent the errors but incurs quite a performance hit...

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]