SQL Expression I am stumped on this one

[ids-dave]

I have a report that uses the Alpha SQL statement builder (instead of a view) and am stuck on a parse error on one of the expressions

These expressions work .....
if( status = 'Active' , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) )
if( CurrentDate() < :r_from , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) )
if( CurrentDate() < start_date , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) )

I did this to prove to myself all dates are legit dates in the correct format. What I really need to do is .....

if( start_date >= :r_from , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) )

but when I use start_date and the argument :r_from together in the first operand of the IF statement, I get a Alpha parsing error message

Any suggestions would be very helpful. I try to do most data handling in SQL views but in this case I need to use arguments in the SQL statement as part of an expression. Is there any way of switching to a SQL view and use the arguments defined in Alpha ?

[Steve Workings]

Re: SQL Expression I am stumped on this one

I haven't tried your expression (I would if you had supplied an example without args) but wonder if you might try it without PortableSQL enabled?

[ids-dave]

Re: SQL Expression I am stumped on this one

Hi Steve, tried changing from Portable to Native but Alpha spit out a handful of errors

Here's one statement that works
SELECT Company_ID, EmpID, (( rtrim(lastname) + ', ') + rtrim(firstname) ) AS empname, start_date, end_date, [pay type], fulltime_parttime, [FTE Hours], status, if( CurrentDate() <= :r_from , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) ) AS weeks1
FROM employee_master

but if I change CurrentDate() to the field "start_date", I get a parsing error. Interestingly enough, if I keep CurrentDate() and change the :r_from arg to be the "start_date" field, there is no error. I only get the parsing error when I try to use start_date and the :r_from arguments together in the IF statement. Either operand will work when used with CurrentDate() or a hardcoded date value, but not together. I am befuddled.....

I know you asked for the statement without the args, but that won't help me figure this out. The parsing error comes when using an argument and a field together in the IF compare. I've already tried lots of combinations and can get either to work against anything other than each other

[christappan]

Re: SQL Expression I am stumped on this one

You can use arguments in the where clause of a view query just like you would a table. Or you can build the query yourself in the onSQLSelectQuery event. Or you can create a stored procedure and pass arguments. Note that the latter two get complicated if you want to use a search part on top of that.

[ids-dave]

Re: SQL Expression I am stumped on this one

This is a report so there's no event trigger that I know of (onSQLSelectQuery)
I got tired of struggling with this so I went the cheesy route... created 3 seperate SQL expressions to calculate the different possible # of weeks elapsed fields. Then in the report, I created a column with an expression that tests for additional conditions I set in the SQL expression in the false result to determine which of the 3 possibilites to print. I know some day I'll go back to this report and wonder why in the world I did it this way... but that'll be a new day

As always, thanks for the input

[TheSmitchell]

Re: SQL Expression I am stumped on this one

Hi Steve, tried changing from Portable to Native but Alpha spit out a handful of errors

Here's one statement that works
SELECT Company_ID, EmpID, (( rtrim(lastname) + ', ') + rtrim(firstname) ) AS empname, start_date, end_date, [pay type], fulltime_parttime, [FTE Hours], status, if( CurrentDate() <= :r_from , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) ) AS weeks1
FROM employee_master

but if I change CurrentDate() to the field "start_date", I get a parsing error. Interestingly enough, if I keep CurrentDate() and change the :r_from arg to be the "start_date" field, there is no error. I only get the parsing error when I try to use start_date and the :r_from arguments together in the IF statement. Either operand will work when used with CurrentDate() or a hardcoded date value, but not together. I am befuddled.....

I know you asked for the statement without the args, but that won't help me figure this out. The parsing error comes when using an argument and a field together in the IF compare. I've already tried lots of combinations and can get either to work against anything other than each other

:r_from is being treated as/set as a date, yes? And start_date is a date type as well? It seems maybe the system might not understand that start_date and :r_from are both date types in that scenario.

Curious: if your change your expression from start_date >= :r_from to DaysBetweenDates(start_date, :r_from) <= 0, does that get you what you want? If start_date is greater than :r_from, then the DaysBetweenDates function is going to return a negative value. If they're equal, it returns 0.

Maybe yes?

[ids-dave]

Re: SQL Expression I am stumped on this one

Hi Sarah, yes the date fields are valid date data types and each works when compared to a hardcoded date. The problem is Alpha is spitting out a SQL parsing error message even though the statement is valid.

Your DaysBetweenDates suggestion was interesting so I just tried it and it works - no Alpha sql parsing error !

No parsing error when the expression is coded as --> if( DaysBetweenDates(start_date, :r_from) <= 0 , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) ) AS weeks1

but this expression gets a parsing error --> if( start_date <= :r_from , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) ) AS weeks1

Both are valid SQL expressions but is another case of the Alpha parsing process kicking out an error when nothing is wrong

[TheSmitchell]

Re: SQL Expression I am stumped on this one

Hi Sarah, yes the date fields are valid date data types and each works when compared to a hardcoded date. The problem is Alpha is spitting out a SQL parsing error message even though the statement is valid.

Your DaysBetweenDates suggestion was interesting so I just tried it and it works - no Alpha sql parsing error !

No parsing error when the expression is coded as --> if( DaysBetweenDates(start_date, :r_from) <= 0 , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) ) AS weeks1

but this expression gets a parsing error --> if( start_date <= :r_from , DATEDIFF(week, :r_from, :r_to) , DATEDIFF(week, :r_from, end_date) ) AS weeks1

Both are valid SQL expressions but is another case of the Alpha parsing process kicking out an error when nothing is wrong

I would recommend sending your wee expression into Alpha to see if that isn't some sort of bug maybe? Or maybe the argument isn't being set properly or is converting to a string for bizarre reasons? Or maybe your start_date was being converted to a string? It doesn't make sense to me that the date function would work but date comparison would fail for a date field in your DB and a date argument. So strange.