How to pull Previously entered value

[CrystalProgDev]

I have a dataset

ID Num Type
1 123 C1
2 123 NULL
3 123 A3
4 123 A1
5 123 NULL

I need to have previous ID's Type, If maximum ID Type is NULL. That is

ID Num Type
1 123 A1
2 123 A1
3 123 A1
4 123 A1
5 123 A1 as output in the report.

Can any one please help me.
Thank you in advance.

 

[lbass]

First, create a SQL expression {%maxID} like this:

(
select max(`ID`)
from table A
where A.`num` = table.`num` and
A.`type` is not null
)

Adjust punctuation according to your datasource.

Then create a formula:

//{@maxIDtype}:
if {table.ID} = {%maxID} then
{table.type}

Then create the following formula for display output:

maximum({@maxIDtype},{table.num})

The above assumes a group on {table.num} and that you always want to display the type of highest ID with a non-null type.

-LB

 

[CrystalProgDev]

Thank you for the response. I am using a stored procedure... SQL Expression Fileds option is not available.... any other solution to solve this issue....
Thank you

 

[lbass]

Create the equivalent of the SQL expression within the stored procedure as a subquery.

-LB

 

[CrystalProgDev]

I tried.... I am not good at SQL so I tried in Crystal reports it self... and also the performance of the query is too poor...
I need to consider 3 scenarios here. my query is returning...

123 C1 1/1/2010
123 A1 1/1/2010
123 A3 1/1/2011
123 NULL 10/1/2010
123 NULL 11/1/2010

124 NULL 10/1/2010
124 NULL 1/1/2010

125 NULL 1/1/2011
125 A1 1/1/2010
125 NULL 11/1/2010

I need to have previous ID's Type, If maximum Date is NULL. Else NULL

For 123, I need to have A3 and the latest date
123 A3 11/1/2011

for 124 --- 124 NULL 10/1/2010
for 125 -- 125 A1 11/1/2010


Can any one suggest me the solution.

Thank you
Shyamala

 

[lbass]

So you ARE able to use a SQL expression? You also seem to be changing the desired display--now it seems you only want to return one record per ID?

-LB