Skip to Content
0
Sep 23, 2014 at 02:47 AM

Derived table @prompt

23 Views

Hello,


I am trying to use @prompt in my derived table its not parsing even though it works as a univ filter. So I created a parameter 'new_param(select disticnt rpt_year_month). The object in Universe for rpt_year_month is 'Numeric'

error in derived table:

Conversion failed when converting the varchar value 'answer' to data type int.

Declare @rpt_year_month as int

set @rpt_year_month =@Prompt(new_param)

SELECT

a.group_name, a.primed_status as curr_prim_status, b.Primed_Status as prev_prim_status, a.EHR as curr_EHR, b.EHR as prev_EHR

from (select practice_id, group_name, primed_status, ehr, RPT_YEAR_MONTH from EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE

where RPT_YEAR_MONTH = @rpt_year_month) a inner join

(select practice_id, group_name, primed_status, ehr, RPT_YEAR_MONTH

from EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE

where EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE.RPT_YEAR_MONTH = convert(varchar(6), dateadd(MM,-3, convert(datetime, substring(cast(@rpt_year_month as varchar), 5,2) + '/' + '30'

+ '/'+ substring(cast(@rpt_year_month as varchar), 1,4))), 112)) b

on a.Group_Name = b.Group_Name