Home No Records Returned after using dplyr and paste0 to summarize and collate results
Reply: 0

No Records Returned after using dplyr and paste0 to summarize and collate results

user1867
1#
user1867 Published in June 20, 2018, 7:26 am

I've used dplyr and paste0 to summarize data from a TSQL query with the intent to output mean and standard deviation for each column, c1 - c8. To this end, I created the following stored procedure:

Declare @sStudy varchar(50)
Set @sStudy = Convert(Varchar(50),@StudyID)
Declare @inquery nvarchar(max) = N'Select
        c.StudyID, c.RespID, c.ProductNumber, c.ProductSequence, c.BottomScaleValue, 
        c.BottomScaleAnchor, c.TopScaleValue, c.TopScaleAnchor, c.StudyDate,
        c.DayOfWeek, c.A, c.B, c.C, c.D, c.E, c.F,
        c.DependentVarYN, c.VariableAttributeID, c.VarAttributeName, c.[1] as c1, 
        c.[2] as c2, c.[3] as c3, c.[4] as c4, c.[5] as c5, c.[6] as c6, c.[7] as c7, c.[8] as c8
        from ClosedStudyResponses c
        --Sensory Value Attributes only for mean and standard deviation analytics.
        where VariableAttributeID = 1
        and c.StudyID =' +@sStudy ;

BEGIN TRY
        --Insert into CodeMeans
        exec sp_execute_external_script
        @language = N'R',
        @script = N'
        library(dplyr)
        codemeans <- function(StudyID){
            res <- InputDataSet %>%
                group_by (StudyID, ProductNumber) %>%
                summarise_all(.funs=c(mean, sd)) %>%
                setNames(c("StudyID","ProductNumber",
                paste0("c",1:8, "_mean"),
                paste0("c",1:8, "_sd")))
            df <- data.frame(res)
            }
            ',
@input_data_1 = @inquery,
@output_data_1_name = N'df',
@params = N'@StudyID int',
@StudyID = @StudyID

When I execute the stored procedure by providing a valid StudyID, the query finishes but contains no data despite the fact that the source table contains data in some if not most of the columns.

How do I modify the above to ensure data is returned?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.32999 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO