I need a conditional based on 2 separate tables in Oracle

[DPlank]

Hi.

I want to create a formula that will :

Read a list of Id numbers, check these for a status and those that it finds, it should check the second table for the date the status was changed. It should return only those where the CurrentDate is 2days ago or more.

I currently have :

{HISTORY.HS_CHANGE_DATE} <( CurrentDate-2) and
{BUG.BG_STATUS} = &quot;Retest&quot;

set up through the select expert on the field which contains the ID numbers.

Anyone got an answer ?

Thanx in advance
David

 

[herbsza]

I have made the following assumptions.

1. that the history table has a id field.
2. In the visual linking expert that the two tables are linked either with a equal join or a left join
on the id field

Table bug Table History

bug.id ---------------->history.id
bug.status history.HS_CHANGE_DATE

The Select Expert

{BUG.BG_STATUS} = "Retest" and
{HISTORY.HS_CHANGE_DATE} <= (CurrentDate() - 2)

I hope this of some help.

 

[DPlank]

I've tried it but I'm now getting an Oracle Database error when I try to preview the report:
ODBC Error : [Oracle][ODBC][ORA]ORA-01722 : invalid number.

On clicking OK, I get a dialog box advising SQL Server Error.

this leaves me with a blank preview page. I can then switch back to Design, or repeat the above...

Any further ideas?

All assistance appreciated.
David

 

[Ngolem]

{BUG.BG_STATUS} = &quot;Retest&quot; and
{HISTORY.HS_CHANGE_DATE} <= (CurrentDate() - 2)

I think I know your problem....is {HISTORY.HS_CHANGE_DATE} in a date format or is it a dateSerial?

You are passing it a date...and the db is expecting a number



Jim Broadbent

 

[DPlank]

Sorry, it's not a dateSerial.

I wasn't too sure so I've exported the data in Excel.

Looks like it's a dateTime value.

Does this make much of a difference?

David

 

[Ngolem]

yes it does....I think you can just go to the Report|options and convert DateTime to Date as a default

That may help....but are you sure it is a real date???

Show me what the Excel value looks like

Jim Broadbent

 

[DPlank]

19/03/2003 00:00
is what it appears as in excel.

DateTime ??

 

[herbsza]

try this

date({HISTORY.HS_CHANGE_DATE}) <= (CurrentDate() - 2)

 

[lyanch]

If it is an oracle timestamp datatype, you must convert it to a date type (in SQL Expression) before you can use it in Crystal. Crystal doesn't correctly identify the timestamp datatype.

Lisa

 

[DPlank]

Thanks for all your help so far guys (and gals)

I still have been unable to get this to work. I'm uncertain if the field on HISTORY is a dat or datetime field. I've tred to view it in the formula editor using browse field data and it comes back with a list of dates in the format Date(year, month, day).

The field when viewed through an SQL Query shows the value in the format : Day/Month/Year.

Is the format incompatible?

OK to recap : I've set both the BUG ID and the HS Key fields to be linked.

I've modified the formula as suggested and have tried coverting the bug id and the hs key fields ToText so I can compare them first.

The problem seems to be in the second condition :
the invalid number error seems to be constantly a problem.

The third statement simply specifies that the only ID numbers returned should have status Retest. The formula is :

totext({HISTORY.HS_KEY}) startswith ( totext  ({BUG.BG_BUG_ID})) and
{HISTORY.HS_CHANGE_DATE} <= (CurrentDate) - 2 and
{BUG.BG_STATUS} = &quot;Retest&quot;

I've tried different places with brackets and all, but it seems to have no effect. If I precede condition 2 with Date and enclose the field in brackets, I get an error telling me not enough parameters for the function. Seems to be treating my field as an eplicitly defined date.

Once again, over to you peeps with the brains...Thanks again.
David

 

[lyanch]

Go to field explorer, right click anywhere in the &quot;whitespace&quot; and make sure that &quot;show data type&quot; is checked. Browse to HISTORY.HS_CHANGE_DATE and see what datatype Crystal thinks it is..

That should start getting us in the right direction.

Lisa

 

[DPlank]

Ermmm. I don't think I have a Field Explorer. Whenever I right click on any whitespace, I get a Dr Watson error and the system falls over.

I think this is because I'm reeeaaallllly behind the times and using v5 Professional....