If/Then in a Custom Field Select Statement | IFS Community
Question

If/Then in a Custom Field Select Statement

  • 22 June 2022
  • 2 replies
  • 41 views

Userlevel 1
Badge +5

I am trying to add field Approved_By_UserId from  views ‘Gen_Led_Vouchers2’ or ‘Voucher’ to as a custom field on ‘Supplier Invoices Analysis’, ‘Multi-Company Invoices Analysis’, and ‘AP Suppler Invoices Analysis’.

Basically if the field is null in gen_led_vouchers2 i can get it from transactions in the hold table.

But I have never seen an IF/Then in a custom field select statement. :-/

Can this be done and does anyone have an example?

 

Thanks

Cindy

 


2 replies

Userlevel 6
Badge +12

Sounds like a use case for COALESCE or NVL -- if one field is null, take the other. You would still have to perform a query to link to both tables, though so that both values could be checked. That might be processor-intensive since it still needs to do all the work.

Oracle doesn't have anything like an IIF() (immediate IF) keyword, but you can read the documentation on DECODE() and CASE expressions for that sort of processing.

Thanks,
Joe Kaufman

Userlevel 4
Badge +9

Hi

You can create a read only field with SELECT CASE statement.

 

SELECT

(CASE 

WHEN t.field = null THEN ‘output_value1’

WHEN t.field = x THEN ‘output_valu2’

ELSE

‘output_default’

END) Custom_field_output

FROM gen_led_vouchers2 t

I am not sure if this satisfies your requirement. But this is how you write if/else in a select statement. 

 

Thanks

Best Regards

Nadeesha Liyanage

Reply