I am attempting to create a chart based on data returned from an SQL statement built and run in an xbasic function.
but when it returns from the xbasic function, I get this error : Error:Script:a5_wp_series_eval() line:12 Expected value
I scrolled thru 50 pages of the documentation after putting this in the search and come up with nothing.
What does this error mean?
I have confirmed that the xbasic function is properly creating the data:
31|001|07|08/10/2006|1|9�31|001|07|08/11/2006|3|51�31|001|07|08/12/2006|10|189�31|001|07|08/13/2006|1|20�31|001|07|08/02/2010|1|12�31|001|07|08/05/2010|1|17�31|001|07|08/06/2010|3|43�31|001|07|08/08/2010|1|6�31|001|07|08/18/2010|2|25�31|001|07|08/19/2010|10|136�31|001|07|08/22/2010|15|173�31|001|07|08/23/2010|8|99�31|001|07|08/24/2010|7|116�31|001|07|08/25/2010|5|52�31|001|07|08/27/2010|2|33�31|001|07|08/28/2010|4|48�31|001|07|08/29/2010|1|11�31|001|07|08/31/2010|1|19�31|001|07|09/03/2010|4|42�31|001|07|08/18/2011|1|17�31|001|07|08/22/2011|4|66�31|001|07|08/25/2011|171|1034�31|001|07|08/09/2012|2|24�31|001|07|08/07/2014|1|11�31|001|07|08/08/2014|1|19�31|001|07|08/10/2014|1|11�31|001|07|08/11/2014|2|30�31|001|07|08/15/2014|1|19�31|001|07|08/23/2014|2|15�31|001|07|08/24/2014|11|85�31|001|07|08/25/2014|5|31�31|001|07|08/27/2014|18|162�31|001|07|08/28/2014|2|40�31|001|07|08/10/2015|2|31�31|001|07|09/03/2017|2|20�31|001|07|08/03/2018|4|31�31|001|07|08/05/2018|5|25�31|001|07|08/07/2018|5|60�31|001|07|08/08/2018|10|84�31|001|07|08/15/2018|5|66�31|001|07|08/16/2018|10|98�31|001|07|08/24/2018|6|58�
My data series is defined as
Custom
Xbasic function name : getdata
Subseries names: tribe, species, area, timestrata, num_fish, num_lbs
function getdata as C (e as p)
'
' Description:
' Gets criteria (time strata, species, catch area, data source) from
' screen and builds SQL query to get data for chart
'
'=======================================================================
'
' Revision History
'
' Date Description of Modification
' 07/15/2021 Initial version
'=======================================================================
' Arguments: e as Pointer to submitted record
'
' Calls: none
'
' Returns: string
' return value: list of values for chart
'=======================================================================
dim ls_timestrata as c
dim ls_species as c
dim ls_area as c
dim ls_datasource as c
dim ls_txt as c
dim ls_tribe as c
dim ls_select as c
dim ls_from as c
dim ls_where as c
dim ls_group as c
dim ls_order as c
dim ls_begin_date as c
dim ls_end_date as c
dim ls_sql as c
dim args as sql::Arguments
dim cn as sql::Connection
dim rs as sql::ResultSet
dim connectflag as L
dim resultflag as L
dim goodflag as L
debug(1)
' get criteria
if eval_valid("e.datasubmitted.SHOW") = .F. then
ls_datasource = "TOCAS"
else
ls_datasource = e.datasubmitted.SHOW
end if
if (ls_datasource = "TOCAS") then
ls_from = " FROM tocas_dba.TOCAS_VIEW "
else
if (ls_datasource = "WAFT") then
ls_from = " FROM tocas_dba.LIFT_VIEW "
else ' ls_datasource = "ALL"
ls_from = " FROM ALL_TICKET_VIEW "
end if
end if
if eval_valid("e.datasubmitted.time_strata") = .F. then
ls_timestrata = "Day"
else
ls_timestrata = e.datasubmitted.time_strata
end if
SELECT
CASE ls_timestrata = "Day"
ls_timeclause = "landing_date"
CASE ls_timestrata = "Week"
ls_timeclause = "mgmt_week"
CASE ls_timestrata = "Month"
ls_timeclause = "month(landing_date)"
CASE ls_timestrata = "Year"
ls_timeclause = "catch_year"
CASE ELSE
ls_timeclause = ls_break
END SELECT
if eval_valid("e.datasubmitted.begin_date") = .F. then
ls_begin_date = "01/01/1970"
else
ls_begin_date = e.datasubmitted.begin_date
end if
if eval_valid("e.datasubmitted.end_date") = .F. then
ls_end_date = "12/31/2100"
else
ls_end_date = e.datasubmitted.end_date
end if
if eval_valid("e.datasubmitted.TRIBE") = .F. then
ls_tribe = SESSION.USER_TRIBE
else
ls_tribe = e.datasubmitted.TRIBE
end if
if eval_valid("e.datasubmitted.species") = .F. then
ls_species = "001"
else
ls_species = e.datasubmitted.species
end if
if eval_valid("e.datasubmitted.catch_area") = .F. then
ls_area = "07"
else
ls_area = e.datasubmitted.catch_area
end if
' build query
ls_select = "SELECT tribe_num, species_code, catch_area_code, " + ls_timeclause + " as timestrata, sum(rptd_cnt) as num_fish, sum(rptd_lbs_qty) as num_lbs "
ls_where = "WHERE tribe_num = :whattribe and species_code = :whatspecies and catch_area_code = :whatarea"
ls_where = ls_where + " and landing_date >= :startdate and landing_date <= :enddate "
ls_group = " group by tribe_num, species_code, catch_area_code, " + ls_timeclause
ls_order = " order by " + ls_timeclause + ", species_code, catch_area_code"
ls_sql = ls_select + ls_from + ls_where + ls_group + ls_order
debug(1)
' set arguments
args.Add("whattribe", ls_tribe )
args.Add("whatspecies", ls_species )
args.Add("whatarea", ls_area)
args.Add("startdate", ls_begin_date)
args.Add("enddate", ls_end_date)
' run query and get data
connectflag = cn.Open("::Name::TOCAS")
cn.PortableSQLEnabled = .f.
resultflag = cn.Execute(ls_sql, args)
rs = cn.ResultSet
ls_txt = ""
goodflag = rs.nextRow()
while goodflag
ls_txt = ls_txt + rs.data(1) + "|" + rs.data(2) + "|" + rs.data(3)+ "|" + rs.data(4)+ "|" + rs.data(5)+ "|" + rs.data(6) + crlf()
goodflag = rs.nextRow()
end while
cn.close()
getdata = ls_txt
end function ' getdata
but when it returns from the xbasic function, I get this error : Error:Script:a5_wp_series_eval() line:12 Expected value
I scrolled thru 50 pages of the documentation after putting this in the search and come up with nothing.
What does this error mean?
I have confirmed that the xbasic function is properly creating the data:
31|001|07|08/10/2006|1|9�31|001|07|08/11/2006|3|51�31|001|07|08/12/2006|10|189�31|001|07|08/13/2006|1|20�31|001|07|08/02/2010|1|12�31|001|07|08/05/2010|1|17�31|001|07|08/06/2010|3|43�31|001|07|08/08/2010|1|6�31|001|07|08/18/2010|2|25�31|001|07|08/19/2010|10|136�31|001|07|08/22/2010|15|173�31|001|07|08/23/2010|8|99�31|001|07|08/24/2010|7|116�31|001|07|08/25/2010|5|52�31|001|07|08/27/2010|2|33�31|001|07|08/28/2010|4|48�31|001|07|08/29/2010|1|11�31|001|07|08/31/2010|1|19�31|001|07|09/03/2010|4|42�31|001|07|08/18/2011|1|17�31|001|07|08/22/2011|4|66�31|001|07|08/25/2011|171|1034�31|001|07|08/09/2012|2|24�31|001|07|08/07/2014|1|11�31|001|07|08/08/2014|1|19�31|001|07|08/10/2014|1|11�31|001|07|08/11/2014|2|30�31|001|07|08/15/2014|1|19�31|001|07|08/23/2014|2|15�31|001|07|08/24/2014|11|85�31|001|07|08/25/2014|5|31�31|001|07|08/27/2014|18|162�31|001|07|08/28/2014|2|40�31|001|07|08/10/2015|2|31�31|001|07|09/03/2017|2|20�31|001|07|08/03/2018|4|31�31|001|07|08/05/2018|5|25�31|001|07|08/07/2018|5|60�31|001|07|08/08/2018|10|84�31|001|07|08/15/2018|5|66�31|001|07|08/16/2018|10|98�31|001|07|08/24/2018|6|58�
My data series is defined as
Custom
Xbasic function name : getdata
Subseries names: tribe, species, area, timestrata, num_fish, num_lbs
function getdata as C (e as p)
'
' Description:
' Gets criteria (time strata, species, catch area, data source) from
' screen and builds SQL query to get data for chart
'
'=======================================================================
'
' Revision History
'
' Date Description of Modification
' 07/15/2021 Initial version
'=======================================================================
' Arguments: e as Pointer to submitted record
'
' Calls: none
'
' Returns: string
' return value: list of values for chart
'=======================================================================
dim ls_timestrata as c
dim ls_species as c
dim ls_area as c
dim ls_datasource as c
dim ls_txt as c
dim ls_tribe as c
dim ls_select as c
dim ls_from as c
dim ls_where as c
dim ls_group as c
dim ls_order as c
dim ls_begin_date as c
dim ls_end_date as c
dim ls_sql as c
dim args as sql::Arguments
dim cn as sql::Connection
dim rs as sql::ResultSet
dim connectflag as L
dim resultflag as L
dim goodflag as L
debug(1)
' get criteria
if eval_valid("e.datasubmitted.SHOW") = .F. then
ls_datasource = "TOCAS"
else
ls_datasource = e.datasubmitted.SHOW
end if
if (ls_datasource = "TOCAS") then
ls_from = " FROM tocas_dba.TOCAS_VIEW "
else
if (ls_datasource = "WAFT") then
ls_from = " FROM tocas_dba.LIFT_VIEW "
else ' ls_datasource = "ALL"
ls_from = " FROM ALL_TICKET_VIEW "
end if
end if
if eval_valid("e.datasubmitted.time_strata") = .F. then
ls_timestrata = "Day"
else
ls_timestrata = e.datasubmitted.time_strata
end if
SELECT
CASE ls_timestrata = "Day"
ls_timeclause = "landing_date"
CASE ls_timestrata = "Week"
ls_timeclause = "mgmt_week"
CASE ls_timestrata = "Month"
ls_timeclause = "month(landing_date)"
CASE ls_timestrata = "Year"
ls_timeclause = "catch_year"
CASE ELSE
ls_timeclause = ls_break
END SELECT
if eval_valid("e.datasubmitted.begin_date") = .F. then
ls_begin_date = "01/01/1970"
else
ls_begin_date = e.datasubmitted.begin_date
end if
if eval_valid("e.datasubmitted.end_date") = .F. then
ls_end_date = "12/31/2100"
else
ls_end_date = e.datasubmitted.end_date
end if
if eval_valid("e.datasubmitted.TRIBE") = .F. then
ls_tribe = SESSION.USER_TRIBE
else
ls_tribe = e.datasubmitted.TRIBE
end if
if eval_valid("e.datasubmitted.species") = .F. then
ls_species = "001"
else
ls_species = e.datasubmitted.species
end if
if eval_valid("e.datasubmitted.catch_area") = .F. then
ls_area = "07"
else
ls_area = e.datasubmitted.catch_area
end if
' build query
ls_select = "SELECT tribe_num, species_code, catch_area_code, " + ls_timeclause + " as timestrata, sum(rptd_cnt) as num_fish, sum(rptd_lbs_qty) as num_lbs "
ls_where = "WHERE tribe_num = :whattribe and species_code = :whatspecies and catch_area_code = :whatarea"
ls_where = ls_where + " and landing_date >= :startdate and landing_date <= :enddate "
ls_group = " group by tribe_num, species_code, catch_area_code, " + ls_timeclause
ls_order = " order by " + ls_timeclause + ", species_code, catch_area_code"
ls_sql = ls_select + ls_from + ls_where + ls_group + ls_order
debug(1)
' set arguments
args.Add("whattribe", ls_tribe )
args.Add("whatspecies", ls_species )
args.Add("whatarea", ls_area)
args.Add("startdate", ls_begin_date)
args.Add("enddate", ls_end_date)
' run query and get data
connectflag = cn.Open("::Name::TOCAS")
cn.PortableSQLEnabled = .f.
resultflag = cn.Execute(ls_sql, args)
rs = cn.ResultSet
ls_txt = ""
goodflag = rs.nextRow()
while goodflag
ls_txt = ls_txt + rs.data(1) + "|" + rs.data(2) + "|" + rs.data(3)+ "|" + rs.data(4)+ "|" + rs.data(5)+ "|" + rs.data(6) + crlf()
goodflag = rs.nextRow()
end while
cn.close()
getdata = ls_txt
end function ' getdata
Comment