sql syntax question

Reply
Valued Guide
Posts: 858

sql syntax question

Hello,  This is more of an sql than sas question but I've been banging my head on this and can't come up with anything.  I have a piece of code that works fine:

decode(LENGTH(a.prd_id),9,concat('0',a.pr_id),

                                       2,concat('0'.a.pr_id),

                                       1,concat('00',a.pr_id),a.pr_id) as "ManufacturerPartNumber",

I want to do something very similar but with an added level of difficulty.  I want to say IF rp_attr_dty_id = 'INT_APPLICATION_TYPE' THEN decode....

Due to the project I have to stay within sql or it would be an easy fix with sas, I thought of nesting another decode but didn't get what I wanted.  Any thoughts?

Thanks,

Super User
Posts: 17,820

Re: sql syntax question

Use case instead?

Case  when () then decode()

else

end as

Valued Guide
Posts: 858

Re: sql syntax question

That's a great suggestion, I'm not sure that will work though.  I have another decode being used for the same variable already.  I'll attach what I have,

The section that is commented out is what I thought might work but doesn't.  I need to concat zero's on the valueidentifier and value variables if one of their variables has a specific value.

See what I mean?

Attachment
Super User
Posts: 17,820

Re: sql syntax question

Are you on Oracle? What are you trying to do overall, what values trigger adding a 0? Is it when it's too short?

Super User
Super User
Posts: 6,500

Re: sql syntax question

CASE is what you want.  You might need to use it multiple times if you have multiple variables that need to be adjusted.  You might need to duplicate the decode() function call.

select

   case when (rp_attr_dty_id = 'INT_APPLICATION_TYPE' )

       then decode(LENGTH(a.prd_id)

                  ,9,concat('0',a.pr_id)

                  ,2,concat('0'.a.pr_id)

                  ,1,concat('00',a.pr_id)

                  ,a.pr_id

                  )

       else decode(a.rp_cat_attr_enty_map_it_id

                  ,NULL,b.RP_IT_TMPLT_IT_NM

                  ,smd.rp30_handler.getitemname (a.rp_cat_attr_enty_map_it_id)

                  )

   end as "PartNumber"

  ,a.RP_ATTR_DTY_ID as "AttributeIdentifier"

  ,decode(c.RP_ATTR_DTY_TYP_DS,'AllowedValues',a.RP_CAT_ATTR_ENTY_MAP_VAL_ID) as "ValueIdentifier"

  ,decode(c.RP_ATTR_DTY_TYP_DS,'AssignedValues',a.RP_CAT_ATTR_ENTY_MAP_VAL_ID) as "Value"

  ,'Descriptive' as "Usage"

  ,a.RP_CAT_ATTR_ENTY_MAP_SEQ_DS as "Sequence"

  ,a.RP_CAT_ATTR_ENTY_MAP_DEL_IN as "Delete"

from

...

Super User
Posts: 17,820
Ask a Question
Discussion stats
  • 5 replies
  • 207 views
  • 0 likes
  • 3 in conversation