asked on

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

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alex [***Alex140181***]

Oralce:
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

NerdsOfTech

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

 SELECT 
	  p.ProjectNo
	, d.DocumentNo
	, CASE d.DocRevision
	    WHEN 0 THEN NULL
	    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.