If field is <9, then pad with 0, else use both digits in field

Here is my query:
select dd.o_itemname||'-'||dd.o_projectno||'-'||dd.o_version||'-'||'0'||dd.o_version_seq SKETCH
        from dms_proj dp, dms_doc dd
        where dd.o_projguid = dp.o_projguid
        and dp.o_stateno = 21
        and dd.o_itemname like 'SK%';

What I need to do is use this condition before qeury can work.
For row in question...
If dd.o_version_seq > 9 then
   select dd.o_itemname||'-'||dd.o_projectno||'-'||dd.o_version||'-'||dd.o_version_seq SKETCH
else
   select dd.o_itemname||'-'||dd.o_projectno||'-'||dd.o_version||'-'||'0'||dd.o_version_seq SKETCH
end if

How do I make this work so that each row will be tested for condition, and appropriate query used?  This IS NOT a stored proc, but a query used in Lotus Notes.

Thanks in advance for your help.  
Jon DavidsonDeveloper/Nuclear UtilityAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
fyi,

to_char(dd.o_version_seq,'fm09')  


will work regardless if the usage is in SQL or a stored procedure
0
 
sdstuberCommented:
select dd.o_itemname||'-'||dd.o_projectno||'-'||dd.o_version||'-'||
to_char(dd.o_version_seq,'fm09')
0
 
Jon DavidsonDeveloper/Nuclear UtilityAuthor Commented:
sdstuber:
What does fm09 do?   Because one way, I need to pad with a zero, and the other (>9) would be not to use the 0, but just the number from the field.  Either way, I'll have 2 numbers in the field at all times.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sdstuberCommented:
that's exactly what fm09  does


fm- removes extra space reserved for a sign character

otherwise  to_char('9','09')  would return ' 09'  instead of '09'

09 - left pad with 0 to fill two digits

you could use   'fm00'  instead of 'fm09'  for the same functionality
0
 
sdstuberCommented:
0
 
Jon DavidsonDeveloper/Nuclear UtilityAuthor Commented:
If I use fm00, I get '000' as output.  If I use fm99, I get '00', or '01'.  I'll have to test a for number greater than 9 since it only happens on rare occasions.  Thank you.  I need to test before I close and award this action.  
0
 
sdstuberCommented:
>>>  If I use fm00, I get '000'

no you don't

at least not from the to_char itself.  your third zero must be from the concatenation of the other fields

'0' || to_char(0,'fm00')  = '000'  but it's not the fm00  that is creating three zero's


in fact,  fm09 (or fm00)  is not just a an insurance to have at least 2 digits,  it's also assurance that you'll have at most 2 digits

to_char(123,'fm09')  = '###"


why?  because you can't fit 123 in 2 digits
0
 
sdstuberCommented:
Here's a simple test to confirm the conversions for every number 0-109

The   ><  is simply to show there is no extra whitespace or other invisible characters surrounding the converted string


SELECT n, '>' || TO_CHAR(n, 'fm00') || '<'
  FROM (SELECT     LEVEL - 1 n
              FROM DUAL
        CONNECT BY LEVEL <= 110)
0
 
Jon DavidsonDeveloper/Nuclear UtilityAuthor Commented:
Ah! I found an error in my sql, so the fm00 works as you said.   fm00 should handle numbers from 1~99?
0
 
sdstuberCommented:
as shown in the test case, it will handle 0 to 99 ,  so yes, 1-99 will work too

of course, best advice,  run it yourself, test it yourself.

don't just trust me
0
 
awking00Commented:
See attached.
comment.txt
0
 
sdstuberCommented:
awking00

why would you force BOTH an implicit conversion from number to string  as well as padding?

using the implicit conversion  that way forces the db to do this for you

lpad(to_char(dd.o_version_seq),2,'0')

I agree it will work, but if you're need to do the to_char anyway, then more efficient and easier to just do it one step
0
 
Jon DavidsonDeveloper/Nuclear UtilityAuthor Commented:
The complete string that this forms will always remain constant

SK0001-12345-00-00 or SK1234-12345-01-99

Note the last two digits in the field.  The value could fall between 0, and 99 (I might've said 1~99 in an earlier note, but is 0~99).  It is a numerical value.  It would be very rare to see the version sequence (last two numbers) value greater than 9, but I have to code for it nonetheless.
Adding the code lpad(),2,'0')  does it all without the implicit conversion?
0
 
sdstuberCommented:
>>Adding the code lpad(),2,'0')  does it all without the implicit conversion?

NO

The lpad version forces the same conversion I suggested above AND requires the extra step of a call to lpad too

just use the

to_char(dd.o_version_seq,'fm09')  

as shown originally.  

It's one function call doing it all in one step and,
if you run it, you'll see it does exactly what you asked

0
 
Jon DavidsonDeveloper/Nuclear UtilityAuthor Commented:
Thanks for the explanations, as I iterated through the tesitng.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.