BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

Max Effective Date


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
haggama
Senior Member
Senior Member



Joined: 21 Mar 2005

Posts: 43



PostPosted: Wed Nov 02, 2005 9:51 am 
Post subject: Max Effective Date

Hi, i've read a few posts on BOB but i'm still having difficulty on this one. I would like to do the following:

Create a Condition Filter in the Universe that returns the maximum effective dated row per EMPLID. My table is called

TB_BEN_ELIGIBILITY_HISTORY and the fields are

EMPLID, EFFDT, RBS_BENEFIT, RBS_BEN_ELIG_TYPE

Do I have to create an alias in the actual universe as well as putting the SQL code in the where part of the condition.

I'm using BO version 6.1

Thanks
Back to top
BO_Chief
Forum Fanatic
Forum Fanatic



Joined: 06 Jun 2004
ASUG Icon
Posts: 5541
Location: Somewhere on God's Land.


flag
PostPosted: Wed Nov 02, 2005 1:14 pm 
Post subject: Re: Max Effective Date

I think you don't need the ALIAS.

Those are used to resolve loops if you have any

If your database is ORACLE,
You can get the max. effective date as below:

Code:
SELECT max(EFFDT)
FROM TB_BEN_ELIGIBILTY_HISTORY
GROUP BY EMPLID


Put this in an object, that should do it. Keep in mind the above will return multiple rows...

_________________
help us help you! make sure your post has the following elements:
Does it include BO version, Database, an error, a problem, a SQL for object or Condition ?
Does it include some sample data what you have?
Does it include any code you already tried ? (working or not..)
Did you explain what you want for results?
If any of those elements are missing,chances are you didn't post enough information for us to help you!
Back to top
multicube
Principal Member
Principal Member



Joined: 28 Sep 2005

Posts: 134
Location: Saint Louis


flag
PostPosted: Wed Nov 02, 2005 1:34 pm 
Post subject: Re: Max Effective Date

See if the following works for you...

Quote:
Do I have to create an alias in the actual universe as well as putting the SQL code in the where part of the condition.


No Need of Alias...just copy the following code in where part, and see if it works..
Code:
TB_BEN_ELIGIBILITY_HISTORY.EFFDT IN
(SELECT
  Max(A.EFFDT)
FROM
  TB_BEN_ELIGIBILITY_HISTORY A
WHERE
A.EMPLID = TB_BEN_ELIGIBILITY_HISTORY.EMPLID
)


Thanks
Back to top
haggama
Senior Member
Senior Member



Joined: 21 Mar 2005

Posts: 43



PostPosted: Thu Nov 03, 2005 3:31 am 
Post subject: Re: Max Effective Date

Thanks, I've got this to work now. The problem was that I was already using the table in question for something else and was trying to use an alias of it in this instance. I simply swapped them over, i.e. put the subquery on the real table and use the alias for the other view

haggama icon_biggrin.gif
Back to top
shaffner
Senior Member
Senior Member



Joined: 22 Jul 2004

Posts: 61
Location: Fannie Mae - Dallas, TX


flag
PostPosted: Mon Nov 21, 2005 2:54 pm 
Post subject: Re: Max Effective Date

haggama wrote:
Thanks, I've got this to work now. The problem was that I was already using the table in question for something else and was trying to use an alias of it in this instance. I simply swapped them over, i.e. put the subquery on the real table and use the alias for the other view

haggama icon_biggrin.gif


Did you code this into the Where clause of a Predefined Condition or did you create a complex join and code it into the Expression for that join?

Can you provide the complete syntax for the solution you got to work, please? I need to accomplish precisely the same thing. Thanks for doing all the heaving lifting already!

SHAFFNER
Back to top
haggama
Senior Member
Senior Member



Joined: 21 Mar 2005

Posts: 43



PostPosted: Tue Nov 22, 2005 3:54 am 
Post subject: Re: Max Effective Date

Hi, what I did was created a self join on the table using the expression below:

TB_BEN_ELIGIBILITY_HISTORY.EFFDT= (SELECT MAX(ben1.effdt) FROM TB_BEN_ELIGIBILITY_HISTORY ben1 where ben1.emplid = TB_BEN_ELIGIBILITY_HISTORY.emplid and ben1.effdt <= sysdate)

Hope this helps
Back to top
shaffner
Senior Member
Senior Member



Joined: 22 Jul 2004

Posts: 61
Location: Fannie Mae - Dallas, TX


flag
PostPosted: Tue Nov 22, 2005 2:56 pm 
Post subject: Re: Max Effective Date

Thank you. This worked well for us. I was having problems because, using your example code, I was errantly creating the join Expression starting with TB_BEN_ELIGIBILITY_HISTORY.EMPLID = instead of TB_BEN_ELIGIBILITY_HISTORY.EFFDT.

Without that snipit of code, I doubt I would have stumbled upon what I was doing wrong. Now I understand. Thanks again.
Back to top
Maria_2
Principal Member
Principal Member



Joined: 17 Jan 2007
ASUG Icon
Posts: 118



PostPosted: Thu Mar 28, 2013 8:09 am 
Post subject: Re: Max Effective Date

Hi All,

I tried the same but this logic doesn't work with ANSI92 set as Yes/Enabled.

Please advice if somebody able to achieve it

Thanks,
Sheriey
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0455 seconds using 17 queries. (SQL 0.0027 Parse 0.0340 Other 0.0087)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo