Combine two fields into one field

[snufse]

I have a query that needs to be extended. The scope is following:

I am selecting field GLSUB. In addition I need to select the field GLALTY if there is a value. GLSUB and GLALTY need to be combined into one field with a slash between.

Example: GLSUB = 01004, GLALTY= AV, Result needs to be = 01004-AV. I only need to do this if field GLALTY have a value.

Not sure how to do this. Thank you.

select  glsub, sum(decimal(glu * .01, 31, 2)), glum               
from vgiprddta/f0911li                                            
where glmcu = @jobnumber and                                      
(globj = '63500 ' or globj = '73500 ' or globj = '73510 ') and    
(gllt = 'AA' or gllt = 'AU') and                                  
glexa = 'Equipment Distribution'                                  
group by  glsub, glum;

[Mercury]

Re: Combine two fields into one field

Assuming GLSUB is a char fld, else use TRIM(CHAR(GLSUB)) instead.

Select TRIM(GLSUB) concat ' /' concat GLALTY ...
Where GLALTY  <> " " ... 

[snufse]

Re: Combine two fields into one field

Query not working corectly yet:

select  trim(glsub) concat '-' concat glalty as costcode,          
           sum(decimal(glu * .01, 31, 2)) as sum,                     
           glum as uom                                                
from vgiprddta/f0911li                                             
where glalty <> ' ' and                                            
           glmcu = @jobnumber and                                       
           (globj = '63500 ' or globj = '73500 ' or globj = '73510 ') and     
           (gllt = 'AA' or gllt = 'AU')                                       
group by  glsub, glalty, glum;

I just need to concat if field GLALTY with GLSUB if GLALTY <> ' ' otherwise not.


Example:

In the table there are following records:

GLSUB = 01004 GLALTY = blank
GLSUB = 01004 GLALTY = AV

Running above query I only get records where GLALTY <> ' ' ie
01004-AV (I also need 01004)

[SteveL]

Re: Combine two fields into one field

how about this:

select  case when GLALTY <> ' '
                      then trim(glsub) concat '-' concat glalty
                      else trim(glsub) end as costcode,
           sum(decimal(glu * .01, 31, 2)) as sum,
           glum as uom                                                
from vgiprddta/f0911li                                             
where glalty <> ' ' and                                            
           glmcu = @jobnumber and                                       
           (globj = '63500 ' or globj = '73500 ' or globj = '73510 ') and     
           (gllt = 'AA' or gllt = 'AU')                                       
group by  glsub, glalty, glum;

Steve

[snufse]

Re: Combine two fields into one field

Got this to work:

select                                                          
  case                                                          
    when glalty  = ' ' then glsub                               
    when glalty <> ' ' then glsub || '-' || glalty              
  end as costcode,                                              
  sum(decimal(glu * .01, 31, 2)) as sum,                        
  glum as uom                                                   
from                                                            
  vgiprddta/f0911li                                             
where glmcu = @jobnumber and                                    
(globj = '63500 ' or globj = '73500 ' or globj = '73510 ') and  
(gllt = 'AA' or gllt = 'AU')                                    
group by                                                        
  case                                                          
    when glalty = ' ' then glsub                                
    when glalty <> ' ' then glsub || '-' || glalty              
  end,                                                          
  glum;

Now I need to trim trailing blanks off the GLSUB field and if I do like this I get error:

when glalty <> ' ' then rtim(glsub) || '-' || glalty

[SteveL]

Re: Combine two fields into one field

Good to see you got it working. I only learned about the case statement a few days back when I had to provide the bill to state code in a view. On our ERP system it works two different ways depending on the default customer address or an adhoc one time address override.

Steve

[snufse]

Re: Combine two fields into one field

Got it working now. Thank you all.