3

Just trying to do a simple case function in powerbuild.

I have a table client_notes A!, B!, C!

case(client_notes when '%A!%' then 'Cash1' else "Cash2")

It compiles OK but when I run it, it says Cash2.

Shouldn't it say Cash1?

jjohnson
  • 203
  • 1
  • 5
  • 16

3 Answers3

2

What's the context here? Is this in PowerScript or in a datawindow expression, or in the SQL source of the datawindow?

And what version/build of PB are you using?

What it should "say" [sic] all depends on the value of "client_notes" at runtime. It will only return the string 'Cash1' when the value of client_notes is exactly equal to the string '%A!%'.

What set of data are you running this against? Show some sample data.

-Paul Horan-

NoazDad
  • 608
  • 3
  • 9
  • 1
    Im pretty new to all this but I believe it is in a datawindow expression, and im using PowerBuild 7.0. Some sample data would be : A!, B!, C!, D! this is what is written in the client notes. And what I would like to do is have A! mapped to Cash, B! mapped to Check, C! mapped to Money Order and D! mapped to Card. So if in the client notes its "A!, C!, D!" I want the out come to be like "Cash, Money Order or Card" – jjohnson Dec 22 '14 at 19:27
  • 1
    Remove the '%' from your statement "when '%A! then..." – Matt Balent Dec 23 '14 at 12:22
  • I don't think Matt's suggestion will give you what you want either... You're essentially writing a custom parser, and if you try and do this in a datawindow expression, you'll quickly paint yourself into a corner. What logic would put the ' or ' in there? What if you want to add a fifth or sixth new option? This is the downside of using multi-valued columns instead of doing this as a 1-m associative entity. I would probably write this into the RetrieveRow event and do it in powerscript. It will be significantly easier and more maintainable. – NoazDad Dec 23 '14 at 21:03
  • 1
    What would that powerscript looks like? – jjohnson Dec 23 '14 at 21:08
0

I have never seen a "LIKE" expression used in the expression painter but it might be possible. I gave it a quick try and wasn't able to. I would agree with what Matt said, except your expression doesn't have the important "LIKE" keyword so it is matching exactly on your string and of course not matching so always getting Cash2.

I've wanted to use > or < in CASE statements before and had problems too, the WHEN seems to be pretty 'dumb' and not able to handle anything other than a value.

This works not as elegant as a case statement though.

if ( client_notes like '%A!%' , 'Cash1', 
   if ( client_notes like '%B!%' , 'Cash2', 
      if ( client_notes like '%C!%' , 'Cash3', 
        'Cash?' ) ) )

What programmer doesn't like a challenge? None of these work but you probably get the thought process I was going through. 

case ( ('%' + client_notes + '%') when (like 'A1') then 'Cash1' else 'Cash2' )
case ( '%A1%' when (like 'A1') then 'Cash1' else 'Cash2' )
case ( ( '%bbb%' like 'A1' ) when true then 'Yah' else 'Nah' )

No cigar on CASE...

You could use a global-function, but then again you could write a database function too and it doesn't really solve what you wanted to do. I recall wanting to use like inside a CASE statement more than once and I don't recall getting it to work. I can also recall trying to do something like case variable_a when '4:20' then 'Celebrate' when > '4:20' then 'Too late' else 'Later'

This compiles but do not think it would work due to the comparison variable needing to be different.

case( if ( client_notes like '%' + client_notes + '%', client_notes , 'X') 
    when 'A1' then 'Cash1' 
    when 'A2' then 'Cash2' 
    else 'use nested if instead')
Rich Bianco
  • 4,141
  • 3
  • 29
  • 48
0

Well, if you wanted to try it as a datawindow computed field, you could use the "brute force" method.

CASE (client_notes  
WHEN 'A!' THEN 'Cash'  
WHEN 'A!, B!' THEN 'Cash, Check'  
WHEN 'A!, B!, C!' THEN 'Cash, Check, Money Order'  
WHEN 'A!, B!, C!, D!' THEN 'Cash, Check, Money Order, Card'  
WHEN 'B!' Then 'Check'  
...  
Else '')

In the RetrieveRow event, it might look like this: 

string ls_result = ''  
string ls_client_notes  

If row > 0  then  
   ls_client_notes = this.getItemString( row, 'client_notes')  

   If pos( ls_client_notes, 'A!' ) > 0  then  ls_result = 'Cash'  
   If pos( ls_client_notes, 'B!' ) > 0  then  
      If len( ls_result ) > 0  then  
         ls_result += ', Check'  
      Else  
         ls_result = 'Check'  
      End if  
   End if  
   If pos( ls_client_notes, 'C!' ) > 0  then  
      If len( ls_result ) > 0  then  
         ls_result += ', Money Order'  
      Else  
         ls_result = 'Money Order'  
      End if  
   End if  
   If pos( ls_client_notes, 'D!' ) > 0  then  
      If len( ls_result ) > 0  then  
         ls_result += ', Card'  
      Else  
         ls_result = 'Card'  
      End if  
   End if  

   this.setItem( row, 'client_notes', ls_result ) // if you want it back in the same column  

End if

-Paul-

NoazDad
  • 608
  • 3
  • 9