+ not working

[priya@90]

Hello Friends,

I am trying to do summation for 3 data items as single data item in my list report but it's not working, what could be the reason iam not getting. Any idea please.

Data item1 from table its having Abc, bcd ,xyz some codes +
data item2 from is having case statement like if( lifestyle='Ame' then null else lifestyle) +
dataitem3: if (affiliated='Y' then 'GF' else null

date item 4=data item 1+2+3

i tried using concatination in both ways (||'-'||) (&+'-'+) but its working

expected output is:data item 2 and 3 are null then it should be data item1.

Thanks,

[Francis aka khayman]

by "not working" do you mean you are getting an error? if so, what is the error?

or, are you getting an output which is not what you are expecting? if so, what are the values you have, what is the output you are getting and what is the expected output?

[priya@90]

by "not working" do you mean you are getting an error? if so, what is the error?

or, are you getting an output which is not what you are expecting? if so, what are the values you have, what is the output you are getting and what is the expected output?

I am getting blanks in my out put.

I need to display data item 1 as expected output when data item 2 &3 satisfied that means data item 2 is null and dta item3 is n ull
data item 4=data item+data item2+data item3 (final output: Abc+null+null=Abc)

[Francis aka khayman]

adding anything to null will result to null. null is not the same as zero. better do an IsNull check...

If (data item1) is null then ('') else (data item1)


data item 4 = data item1 + data item2...

[priya@90]

Hi,

I got the issue. Data item 2 and 3 are varchar's so it's not considering nulls so I kept ('0') and ('1') instead of nulls so it's giving as expected data but its giving like this Abc-0-1 . What to do next, any idea would be appreciated. Is that good approach to do substring? Or any ideas



Thanks,
Priya

[Francis aka khayman]

are you trying to add or concatenate? not really sure what you are doing now

[priya@90]

are you trying to add or concatenate? not really sure what you are doing now

Hi,

I am trying to 'add' only not cocatination.



Sent from my CPH1859 using Tapatalk

[sdf]

Sorry, but it seems like your requirement is more on Concatenation rather than addition.
You cannot 'add' characters, that's a mathematical procedure. Let's get that straight.

I have not tried this but you can try.
First, instead of null, can you use (' ') (space) instead. like what khayman proposed :

If (data item1) is null then ('') else (data item1)

Then, there's an oracle function I want you to try :

concat( concat([dataitem1],[dataitem2]), [dataitem3])

again.. just giving you options you can try.

[dougp]

You're trying to add strings?  I've never seen that.  My calculator has numbers, not letters.

You did not state what RDBMS you are using.  I use Microsoft SQL Server, so the rest of this post is from that perspective.


I suspect you are trying to concatenate strings.  Something like this:

"I can" + " " + "concatenate" + " strings." = "I can concatenate strings."

If that's the case, and some of your values are null, you'll get null as a result.

"a" + NULL + "B" = NULL

You'll need to use the coalesce() function.

If column_a contains 'Hello', column_b contains ' there', and column_c contains NULL...

Code Select Expand

coalesce(column_a, '') + coalesce(column_b, '') + coalesce(', ' + column_c, '') + '.'

will return "Hello there."  (because ', ' + NULL = NULL, so the third term returns '')

If column_c contains 'Priya', it will return "Hello there, Priya."

[hespora]

Shame on IBM for overloading the '+' operator! :)

[dougp]

IBM?  I'm pretty sure Microsoft makes SQL Server.

[hespora]

True, but Cognos allows the same syntax of using + for concatenation.

[sdf]

same thing in tableau and some other tool that utilizes sql.
others use &.

[cognostechie]

Whenever + doesn't work, I use a - and it works !  ;)

[priya@90]

You're trying to add strings?  I've never seen that.  My calculator has numbers, not letters.

You did not state what RDBMS you are using.  I use Microsoft SQL Server, so the rest of this post is from that perspective.


I suspect you are trying to concatenate strings.  Something like this:

"I can" + " " + "concatenate" + " strings." = "I can concatenate strings."

If that's the case, and some of your values are null, you'll get null as a result.

"a" + NULL + "B" = NULL

You'll need to use the coalesce() function.

If column_a contains 'Hello', column_b contains ' there', and column_c contains NULL...

Code Select Expand

coalesce(column_a, '') + coalesce(column_b, '') + coalesce(', ' + column_c, '') + '.'

will return "Hello there."  (because ', ' + NULL = NULL, so the third term returns '')

If column_c contains 'Priya', it will return "Hello there, Priya."

Hello dougp, thanks for your suggestion,your idea is also working

Sent from my CPH1859 using Tapatalk

[priya@90]

Sorry, but it seems like your requirement is more on Concatenation rather than addition.
You cannot 'add' characters, that's a mathematical procedure. Let's get that straight.

I have not tried this but you can try.
First, instead of null, can you use (' ') (space) instead. like what khayman proposed :
Then, there's an oracle function I want you to try :

concat( concat([dataitem1],[dataitem2]), [dataitem3])

again.. just giving you options you can try.

Hello Sdf,

Thanks for your suggestions and help. I have applied your idea in my formula.

Thanks

Sent from my CPH1859 using Tapatalk