Nested case in simple formula
2 February, 2012
Hi,
How I can use nested "case when" in simple formula in calculated field? I have used,
CASE WHEN Append: MileageField > 0 THEN WHEN Max ActualFuelConsumed > 0 THEN Max ActualFuelConsumed / 3.785 END
but it is giving me error "SQL is invalid."
Thanks,
Gaurav
How I can use nested "case when" in simple formula in calculated field? I have used,
CASE WHEN Append: MileageField > 0 THEN WHEN Max ActualFuelConsumed > 0 THEN Max ActualFuelConsumed / 3.785 END
but it is giving me error "SQL is invalid."
Thanks,
Gaurav
Hi Gaurav,
I think you can try something like this:
[code]
CASE
WHEN append:mileagefield > 0 AND MAX ActualFuelConsumed > 0
THEN MAX ActualFuelConsumed / 3.785
END
[/code]
Please let me know how you go.
Regards,
David
I think you can try something like this:
[code]
CASE
WHEN append:mileagefield > 0 AND MAX ActualFuelConsumed > 0
THEN MAX ActualFuelConsumed / 3.785
END
[/code]
Please let me know how you go.
Regards,
David
Hi David,
Thanks for reply.
I know the above solution but I had sent you only small snap of my conditions but it was little bit complex. So I need nested looping for other conditions.
How I can do that? Can I use case in between other case?
Thanks,
Gaurav
Thanks for reply.
I know the above solution but I had sent you only small snap of my conditions but it was little bit complex. So I need nested looping for other conditions.
How I can do that? Can I use case in between other case?
Thanks,
Gaurav
Hi Guarav,
When creating calculated fields at the report level, you cannot nest a case statement within a case statement.
However at the view level you can create a freehand sql calculated field or virtual table so you can enter in SQL directly.
Regards,
David
When creating calculated fields at the report level, you cannot nest a case statement within a case statement.
However at the view level you can create a freehand sql calculated field or virtual table so you can enter in SQL directly.
Regards,
David
Hi David,
I have report which contains the sub queries fields and I think, these appended fields I could not access at the view level. I am not able to use the appended fields into the custom functions as well.
Thanks,
Gaurav.
I have report which contains the sub queries fields and I think, these appended fields I could not access at the view level. I am not able to use the appended fields into the custom functions as well.
Thanks,
Gaurav.
Hi Gaurav,
Is this due to the sub-query being based on a separate view?
If so, you will need to think of creating a freehand SQL report, as you can then use fields and calculations freely without limitations.
Regards,
David
Is this due to the sub-query being based on a separate view?
If so, you will need to think of creating a freehand SQL report, as you can then use fields and calculations freely without limitations.
Regards,
David