Click here to Skip to main content
15,886,724 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Need to change and include the undernoted IF...ELSE...END Literal expression in a CTE- Common table expression.

IF @CINCIDENT <>'ALL'
            BEGIN
              IF @CINCIDENT = 'OPENED'
                  SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT >= GETDATE()'
              ELSE IF @CINCIDENT = 'CLOSED'
                  SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT <  GETDATE()
                                      AND A.RESOLUTION_STAT_FRONT NOT IN ("BAD","UNHAPPY","UNSATISFACTORY")'
              ELSE IF @CINCIDENT = 'UNRESOLVED'
                  SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT <  GETDATE()
                                    AND A.RESOLUTION_STAT_FRONT     IN ("BAD","UNHAPPY","UNSATISFACTORY")'
             END




CTE -Common Table Expression

;with cte as
(	
		SELECT a.Brn_name AS BRNNAME,A.PTICK_CODE AS TICKET,
		       A.LNAME    AS LNAME   ,A.FNAME      AS FNAME ,
		       A.EMPNO    AS EMPNO  ,A.COMP_TYPE  AS COMP_TYPE,     
		                                b.Attribs AS ATTRIB1  , c.Attribs AS ATTRIB2,
                                        d.Attribs AS ATTRIB3  , e.Attribs AS ATTRIB4
			FROM Response A
			JOIN @REQUEST B ON a.RESPONSE1 = B.Response
			JOIN @REQUEST C ON a.RESPONSE2 = C.Response
			JOIN @REQUEST D ON a.RESPONSE3 = D.Response
			JOIN @REQUEST E ON a.RESPONSE4 = E.Response
	    WHERE A.COMP_DATE BETWEEN @CFROM AND @CTO

         .................(CASE THEN END) STATEMENT..........

)



How do I switch from the IF..ELSE..END
literal expression
to tie into the CTE expression to complete the statement


Thanks

What I have tried:

On going system developmental challenge.

Checked the Internet to no avail
Posted
Comments
Jörgen Andersson 7-May-18 2:05am    
Use a case statement. https://www.techonthenet.com/sql_server/functions/case.php

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900