HI ,
I need to create an object which just uses sysdate in designer for a report.
I want the objects to be there at designer
Appreciate your assistance
sethybibeka (BOB member since 2005-12-23)
Hi,
create a new object and in the select part use sysdate. The object wont parse as it is not referencing any table.
However it can be used in the report along with any other objects just fine. try it and see 
Marek Chladny 
(BOB member since 2003-11-27)
Hello,
I have tried this option and doesnot work. Can you pls show me how exactly it is defined?
Actually we have to create some 5 sysdate condition objects (e.g Sysdate - 1) ; TRUNC(ADD_MONTHS(SYSDATE,-1)) ; sysdate - 7 and some more without referencing any table. As per my experience I think that is not possible. If anyone know a way to do this your help is appreciated.
Thank you
joshroy (BOB member since 2006-09-19)
You can’t build a condition if it is not going to reference a column in a table. If you want SYSDATE-7 to return as a result, you have to build it into a dimension or detail object.
Eric Vallo 
(BOB member since 2002-08-15)
If you want a result column, then make a regular object. If you want a condition, then you have to have a valid where clause. “sysdate” is not a valid where clause. invoice_headers.invoice_date = sysdate is a valid condition, and will parse because it references a table.
If you can provide a specific question, you’ll get a specific answer. 
Dave Rathbun (BOB member since 2002-06-06)
Define “does not work”
Are you getting an error message? What exactly is the code your are using?
I tried this with a dimension defined with GETDATE(). If I selected only the dimension I would get an error message "The Query does not reference a table. (QP0007).
If I selected another dimension then results were returned. Each row of my report contained the system date.
(I believe SYSDATE is the Oracle version of SQL Server GETDATE.)
Crash (BOB member since 2004-04-27)
Correct.
In Teradata the equivalent is DATE. I believe it’s the same for DB2.
Dave Rathbun (BOB member since 2002-06-06)
Thank you for the replies.
Basically the user wants the condition objects. So we want the data on the WEBI report with conditions defined in the universe for System date.
e.g 1) for current sysdate i have to defined a condition object in universe.
So i did the following
SYSDATE = (SELECT (SYSDATE) FROM DUAL )
- For last month date condition following :
SYSDATE BETWEEN (SELECT ADD_MONTHS(TRUNC(SYSDATE),-1) FROM DUAL) AND (SELECT (SYSDATE) FROM DUAL)
But the issue is i get same data . And i am not sure if what i have wriiten is correct for condition object as it doenot parse and will not as everybody mentioned as it doenot reference the table . but when i use this condition in report with other dimension i get some data but not sure if it is correct.
joshroy (BOB member since 2006-09-19)
Hi,
your predefined conditions in the universe should look something like:
- For current date:
TRUNC(universe_table.datecolumn_you_want_to_limit) = TRUNC(SYSDATE)and
2) For last month:
universe_table.datecolumn_you_want_to_limit BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1) AND TRUNC(SYSDATE,'mm')-1/86400Marek Chladny (BOB member since 2003-11-27)
Hi,
As per your code you are saying that i have to choose a Universe_table and Date column.
But as per our requirment the user doesnot want to choose any table and column and create condition objects in universe using sysdate or oracle function.
This is where i am confused and not sure if that is possible.
Or there is way to do this in prompt or at reporter Level.
Thank you.
joshroy (BOB member since 2006-09-19)
You want to limit the data to current date or to last month. So I expect that you have any sort of date column in your universe (or database). Otherwise there is no trick how to limit data date-wise if you dont have a date.
How many such columns that hold dates do you have in the universe? Do you have a calendar table where these table.datecolumns are joined to? If yes then create the predefined conditions for “cuurent date” and/or for “last month” only based on the date field in the calendar table.
Then all date columns in all other universe tables will be limited with this condition as you have joins between them and the calendar table.
I hope you got the idea.
Marek Chladny (BOB member since 2003-11-27)
Hello,
True. I think in short i have to let the user know that I cannot create any condition object in Universe without using the table and column in the database .
We basically have lots of tables which have the date time information and i can join this table to the fact table and use this table/column to limit the data
e.g the way you wrote the code :
Schemaname.Tablename.columnname(date type) =TRUNC(SYSDATE)
But my user is insisting that i should be able to create these conditons objects using sysdate function.
joshroy (BOB member since 2006-09-19)
I dont understand 2 things here:
a) in the above example you ARE using sysdate in the condition.
b) more important, why do users have anything to say about your technical realization of their business requirement 
They should know nothing about sysdate or date or getdate(). They should require that the condition you create will limit the data to the current date. Why do they say you need to use sysdate? It is your responsibility how their business requirement is technically realized in the universe. My 2 cents.
Marek Chladny (BOB member since 2003-11-27)
No, that is not true.
Supposed you have 3 dates in your universe. You can create condition objects for each of them, and the user will pick. Obviously this is a problem if you have something more like 200 dates in your universe. And in this case you would have to reference a specific table and column. But…
Next option: build result objects (not condition objects) that return different date values, and have the users build their own conditions with those. For example, you might have an object called “Today” which has as the select clause:
trunc(sysdate)Note that this is an object and not a predefined condition. You can make Today, Yesterday, Start of This Month, End of This Month, and an entire variety of these based on sysdate.
Then the user will use those objects to make conditions. This will only work in full client, or in Webi XI as prior versions of webi did not offer this option. Suppose you have an object for Invoice Date. To make a condition that shows invoices entered yesterday the user would:
- Click the Invoice Date object and drag into the conditions area of the query panel
- Select Equal To as their operator
- Select “Select an Object” as the final step… when prompted, select the “Yesterday” object from the universe
In this way the user can create their own conditions based on dynamic dates. All you have to do is provide the building blocks. The user can use between, equal to, greater than… any operator that will work with date values.
Dave Rathbun (BOB member since 2002-06-06)
Hello,
Can you pls tell me a step wise process of buiding this as object in universe. As i tried and it won’t parse as there is no table to refernce it.
So i am not sure how can i do this as a Dimenasion Object.
Thank you
joshroy (BOB member since 2006-09-19)
The object does not have to parse, indeed, it should not parse. You can still use it as Mr Rathbun describe.
SonnyWinston 
(BOB member since 2005-04-07)
You got that answer in the very first (and correct) response in this topic 
Dave Rathbun (BOB member since 2002-06-06)
I have to interject.
All the responses to the “Object won’t Parse” questions have been correct becuase the object cannot parse without referring to a table.
The piece that has been missing is that you have to turn off the Parse on Definition option in designer.
You can turn it off, define your object, then turn it back on.
It will be a divergence in the list when you do an integrity check but since you know how that object is defined you’ll know to ignore the message.
chris_c (BOB member since 2006-01-10)
Hello,
I have similar kind of problem. I created an object called date for current date - Today() and it parses ok. When I pull only that object into the report it works fine but if I pull it with other objects or if I use in the conditions for example say ‘table.xyzdate >=date’ it results in cartisian product. Can anyone have idea what is wrong here. 
Thank you
5.1.8 FC Sybase.
cris (BOB member since 2006-10-17)
Did you pull in some dummy table (I do not know Sybase) such as the DUAL table in Oracle? Only this way an object such as Today would parse in the first place, but it introduces all kinds of problems such as having to join this dummy table to one of the other tables in you universe, this new join then needs to be added to contexts if you are using contexts that is and on and on and…
Recommendation/best practice:
Just accept that an object such as Today (using SYSDATE in Oracle, CURRENT DATE in IBM DB2/UDB, DATE in Teradata, etc.) will not parse in Designer instead of including a dummy table in your universe. This object will work fine in any data provider though as long as it is used in conjunction with other universe objects, which are properly based on a table.
This way you should not have any further issues.
Andreas 
(BOB member since 2002-06-20)