Planchardo
Programmer
I have been trying to write an SQL query in a report in CR 8.5 and 11, but having problems in both. Here is the query:
(select employee.memberid from employee
where DateDiff("m",EMPLOYEE.dateOfBirth,{fn CURDATE()}) > 462
and DateDiff("m",EMPLOYEE.dateOfBirth,{fn CURDATE()}) <= 522
and EMPLOYEE.sex <> 'F'
and COVERAGEMSTR.groupNo = '0000094001'
and PRODUCT.productId in ('GAD&D', 'GLIFE'))
Basically, I am trying to select the member IDs of employees in a certain age range with certain criteria, and pass that list of IDs into a running total. The best way I figured was to use an SQL query, but every time the syntax is checked in 8.5, I keep on getting the error message
"Error in compiling SQL expression : . Error in initialising SQL Expression parser. Error in creating SQL Expression COM parser.",
no matter how simple the expression (such as (select * from employee)). Even when I make a new report and have only the sql expression field in the report, I still get the same error when I check the syntax.
So I tried the same expression in CR 11, and the syntax apparently works, but once I put the sql expression field in the report itself and try to preview it, the program keeps on freezing. Also, when I try to break down the query into something simple, I get an error along the lines of
"Error in compiling SQL expression:"..." Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression."
This message usually only shows up if I search on just one table, or if the criteria searches on the same table
eg. (select employee.memberid from employee where EMPLOYEE.lastname = 'gordon'),
...but if I add criteria that searches other tables , the syntax works:
eg. (select employee.memberid from employee where EMPLOYEE.lastname = 'gordon' and product.groupno = '94001').
However, the program still freezes once I try to preview the report. Is there any solution to this problem with both 8.5 and 11? And if not, is it possible for me to use a formula to do what I need to do?
(select employee.memberid from employee
where DateDiff("m",EMPLOYEE.dateOfBirth,{fn CURDATE()}) > 462
and DateDiff("m",EMPLOYEE.dateOfBirth,{fn CURDATE()}) <= 522
and EMPLOYEE.sex <> 'F'
and COVERAGEMSTR.groupNo = '0000094001'
and PRODUCT.productId in ('GAD&D', 'GLIFE'))
Basically, I am trying to select the member IDs of employees in a certain age range with certain criteria, and pass that list of IDs into a running total. The best way I figured was to use an SQL query, but every time the syntax is checked in 8.5, I keep on getting the error message
"Error in compiling SQL expression : . Error in initialising SQL Expression parser. Error in creating SQL Expression COM parser.",
no matter how simple the expression (such as (select * from employee)). Even when I make a new report and have only the sql expression field in the report, I still get the same error when I check the syntax.
So I tried the same expression in CR 11, and the syntax apparently works, but once I put the sql expression field in the report itself and try to preview it, the program keeps on freezing. Also, when I try to break down the query into something simple, I get an error along the lines of
"Error in compiling SQL expression:"..." Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression."
This message usually only shows up if I search on just one table, or if the criteria searches on the same table
eg. (select employee.memberid from employee where EMPLOYEE.lastname = 'gordon'),
...but if I add criteria that searches other tables , the syntax works:
eg. (select employee.memberid from employee where EMPLOYEE.lastname = 'gordon' and product.groupno = '94001').
However, the program still freezes once I try to preview the report. Is there any solution to this problem with both 8.5 and 11? And if not, is it possible for me to use a formula to do what I need to do?