SQL expression not returning data

[morechocolate]

I am doing a simple SQL expression and nothing is returning though it should.

Here is an example of the expression.

{fn YEAR(ACCRHIST."ACCOUNTING_DATE")} The ACCRHIST."ACCOUNTING_DATE" field returns 6/30/2005, however, the SQL expression does not return anything. Any ideas why not?

Thanks

 

[morechocolate]

I forgot to mention that I am using version 8.5

 

[synapsevampire]

Please supply the database/connectivity used.

You can create a formula in Crystal of year({ACCRHIST."ACCOUNTING_DATE"}) to get the same result.

You might try using:

datepart(yyyy,ACCRHIST."ACCOUNTING_DATE")

SQL Expressions can use the actual database syntax if you don't use the {FN} wrapper

-k

 

[morechocolate]

Yes I realize I can do that, but this is part of a bigger issue. I was breaking the issue down into smaller pieces to resolve the problem. I know the problem has to do with what I showed above. Here is what I was trying to do.

(SELECT P.DISCOUNT_BAL_P
FROM PORTHIST P
WHERE P.ACCOUNTING_DATE = dateadd(day,-1,(dateadd(day, -(datepart(day,PORTHIST."ACCOUNTING_DATE"-1)), (dateadd(day,datediff(day,0,PORTHIST."ACCOUNTING_DATE"),0))))) AND
P.LOAN = PORTHIST."LOAN" AND
P.PARTICIPANT = PORTHIST."PARTICIPANT" AND
P.PORTFOLIO = PORTHIST."PORTFOLIO"
)

Either nothing was being returned, as it should be in cases where there was no match, but in instances where something should return some pretty wacky and incorrect information was being returned.

The report itself returns information from the prior month which is supplied in the select expert. The field I am trying to derive here would return the value for the month prior to the date in the select expert. If the user was always going to run this for the prior month and the month before that, I would not have to do the expression this way. However, the user can supply any date for the select expert and they would want the value for the month prior to that date.

Thanks for your feedback

 

[morechocolate]

I have no idea what was happening earlier, but the darn thing is working fine now.

 

[morechocolate]

I got excited for nothing. I was looking at the wrong field. It's still not working. UGH!!! It works fine in SQL Sever.