Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Vaishali mehtaVaishali mehta 

Hi,I am new to salesforce. Wanted to know that, is there any functionality in SOQL that can work similar to "NVL" functionality in SQl?

Hi,I am new to salesforce. Wanted to know that, is there any functionality in SOQL that can work similar to "NVL" functionality in SQl? 
Best Answer chosen by Vaishali mehta
Shashikant SharmaShashikant Sharma
Hi Vaishali,

Yes your assumption is correct  and you need to use both ISNULL and ISBLANK. Try this,
  
IF( OR( ISNULL( Field1 ), ISBLANK( Field1 ) ),  
      IF( OR( ISNULL(Field2), ISBLANK( Field 2) ), 
           Field3, 
           Field2 ),
      Field1 )

Thanks
Shashikant

All Answers

Shashikant SharmaShashikant Sharma
Hi Vaishali,

No there is no way to achive it in SOQL like in SQL using NVL but you can achieve it with below alternative 

1. Create a Formula Field where you could set up conditions like NVL in SQL and get the desired out put from formula field
2. Query the Formula Field in SOQL and it will give you appropriate results

Now the only problem here would be that if you want many different conditions on many fields you might need to add more formula fields in such case you have achive it by writing down some kind of Apex method where you can pass the results returned by SOQL and get the desired out put for that field.

Thanks
Shashikant
Vaishali mehtaVaishali mehta
Thanks shashikant, I got your point.
I am trying to achieve my functionality using your suggestion.

Can you please tell me how can i  concatenate output from multiple fields in soql?
Will the formula field be useful for this?
Vaishali mehtaVaishali mehta
I got the answer for concatenation issue. !!
Tried ur suggestion, facing little problem. Please see if you can help:

I am trying 
IF(ISNULL( Field1 ),  Field2, Field3). I guess, it should return Field2 if the field1 is blank, else it should return field3.
But it is giving field2 everytime. Can you tell me what is wrong?
Shashikant SharmaShashikant Sharma
You could try this
  
IF( ISNULL( Field1 ),  
      IF( ISNULL(Field2), Field3, Field2 ),
      Field1 )
This should work for your need.

Thanks
Shashikant



 
Vaishali mehtaVaishali mehta
Hi shashikant,

It is  still giving the same result.I am stating my requirement, please suggest something:

1) There are two fileds, say Field1 and Filed2.
2) I want the formula to return Filed2 is the Field 1 is null.
3) If the filed1 is not null, the formula should return field1 itself.

I have tried this using the above suggested formula, but even if the field1 is blank, the final outcome is not filed2, rather i am getting the blank output.

I guess it is returning filed1 in every situation. Maybe,  it is not recognising the blank field as Null value.?

 
Shashikant SharmaShashikant Sharma
Hi Vaishali,

Yes your assumption is correct  and you need to use both ISNULL and ISBLANK. Try this,
  
IF( OR( ISNULL( Field1 ), ISBLANK( Field1 ) ),  
      IF( OR( ISNULL(Field2), ISBLANK( Field 2) ), 
           Field3, 
           Field2 ),
      Field1 )

Thanks
Shashikant
This was selected as the best answer
Vaishali mehtaVaishali mehta
It worked, Thanks a lot Shashikant. 
:) :)
Shashikant SharmaShashikant Sharma
Glad that it worked for you :)