Report wont run but SQL view runs fine

Technical support

Moderators: Bob Cergol, Data Access, Cintac

Report wont run but SQL view runs fine

Postby cjbates » Fri Dec 07, 2012 3:44 pm

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
Attachments
daisqljoin.png
daisqljoin.png (72.41 KiB) Viewed 24258 times
cjbates
 
Posts: 25
Joined: Thu May 10, 2012 7:46 pm

Re: Report wont run but SQL view runs fine

Postby Bob Cergol » Fri Dec 07, 2012 7:31 pm

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
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm

Re: Report wont run but SQL view runs fine

Postby cjbates » Sat Dec 08, 2012 6:35 pm

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
cjbates
 
Posts: 25
Joined: Thu May 10, 2012 7:46 pm

Re: Report wont run but SQL view runs fine

Postby Bob Cergol » Mon Dec 10, 2012 3:18 pm

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
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm


Return to Technical Support

cron