Report generates a SQL error that view does not

[Bob Cergol]

I have a DAI view that I can execute and browse the result set with no problem. But the report connected to that view gives a SQL error "Incorrect syntax near the keyword IF". That keyword does not occur in the view -- so DAI has to be generating it on-the-fly.

SQL_IF_Error.gif (27.07 KiB) Viewed 11779 times

I had been muddling my way through setting up an alias1 filter on customer ID. I had that working fine. I could login as a user that had an alias1 value of "XYZ" and when browsing the view as that user, the result set was constrained to rows for "XYZ".

Thinking something I'd done wrong was causing the error, I removed the alias value definitions. However the system won't delete the system alias definitions -- and I'm thinking this has something to do with the error.

DAI_System_Alias_Values.gif (13.97 KiB) Viewed 11779 times

I've blanked out the values you see displayed, clicked save, get the system updated message, and then navigate back to this screen and the values are not gone. (BTW -- its tedious that navigation returns to the system start page after each update to any user or system data, and one has to navigate back to where one started.)

I admit I'm delving into this without having fully read the docs in this area and my understanding is still hazy so I'm probably doing something dumb.

Bob

[Eddy Kleinjan]

Hi Bob,

Does the Dynamic AI View have a dictionary defined for it and/or a forced filter? Or maybe there was a filter put on the report itself? If it does have those, can you try to remove those and see if the error goes away?

Best regards,

Eddy Kleinjan

[Bob Cergol]

Hi Eddy,

I had defined a forced filter in the view's data dictionary, none in the report. After I proved that worked by browsing the view, I defined another one on sales rep ID and proceeded to work on a report.

After getting the error I thought getting rid of the filter and retesting was the right thing to try. Here's the sequence I followed as far as I can remember.

1) Navigated to Adm., System, Alias tab page and tried clearing the aliases show there.
2) removed all alias definitions from the user profiles
3) Navigated to Adm., Dictionary, Forced Filter/Key Fields, and deleted the entries from there.

After reading your reply I navigated navigated to the "maintain dictionary" option from right clicking on the view and clicked on the "enforced filters" tab, scrolled down and the Alias# values I'd entered are no longer there.

So currently no filters are defined. The view works, "AI" works on it, but the report generates the error.

I think I did something in an unusual, perhaps nonsensical sequence, and introduced some internal inconsistency. I'll delete the report and if that doesn't work I'll delete the view and start over. I am concerned that the system alias screen retains what looks to me to be incomplete entries.

Thanks,
Bob

[Eddy Kleinjan]

Hi Bob,

When alias values are changed, these are not immediately active; they become active after logging out and in again. Could you try to logout and login to Dynamic AI to activate the alias values? That might help.

Eddy

[Carsten Sørensen]

Hi Bob,

Using alias bundles involves using a prefix bundle ID etc. It's a little tricky to get setup but powerful and solid in use.

Apart from the forced_filter value in the dictionary - we might have to set a data-type (contentType) for the key as well. (I see that you use IDNO) and I believe the default for the filter is TEXT.

We'll get back to you with a better example on how to set it up!

Best regards,
Carsten

[Bob Cergol]

I did log out and back in during testing as I figured out DAI loads and retains the user alias values from its repository only at login -- which makes sense. But it didn't affect report error I was getting.

I deleted that report, recreated the enforced filter in the view and a new report and am not having any problem now. I' sure I did some convoluted sequence of things that you'd never be able to test that let to whatever went wrong.

It would be great to see more specific examples of some of the more advanced features -- such as these "bundles". I'm not grasping that -- beyond its a way to have something in between all or nothing on enforced filters.

The AI6 Server demo database does not have much in the way of examples to look at on these advanced features. It was the first place I checked before the documentation.

Thanks for your quick responses.

The more I explore in the system, the more I see the wealth of latent functionality that exists -- just waiting for me to figure it out! I hope I can, I think I can ....

[Carsten Sørensen]

I'm sure you can!!!

just 2 additional comments:

- Your ability to boil alias bundles down to "something in between all or nothing" is spot on!! Impressed me again - seriously!!!

- We used to have bundles and partitioning in the DEMO - however we removed it into an "advanced demo" (that I'm currently not sure which state is in) - as it was making too much "noise" during initial learning. I have asked Mikkel to give you more info on the forced_filter - I'm sure he will be happy to see that you want more of the advanced stuff

best regards
Carsten

[Mikkel Jensen]

Sure!

Please see http://support.dynamicai.com/forum/viewtopic.php?f=3&t=170 for a "walk through" of how to set up alias bundles

/Mikkel

[Carsten Sørensen]

(BTW -- its tedious that navigation returns to the system start page after each update to any user or system data, and one has to navigate back to where one started.)

Hi Bob,

Just a note - When you get the OK / CANCEL dialog - you can click cancel to stay in the system data window instead of jumping to the start page. OR ignore the dialog and pick a new item from the administration menu to the left. The final dialog is fyi only.

Best regards,
Carsten