SQL VIEW

[muhaidib]

I have created following SQL view for the sake of report. I have made use of 2 case statements for fields dbo.ICSHIPD.SHIPQTY and dbo.ICSHIPD.EXPRICEHM. SQL Enterprise manager saved the view with first case statement but when I add second case statement it flashes the message as "View definition includes no output columns or no items in the FROM clause" and does not saves the view. In Query analyzer this works is desired by me.

Can someone help me to sort out? Putting 2 case statement as must for me.

SELECT TOP 100 PERCENT RTRIM(dbo.ICSHIPH.DOCNUM) AS DOCNUM, dbo.ICSHIPH.TRANSTYPE, RTRIM(dbo.ICSHIPH.HDRDESC) AS DESCRIPTION,
dbo.ICSHIPD.LOCATION, RTRIM(dbo.ICSHIPD.FMTITEMNO) AS FMTITEMNO, RTRIM(dbo.ICSHIPD.ITEMDESC) AS ITEMDESC,
dbo.ICSHIPD.SHIPQTY * CASE WHEN dbo.ICSHIPH.TRANSTYPE = 2 THEN - 1 ELSE 1 END AS SHIPQTY,
dbo.ICSHIPD.EXPRICEHM * CASE WHEN dbo.ICSHIPH.TRANSTYPE = 2 THEN - 1 ELSE 1 END AS EXPRICEHM,
dbo.ICSHIPD.SHIPCOST * - 1 AS SHIPCOST, dbo.ICSHIPD.VARIANCE, dbo.ICSHIPH.FISCPERIOD
FROM dbo.ICSHIPH INNER JOIN
dbo.ICSHIPD ON dbo.ICSHIPH.DAYENDSEQ = dbo.ICSHIPD.DAYENDSEQ AND dbo.ICSHIPH.TRANSSEQ = dbo.ICSHIPD.TRANSSEQ
ORDER BY dbo.ICSHIPD.FMTITEMNO, dbo.ICSHIPH.TRANSTYPE

 

[muhaidib]

This matter is resolved by using Query Analyzer by simply creating a view. It saved the query.

This syntax of Case statement is not allowed in QUERY DESIGNER of SQL Enterprise Manager.