Page 1 of 1

Report wont run but SQL view runs fine

PostPosted: Fri Dec 07, 2012 3:44 pm
by cjbates
Hi Bob,

I am having a problem with a report that I did on a new sql join that uses a sql view. The view and join execute without error and I can browse the data, but when I run the report I am getting an error that DAI cant open the report and I am prompted to open the Join.
Ive attached a screen shot of the Join. Do you see something wrong?

Thanks
Cynthia

Re: Report wont run but SQL view runs fine

PostPosted: Fri Dec 07, 2012 7:31 pm
by Bob Cergol
Hi Cynthia,

I don't see an error in your dyn-join. I notice your first "table" is actually a dynamic data source -- probably a join since I don't think you are using dyn-views at this time. You are allowed to do this by the way.

What is the error you are getting?

If it is "invalid use of null" then it could mean you have null values in your data that is causing a problem.

If so, you will have to either eliminate the null values -- or use a dyn-view to convert them to a value like:

Select
isNull(order.[date],'1751-01-01') as order_date,
isNull(order.amount,0) as order_amount,
etc.

You don't have to convert every column, just the ones involved in joins, groupings and some formulas -- especially if "case" statement is involved.

Regards,
Bob

Re: Report wont run but SQL view runs fine

PostPosted: Sat Dec 08, 2012 6:35 pm
by cjbates
Hi Bob,

The only error I get is that DAI cant open the report. There is no other information....

I already decided to just create a sql view with all the fields I need instead of using the Join....

Thanks.

Cynthia

Re: Report wont run but SQL view runs fine

PostPosted: Mon Dec 10, 2012 3:18 pm
by Bob Cergol
Cynthia,

The system log "General Alerts" will contain an entry for each time you got the error. Sometimes this gives additional clues for discovering what is causing the error.

Bob