AWQMS_Data_Cont function sending an non-boolean expression to SQL query.

DEQabrits commented 5 months ago

I'm trying to use the AWQMS_Data_Cont() function in my ShinyNPDES_AWQMS app. and I'm getting the following error:

Warning: Error in dplyr::collect: Failed to collect lazy table. Caused by error: ! nanodbc/nanodbc.cpp:1710: 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near ')'.

At first I thought it was because the shiny app (code snippet below) was passing something that the SQL query couldn't read, but after experimenting with different data formats (e.g. transforming the input variables into .data and !!sym) I was wondering if there might be something going on in the function itself.

dat<-AWQMS_Data_Cont(startdate=toString(sprintf("%s",input$startd)),enddate=toString(sprintf("%s",input$endd)),
                    MLocID=c(input$monlocs),
                    Char_Name=rchar,
                    OrganizationID=c(input$orgs), 
                    HUC8_Name=c(input$huc8_nms), 
                    AU_ID=c(input$AUID), 
                    Result_Status=c("Accepted","Final","Validated","Preliminary","Provisional"))

I tried performing the following query just using the function and got the same error:

dat<-AWQMS_Data_Cont(OrganizationID = "CITY_STHELENS/BC(NOSTORETID)" , Result_Status=c("Accepted","Final","Validated","Preliminary","Provisional"))

Here is the backtrace on the error:

Error in `dplyr::collect()`: ! Failed to collect lazy table. Caused by error: ! nanodbc/nanodbc.cpp:1710: 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Backtrace: ▆

└─AWQMSdata::AWQMS_Data_Cont(...)
├─dplyr::collect(...)
└─dbplyr:::collect.tbl_sql(...) Run rlang::last_trace(drop = FALSE) to see 6 hidden frames.

Please let me know if there is anything I can do to help!

TravisPritchardODEQ commented 5 months ago

This is due to

AWQMS_Data_Cont(OrganizationID = "CITY_STHELENS/BC(NOSTORETID)" , Result_Status=c("Accepted","Final","Validated","Preliminary","Provisional"))

not returning any data from AWQMS. When performing the Stations query, there are no mlocs to filter by. I will edit the function to make it fail better and return an empty dataframe

TravisPritchardODEQ commented 5 months ago

Fixed in version 3.0.2. @DEQabrits - please reinstall and confirm the error is resolved.

DEQabrits commented 5 months ago

Ran code. Got new error:

[1] "Query AWQMS database..." [1] "Query AWQMS database- Complete" AWQMS database query: 36.21 sec elapsed Warning: Error in select: Can't subset columns that don't exist. ✖ Column StationDes doesn't exist.

Also, it is very common for us over in the NPDES program to search only using OrganizationID and dates. Would you be able to add OrganizationID to the bit of code that filters the stations (I noticed you had it for HUC8 and AU_ID, etc)?

TravisPritchardODEQ commented 5 months ago

Fixed in 3.0.4

TravisPritchardODEQ / AWQMSdata