No data

[funkmonsteruk]

Hi,

I'm currently running a bo query which aims at including all <names> where <date 1> is at least 30 days after <date 2>. If i include <name> and either <date 1> or <date 2> i get a list of results, however if i include <name> and both date variables the i get no data.

Both date variables refer to the same field in a table, but have different events attached to them (i have done this using the Where function at universe level).

So i guess i need two bits of advice if anyone can help - how to report on both dates and how to set a condition were only <name>'s where 30 days between the two date variables are included............

Can anybody help

Funkmonsteruk.....

 

[PAULSGS]

Can you post the sql the query generates here?

I suspect that you are 'AND'ing the two dates and no records can match that situation

 

[funkmonsteruk]

Cheers Paul, checked SQL your right was AND'ing the query, have set to do not generate SQL and changed it to OR.
Here is the new SQL, any ideas on the other problem?

SELECT
COALESCE(STRIP(LLPSC01U.PSC_ESTA.SRNM) || ', ' || STRIP(COALESCE(LLPSC01U.PSC_ESTA.FRNM,'')) , STRIP(LLPSC01U.PSC_ESTA.SRNM) ) ,
DIRY_ESTA2.DIRY_TS,
DIRY_ESTA.DIRY_TS
FROM
LLPSC01U.PSC_ESTA LEFT OUTER JOIN LLPSC01U.PSC_DIRY DIRY_ESTA ON LLPSC01U.PSC_ESTA.ESTA_COD=DIRY_ESTA.ESTA_COD LEFT OUTER JOIN LLPSC01U.PSC_DIRY DIRY_ESTA2 ON LLPSC01U.PSC_ESTA.ESTA_COD=DIRY_ESTA2.ESTA_COD
WHERE
( DIRY_ESTA.EVNT_COD='GRANT' )
OR ( DIRY_ESTA.EVNT_COD='REVIEW' )

 

[PAULSGS]

Well, what horrible Sql!!! ;} I am only familiar with OracleSpeak - what is your DB?

I shan't attempt to ammend it as shown, but from your problem description isn't the pseudo sql that you want something like this. The important bit is the last line. You have to express your written requirement

&quot;including all <names> where <date 1> is at least 30 days after <date 2>&quot;

as SQL


Select Name
From Tab1,Tab2,Tab3
Where <your joins>
and date1 >= date2 + 30

 

[skrandel]

You're mistake is that you can't use the where statements on the objets. Those rarely work. What you need is a calendar table that you alias and join to the field in question. You should have 1 alias for each role that the date plays.

Bottom line, you may not be able to do this at the universe level. Perhaps you should just have a condition object that holds the logic the way you need it. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com