//i had shared a piece of code where i am getting the error. here i want to pass the date range and get the data from the query in my data frame
output$DateRange <- renderText(
if(USER$Logged==TRUE){
//getting the input date ranges from here
validate(
need(as.character(Sys.Date()) >= input$dates[1],
"Start Date should be less than or equal to Current Date"
)
)
validate(
need(input$dates[2] <= as.character(Sys.Date()) && input$dates[2] >= input$dates[1], "End Date should be greater than Start date and less than Current Date"
))
})
//i am passing date range from here in my query but i am getting a error while passing datesetFirst and datesetSecond
datesetFirst <- reactive(input$dates[1])
datesetSecond <- reactive(input$dates[2])
if (!is.null(datesetFirst) && !is.null(datesetSecond))
{
// this is the query i want to execute after passing the date range from here i want to get the data on the basis of input range
resultset =paste("SELECT SET2.AREA_NAME,SET2.PROD_NM,
SET2.TherapeuticClass,set2.TOTAL,
ROUND(((set2.TOTAL/SET3.TOTAL)*100),2) as SHARE
FROM (select retail_store_area_wise.area_name AS AREA_NAME,
set1.PROD_NM AS PROD_NM,sum(set1.TOTAL) AS TOTAL,
set1.TH_CLASS_1 AS TH_1,
set1.TH_CLASS_2 AS TH_2,
set1.TH_CLASS_3 AS TH_3,
set1.TH_CLASS_4 AS TH_4,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) ) as TherapeuticClass
from
(select
retail_store_prod.TH_CLASS_4 as TH_CLASS_4,
retail_store_prod.TH_CLASS_3 as TH_CLASS_3,
retail_store_prod.TH_CLASS_2 as TH_CLASS_2,
retail_store_prod.TH_CLASS_1 as TH_CLASS_1,
retail_store_prod.store_id as store_id ,
retail_store.str_nm,
retail_Str_sales_detail.prod_nm as PROD_NM,
round(sum (retail_Str_sales_detail.total),2) AS TOTAL
from
retail_str_sales_detail ,
retail_store_prod,retail_store
where
retail_store_prod.prod_nm = retail_str_sales_detail.prod_nm and
retail_store_prod.store_id=retail_str_sales_detail.store_id and
retail_store.store_id = retail_store_prod.store_id AND
retail_str_sales_detail.SALE_DATE BETWEEN //while passing the date range here i am getting the error**'",datesetFirst,"'** AND **'",datesetSecond,"'**
AND retail_store_prod.TH_CLASS_4 != 'NULL'
AND retail_store_prod.TH_CLASS_3 != 'NULL'
AND retail_store_prod.TH_CLASS_2 != 'NULL'
AND retail_store_prod.TH_CLASS_1 != 'NULL'
AND retail_store_prod.TH_CLASS_4 != ''
AND retail_store_prod.TH_CLASS_3 != ''
AND retail_store_prod.TH_CLASS_2 != ''
AND retail_store_prod.TH_CLASS_1 != ''
GROUP BY
retail_store_prod.TH_CLASS_4 ,
retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,
retail_store_prod.TH_CLASS_1 ,
retail_Str_sales_detail.prod_nm ,retail_store.str_nm,
retail_store_prod.store_id
order by retail_Str_sales_detail.prod_nm,
retail_store_prod.TH_CLASS_4 ,
retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,
retail_store_prod.TH_CLASS_1 ,retail_store.str_nm,
round(sum (retail_Str_sales_detail.total),2) desc) as set1, retail_store_area_wise
where set1.store_id = retail_store_area_wise.store_id
group by set1.PROD_NM,
set1.TH_CLASS_1,
set1.TH_CLASS_2,
set1.TH_CLASS_3,
set1.TH_CLASS_4,
retail_store_area_wise.area_name,CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) )
order by retail_store_area_wise.area_name,set1.PROD_NM) as SET2
FULL OUTER JOIN
(select retail_store_area_wise.area_name AS AREA_NAME,sum(set1.TOTAL) AS TOTAL,
set1.TH_CLASS_1 AS TH_1,
set1.TH_CLASS_2 AS TH_2,
set1.TH_CLASS_3 AS TH_3,
set1.TH_CLASS_4 AS TH_4,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) ) as TherapeuticClass
from
(select
retail_store_prod.TH_CLASS_4 as TH_CLASS_4,
retail_store_prod.TH_CLASS_3 as TH_CLASS_3,
retail_store_prod.TH_CLASS_2 as TH_CLASS_2,
retail_store_prod.TH_CLASS_1 as TH_CLASS_1,
retail_store_prod.store_id as store_id ,
retail_store.str_nm,
retail_Str_sales_detail.prod_nm as PROD_NM,
round(sum (retail_Str_sales_detail.total),2) AS TOTAL
from
retail_str_sales_detail ,
retail_store_prod,retail_store
where
retail_store_prod.prod_nm = retail_str_sales_detail.prod_nm and
retail_store_prod.store_id=retail_str_sales_detail.store_id and
retail_store.store_id = retail_store_prod.store_id AND
retail_str_sales_detail.SALE_DATE BETWEEN '",datesetFirst,"' AND '",datesetSecond,"'
AND retail_store_prod.TH_CLASS_4 != 'NULL'
AND retail_store_prod.TH_CLASS_3 != 'NULL'
AND retail_store_prod.TH_CLASS_2 != 'NULL'
AND retail_store_prod.TH_CLASS_1 != 'NULL'
AND retail_store_prod.TH_CLASS_4 != ''
AND retail_store_prod.TH_CLASS_3 != ''
AND retail_store_prod.TH_CLASS_2 != ''
AND retail_store_prod.TH_CLASS_1 != ''
GROUP BY
retail_store_prod.TH_CLASS_4 ,
retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,
retail_store_prod.TH_CLASS_1 ,
retail_Str_sales_detail.prod_nm ,retail_store.str_nm,
retail_store_prod.store_id
order by retail_Str_sales_detail.prod_nm,
retail_store_prod.TH_CLASS_4 ,
retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,
retail_store_prod.TH_CLASS_1 ,retail_store.str_nm,
round(sum (retail_Str_sales_detail.total),2) desc) as set1, retail_store_area_wise
where set1.store_id = retail_store_area_wise.store_id group by
set1.TH_CLASS_1,
set1.TH_CLASS_2,
set1.TH_CLASS_3,
set1.TH_CLASS_4,
retail_store_area_wise.area_name,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) )
order by retail_store_area_wise.area_name) AS SET3
ON SET3.AREA_NAME= SET2.AREA_NAME AND
SET3.TH_1= SET2.TH_1 AND
SET3.TH_2 = SET2.TH_2 AND
SET3.TH_3 = SET2.TH_3 AND
SET3.TH_4 = SET2.TH_4");
querytherapeutic1= reactive({dbGetQuery(conn,resultset)})
biztherapeuticproduct1= data.frame(
Area= querytherapeutic1$AREA_NAME,
THERAPEUTIC_CLASS= querytherapeutic1$THERAPEUTICCLASS,
Market_Share= as.numeric(as.character(querytherapeutic1$SHARE)),
Product= querytherapeutic1$PROD_NM,
Total = as.numeric(as.character(querytherapeutic1$TOTAL)),
stringsAsFactors = FALSE
)}
Your error message ist from this part of the code
datesetFirst <- reactive(input$dates[1])
datesetSecond <- reactive(input$dates[2])
if (!is.null(datesetFirst) && !is.null(datesetSecond))
reactive returns a function not a value so you need to change your code to this
datesetFirst <- reactive(input$dates[1])
datesetSecond <- reactive(input$dates[2])
if (!is.null(datesetFirst()) && !is.null(datesetSecond()))
and of course in the query aswell
"retail_str_sales_detail.SALE_DATE BETWEEN '",datesetFirst(),"' AND '",datesetSecond(),"'