Sql in formula fields

[oguzhan]

Hello,
I have a problem and i will be vey happy if you share your comments and solutions with me.
I have a database that keeps telephone logs.I can get sum of all calls according to city codes.
I also have to take another report that calculates the price of these calls according to city codes.
So we created another table that consists city codes and price.
However this table was designed like that;

Code Price
for example //cellular 555 10$
//inside city 222 1$
//out of city 100 2$
//out of country 999 15$

here we have exact codes of cellular,inside city and out of country but we couldnt parse all city codes here.

i can succes this query like that i think.
if log.citycode 555 then time*price
if log.citycode 222 then time*price
if log.citycode 999 then time*price
else time*(select price from pricetable where citycode=100)

Off course i cannot do this from formula fields because of sql.

Thank You for your attention...

 

[MalcolmW]

1. You can use if-then-else in Crystal formulas, but the formula will not push to the server.
2. You can use CASE statements in the SQL Expressions fields, if your database supports CASE. This will push the result to the server.
3. You can use IIF or Switch functions in Crystal formulas instead of If-Then-Else, if you have version 8.x of Crystal reports. You would also have to use a SQL Expression for time*price to ensure the statement is pushed to the server. Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[oguzhan]

I am sure these will be very helpful to me but can you give me more specific examples?
For example the formula i coded was this:

If {LOG.CITYCODE} in {PRICE.CITYCODE}
Then Sum ({LOG.TIME}, {LOG.CITYCODE})* {PRICE.PRICE}
Else
(
{LOG.CITYCODE}='100' ;
Sum ({LOG.TIME}, {LOG.CITYCODE})* {PRICE.PRICE}
)

Of course because of " {LOG.CITYCODE}='100' " it did not work.But instead of this i cannot use a select statement here.

What can i do then?
Thank you for your attention...

 

[MalcolmW]

I thought you were interested in optimizing the performance of the formula. Let's start with getting the formula to work.
This formula will always return the result
Sum ({LOG.TIME}, {LOG.CITYCODE})* {PRICE.PRICE}
assuming that the report has a group based on {LOG.CITYCODE}, and that no null values are encountered. What do want this formula to do?
When you say {LOG.CITYCODE}='100' does not work, what do you mean? This expression is either true or false - are you trying to assign a value to a database field?

Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[oguzhan]

Yes, like an assignment.I know that wont work but i want to get a formula like this :

If

citycode in log table exist in price table take the price of citycode in price table and calculate the total price.

Else (I mean if citycode is an out of city code that does not exist in price table.In this situation out of city code is 100 that differs than the actual codes in log table)

take the price from price table that citycode is 100.and then calculate the price according to this.