BusinessObjects Board

Unable to create a sysdate object @ universe level

building semantic layer
#6

Hi,

I defined it to be a Dimension object , type was date, in the select stmt I choose the systdate from the date function list.

swamy82 (BOB member since 2007-05-08)

#7

Shouldn’t it be

sysdate

if you use Oracle database?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#8

Yes…typo from my side…I did use sysdate from the function list.

swamy82 (BOB member since 2007-05-08)

#9

Then it should be possible to save the object and use it with any other object.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#10

PFA, the screens of how I created this object.

My oracle db is 11g , I use BO XI R2 10 g drivers to connect to that DB, will that matter,I feel that should be something to do with the drivers compatiablity.

Please comment.
Sysdate_object_error.doc (59.0 KB)

swamy82 (BOB member since 2007-05-08)

#11

I already mentioned that such object will not parse. But, you can save it and use it with any other object in a query. Have you tried that? :roll_eyes:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#12

This covers it all:

Dave Rathbun :us: (BOB member since 2002-06-06)

#13

Yes,I did try to save with the parse error, it is not allowing me to go ahead, that is the issue I am talking abt.

swamy82 (BOB member since 2007-05-08)

#14

Dave, I completely understand that even if i get a parse error, I can go ahead save the object, but in this case it is not allowing me to save.

swamy82 (BOB member since 2007-05-08)

#15

Don’t parse, just save it.

What sort of response are you getting? I’ve never seen this behavior, where Designer would refuse to save an object because of a parse error.

Dave Rathbun :us: (BOB member since 2002-06-06)

#16

Yea…I did try that, the error I get is :

Exception DBD ,ORA 00903:INVALID Table name.

I just tired to save . did not parse it at all.

Thanks.,

swamy82 (BOB member since 2007-05-08)

#17

Create a new object.

Call it Today

In the select box type
trunc(sysdate)

Click OK at the bottom.

Save and Export your universe.

Use the Today object in a query with other objects.

Report back.

:+1:

#18

Something just occurred to me… you probably have automatic parse turned on. Turn that off and you should be fine. It’s under “Tools” and then “Options” and on the “General” tab.

Dave Rathbun :us: (BOB member since 2002-06-06)

#19

Great Thanks Dave…that worked…I had the automatic parse option disabled.

Thanks, that worked…I really appreciate your help.

Rgds,

swamy82 (BOB member since 2007-05-08)

#20

Hi,

You can link the object to SYS.Dual table which is a dummy table in Oracle and then ue SysDate in Object Definitions to avoid parse error .

sagar10 (BOB member since 2010-11-11)

#21

Including the DUAL table solves one problem but creates many more, mostly because of the lack of joins to the table.

Dave Rathbun :us: (BOB member since 2002-06-06)

#22

HI Dave,

Could you be please more specific on the problems created by using Sys.dual and lack of join?I was thinking as the sys.dual did have any data even if it is not joined with another table and a cartesian takes place it will no cause any issue…

sagar10 (BOB member since 2010-11-11)

#23

There is a setting in the universe related to Cartesian products. It is either set to “prevent” or “warn.” Prevent means you cannot run a query that contains a Cartesian product at all. Warn means the user will have to see the message about the warning, understand it, and click to go ahead and run the query. Neither situation is desirable. :slight_smile:

Dave Rathbun :us: (BOB member since 2002-06-06)

#24

did you check the query that it generated for the LOV. Is it taking the table as dual? if yes then add that dual table into the universe and point that object to dual table. This may solve your problem. Try to create new object and do this. Sometimes the exisitng object will throgh errors but the same logic works in new object :frowning:

divyap (BOB member since 2010-09-08)

#25

I just read the Fixed Issues in SP4, and I think this all seems difficult now - if I read it right, warnings about cartesian products will stop execution of the query.

Has anyone loaded SP4 yet (6/1/11), and do you know this behaviour? It seemed bad enough before I realized there was already a Cancel / Warn toggle in Designer, now it just seems silly.

Silly that would stop me going to SP4… I’m opening a case with BO while I wait for my CD to arrive, but I’m pretty curious about this.

B

bdouglas :switzerland: (BOB member since 2002-08-29)