AND/OR formula

[leanneconnexions]

This is returning a null dataset. I know it should return one at least as I have a record that contains FD and F1. What is wrong with the formula? In my dataset I need all history categories D and where there is also 0 or 1 or 2 etc.
HELP! ;-)

{CR_HISTORY.HistoryType_Ref} = 'F' and
{CR_HISTORY.HistoryCategory_Ref} = 'D' and
{CR_HISTORY.HistoryCategory_Ref} = '0 or 1 or 2 or 3'

 

[leanneconnexions]

That is correct. Yes for every client ref i only want to see where the Category = D and at least one other Category of 0-5 for that client. As in AINDJ00 is the sample data.

 

[MisterMo]

then you where asking the wrong question

this is more to do with SQL than CR

try asomething like this

select * from your_table T1
where HistoryType = 'F'
and HistoryCategory = 'D'
and Client_Ref =(select Client_Ref from yortable T2
where HistoryType = 'F'
and HistoryCategory in ('0','1','2','3')
union
select * from your_table T1
where HistoryType = 'F'
and HistoryCategory in ('0','1','2','3')
and Client_Ref =(select Client_Ref from yortable T2
where HistoryType = 'F'
and HistoryCategory = 'D'

Mo

 

[leanneconnexions]

I get the following error from query analyser:

Server: Msg 8163, Level 16, State 4, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.

 

[MisterMo]

that is because is finding more than one record
try 'in' instead of '='


select * from your_table T1
where HistoryType = 'F'
and HistoryCategory = 'D'
and T1.Client_Ref in(select T2.Client_Ref from your_table T2
where T2.HistoryType = 'F'
and T2.HistoryCategory in ('0','1','2','3'))
union
select * from your_table T1
where HistoryType = 'F'
and HistoryCategory in ('0','1','2','3')
and T1.Client_Ref in(select T2.Client_Ref from your_table T2
where T2.HistoryType = 'F'
and T2.HistoryCategory = 'D')

Mo

 

[kskid]

In CR 8.5, I am able to use the following

In your main record selection, you should have

{CR_HISTORY.HistoryType_Ref} = 'F' and
{CR_HISTORY.HistoryCategory_Ref} = 'D'

From Database->Show SQL Query, add the following to the end

and exists
(
select *
from CR_History Hist
where HIST.HistoryType_Ref = 'F'
and HIST.HistoryCategory_Ref in ['0','1','2','3']
and HIST.CLient_ref = CR_HISTORY.CLient_ref
)

-lw

 

[MisterMo]

That is no viable in 9,10,XI

Mo

 

[leanneconnexions]

unable to do:

and exists
(
select *
from CR_History Hist
where HIST.HistoryType_Ref = 'F'
and HIST.HistoryCategory_Ref in ['0','1','2','3']
and HIST.CLient_ref = CR_HISTORY.CLient_ref
)

 

[leanneconnexions]

when using In in sql i still get the error as above: The text, ntext, or image data type cannot be selected as DISTINCT.

 

[kskid]

Is there an alternate solution for 9, 10, XI? I have several reports that are created this way. I am in the process of testing and moving my 8.5 reports to XI.

I did noticed that in CRXI Database->Show SQL Query is frozen from inserting anything and haven't explored the Add Command section yet.

-lw

 

[kskid]

Sorry. Change the brackets to Parenthesis

and exists
(
select *
from CR_History Hist
where HIST.HistoryType_Ref = 'F'
and HIST.HistoryCategory_Ref in ('0','1','2','3')
and HIST.CLient_ref = CR_HISTORY.CLient_ref
)

By the way, since you did not mention the database type, I am assuming an Oracle database based on other posts.

 

[leanneconnexions]

using SQL database and crystal 9

 

[GJParker]

kskid

yuo are right the Add command option is the way to go from version 9 onwards.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

An alternative approach would be to add the History table twice, linking on client_ref with an equal join. Then use a record selection formula of:

{CR_HISTORY.HistoryType_Ref} = 'F' and
{CR_HISTORY.HistoryCategory_Ref} = 'D' and
{CR_HISTORY_1.HistoryCategory_Ref} in ["0","1","2","3","4","5"]

-LB