Click here to Skip to main content
15,889,216 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
ALTER PROCEDURE [dbo].[rpt_Inc_Xtat]
 (
   @CINCIDENT VARCHAR(50),
   @CDDL1     VARCHAR(50),
   @CDDL2     VARCHAR(50),
   @CDCOMPT   VARCHAR(50),
   @CFROM     DATETIME   ,
   @CTO       DATETIME 
)
AS
  BEGIN
        DECLARE @sql nvarchar(4000)
       SELECT  A.BRN_NAME,A.COMP_NO, A.COMP_CODE, A.COMPLAINANT, A.PTICK_CODE, 
                  SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) < COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS ACCEPT_TAT,
				   COUNT(*) AS OVER_CNT ,
				  (SELECT COUNT(*) FROM RESPONSE A , BB    WHERE A.BRN_NAME=BB.BRN_NAME) AS GRP_CNT,
				    P.PROD_SERV_TAT
                    FROM RESPONSE A , BB
                    LEFT  JOIN BRANCH    C   ON A.BRN_NAME   = C.BRN_NAME
                    LEFT  JOIN PROD_SERV P   ON A.PROD_SERV  = P.PROD_SERV_NAME
                    LEFT  JOIN PAYMAST   B   ON A.LNAME      = B.LNAME
                                         AND  A.FNAME        = B.FNAME
                                         AND  A.EMPNO        = B.EMPNO 
                    WHERE (A.COMP_DATE BETWEEN @CFROM AND @CTO) 
                    GROUP BY  A.BRN_NAME,A.COMP_NO, A.COMP_CODE,A.COMPLAINANT, A.PTICK_CODE, P.PROD_SERV_TAT 

			
  END


Errors

1. Invalid object name 'BB'

(SELECT COUNT(*) FROM RESPONSE A , BB    WHERE A.BRN_NAME=BB.BRN_NAME) AS
  GRP_CNT,


2.
LEFT  JOIN BRANCH    C   ON A.BRN_NAME   = C.BRN_NAME
                    LEFT  JOIN PROD_SERV P   ON A.PROD_SERV  = P.PROD_SERV_NAME
                    LEFT  JOIN PAYMAST   B   ON A.LNAME      = B.LNAME
                                         AND    A.FNAME        = B.FNAME
                                         AND    A.EMPNO        = B.EMPNO 



The multi part-identifier "A.PROD_SERV" could not be found
The multi part-identifier "A.LNAME" could not be found
The multi part-identifier "A.FNAME" could not be found
The multi part-identifier "A.PROD_SERV" could not be found

What I have tried:

Have a similar codes which works.

What am doing here is I have introduced a subquery to COUNT on GROUP .
Posted
Updated 26-Jan-18 7:37am
v2
Comments
Rajesh Pandya 26-Jan-18 7:00am    
'BB' is your table name? If yes then that table is not exists in database and because of that it gives error "Invalid object name 'BB'".
If you have given alias as 'BB' then you need to specify table name and then alias name.

1 solution

SQL
(SELECT COUNT(*) FROM RESPONSE A , BB /* */),
-- ...
FROM RESPONSE A , BB

The second BB alias is not related to any table. It is not needed anywhere else than in the subquery to count on group, so why including it in the top-level query?
 
Share this answer
 
v2

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