Sql with switch

I am looking for query for followng  ( I need the query for both Sql server and Oracle)
    There are Project  and Document tables
         Project has projectName, projectNo fileds
         Document has DocuemntNo, DocRevision,  projectNo

       The tables join with    Project.ProjectNo = Document.ProjectNo

    I need to get the ProjectName, DocumentNo, VersionNo

    But if versionNo  = 0  return Blank as VersionNo  else return  "\Ver" & VersionNo padded with five zeros

      Example result rows are
           VersionNo = 0
            Proj1, Doc1 , Blank
          VersionNo = 1
            Proj1,Doc1, \Ver00001
Sam OZAsked:
Who is Participating?
Ryan ChongCommented:
try like:

case when VersionNo = 0 then '' else '\Ver' + right('00000' + cast(VersionNo as varchar), 5) end VersionNo

Open in new window

Alexander Eßer [Alex140181]Software DeveloperCommented:
using DECODE:
decode(versionno, 0, null, lpad(to_char(versionno), 5, '0'))

Open in new window

using CASE:
case when versionno = 0 then null else lpad(to_char(versionno), 5, '0') end

Open in new window

NerdsOfTechTechnology ScientistCommented:
CASE and LPAD functions work for both databases. Assuming DocRevision is being tested as VersionNo:

	, d.DocumentNo
	, CASE d.DocRevision
	    ELSE ('\Ver' + LPAD(d.DocRevision, 5, '0')) 
	  END VersionNo
 FROM Project p
 LEFT JOIN Document d
 ON p.ProjectNo = d.ProjectNo 

Open in new window

So now the question is, if DocRevision is not being tested, where is VersionNo coming from? Is it a variable? More info is needed in this case.
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.