how select statement

[DanHD]

hello

I try to create in a formula field a select statement, because I only need a select on that field.
Like:

select INV1.Price
from INV1
where INV1.ItemCode = '98'

this is not working. What can I do?

Dan

 

[briangriffin]

Need more info, I think.

If you want the formula to return the price if the item is 98, and no price if it's not item 98, then:

if INV1.ItemCode = '98' then INV1.Price else 0

You could also use a case statement, but in this case there's no real benefit.

You would probably want to suppress the 0's for the non-item 98 records.

If I'm not interpreting your request correctly then please post more specific info.

 

[fisheromacse]

For a SQL Expression, use the following:
(
Select Price
from INV1
where "INV1"."ItemCode" = '98'
)


For Select Expert use the following:
{INV1.ItemCode}="98"

 

[DanHD]

thanks both of you, the first option is working correct.
But if i using the SQL Expression i receive the error "the ) is missing" and the word 'from' is highligthed.

Dan

 

[fisheromacse]

it is likely a quotation syntax error on my part. Crystal gives some misleading error dialogs.

 

[lbass]

The SQL expression would also error out unless it returned only one value per the criteria.

-LB

 

[fisheromacse]

thanks lb, i forgot about that!

 

[DanHD]

oke, nice that you're both agree ;-)
But when i saved it with ignoring the error, I still get the error by running the report.
So what I've to do for running a sql statement in crystall?

kind regards
Dan

Dan

 

[lbass]

You should just use Brian's suggestion (field explorer->formula->new) if you only want to display prices for that code, but still need other codes in your report:

if {INV1.ItemCode} = '98' then
{INV1.Price}

If you don't need any other codes in the report, then use a selection formula like this:

{INV1.ItemCode} = '98'

-LB

 

[DanHD]

oke thanks
can we close threads by our selves?

Dan

 

[lbass]

Threads are only closed after a certain period of time with no new responses--so no need to be concerned about this.

-LB