469,344 Members | 6,383 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

sql2005/BIDS: getting error 102 with CTE in dynamic query expression

Hi,

Using BIDS / Report Designer with SSRS / SQL2005(SP2) and for one dataset, I'm trying to make a CTE into a 'dynamic' query by wrapping the accessor query in an Iif() function. Here it is:

Expand|Select|Wrap|Line Numbers
  1. = "with fpy_insp1 as " &
  2. "(  " &
  3. "select count(distinct(alias)) as fmacount " &
  4. "from e_cs_mfdunit_history " &
  5. "where (product_id like '103%' or product_id like '106%' or product_id like '108%') " &
  6. "and updated_date >= @start_date and updated_date < DateAdd("Day",1,@end_date) " &
  7. "and (current_step in ( 'ARCVAULT.2U.600.PREINSPECTION.PREP','ARCVAULT.2U.600.INTERNAL.INSPECTION') " &
  8. "or current_step in ('ARCVAULT.2U.EXTERNAL.INSPECTION'))  " &
  9. "and substring(alias,1,2) = '2B' " &
  10. "and (exception_step like 'FMA.REWORK' or exception_step like 'FMA.PROCESS') " &
  11. "), " &
  12.  
  13. "fpy_insp2 as ( " &
  14. "select count(distinct(alias)) as allcount " &
  15. "from e_cs_mfdunit_history " &
  16. "where (product_id like '103%' or product_id like '106%' or product_id like '108%') " &
  17. "and updated_date >= @start_date and updated_date < DateAdd("Day",1,@end_date) " &
  18. "and (current_step in ( 'ARCVAULT.2U.600.PREINSPECTION.PREP','ARCVAULT.2U.600.INTERNAL.INSPECTION') " &
  19. "or current_step in ('ARCVAULT.2U.EXTERNAL.INSPECTION'))  " &
  20. "and substring(alias,1,2) = '2B' " &
  21. "and exception_step is null " &
  22. ") "  &
  23.  
  24. Iif(allcount = 0, "select 0", "select (1 - (select cast(fmacount as decimal) from fpy_insp1) " &  "/ (select cast(allcount as decimal) from fpy_insp2)) * 100  as fpy_insp)"
  25.  
Here are the error details:

Incorrect syntax near '='. (Microsoft Report Designer)
Incorrect syntax near '='. (.Net SqlClient Data Provider)

Server Name: (local)
Error Number: 102
Severity: 15
State: 1
Line Number: 1

Never had trouble doing this on a simple select query before. Anyone know if this is specific to using CTEs?

Thanks!
e
May 8 '07 #1
0 1323

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by John Øllgård Jensen | last post: by
2 posts views Thread by srusskinyon | last post: by
1 post views Thread by =?ISO-8859-1?Q?Brian_Sprog=F8?= | last post: by
reply views Thread by =?Utf-8?B?U1VOTlk=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.