CODEWITHSUNDEEP
sql
user2119980
user2119980

Reputation: 509

Encountering Error Cannot seem to fix

I have the following code 

SELECT dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR AS [EHP Code], 
       dbo.COL_TBL_VCOURSE_NEW.TNG_NA AS [Course Title],
       dbo.COL_TBL_VCOURSE_NEW.FCT_TYP_CD & dbo.COL_TBL_VCOURSE_NEW.DEP_TYP_CD AS [Course Owner], 
       dbo.COL_TBL_VCOURSE_TYP.TNG_DESC AS [TYPE OF TRAINING], 
       dbo.COL_V_Cost_GEMS_Detail.JOB_FCT_CD, 
       dbo.COL_V_Cost_GEMS_Detail.JOB_GRP_CD, 
       dbo.COL_V_Cost_GEMS_Detail.ST_COST_SUM + dbo.COL_V_Cost_GEMS_Detail.IN_COST_SUM AS [Total Cost], 
       SUM(dbo.COL_V_Cost_GEMS_Detail.STUDENTS) AS SumOfSTUDENTS, 
       SUM(dbo.COL_V_Cost_GEMS_Detail.INSTRUCTORS) AS SumOfINSTRUCTORS,
       SUM(dbo.COL_V_Cost_GEMS_Detail.ST_HOURS_SUM) AS ST_HOURS,
       SUM(dbo.COL_V_Cost_GEMS_Detail.ST_COST_SUM) AS ST_COST, 
       SUM(dbo.COL_V_Cost_GEMS_Detail.IN_HOURS_SUM) AS IN_HOURS, 
       SUM(dbo.COL_V_Cost_GEMS_Detail.IN_COST_SUM) AS IN_COST
FROM  dbo.COL_V_Cost_GEMS_Detail INNER JOIN  dbo.COL_TBL_VCOURSE_NEW 
      ON dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR = dbo.COL_TBL_VCOURSE_NEW.TNG_SYS_NR       
      INNER JOIN dbo.COL_TBL_VCOURSE_TYP 
      ON dbo.COL_TBL_VCOURSE_NEW.TNG_MDA_TYP_CD = dbo.COL_TBL_VCOURSE_TYP.TNG_TYP
WHERE (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%12%') 
       AND 
       (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%13%') 
       AND 
       (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%2706%') 
       AND 
       (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%2707%')
       AND 
       (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%2331%')
GROUP BY dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR, 
         dbo.COL_TBL_VCOURSE_NEW.TNG_NA, 
         dbo.COL_TBL_VCOURSE_NEW.FCT_TYP_CD & dbo.COL_TBL_VCOURSE_NEW.DEP_TYP_CD,         
         dbo.COL_TBL_VCOURSE_TYP.TNG_DESC, 
         dbo.COL_V_Cost_GEMS_Detail.JOB_FCT_CD,
         dbo.COL_V_Cost_GEMS_Detail.JOB_GRP_CD, 
         dbo.COL_V_Cost_GEMS_Detail.ST_COST_SUM +  
         dbo.COL_V_Cost_GEMS_Detail.IN_COST_SUM

And I keep getting the error

Msg 402, Level 16, State 1, Line 1
The data types nvarchar and nvarchar are incompatible in the boolean AND operator.

No matter what changes I make I cannot seem to get rid of this error. At first I thought it would be the data types but they are all fine, and I thought it was some of the symbols but replacing those did not work either.

Upvotes: 0

Views: 57

Answers (3)

GarethD
GarethD

Reputation: 69759

You are using the & Bitwise operator to concatenate strings. String concatenation uses + in SQL-Server (or you can use SQL-Server 2012 you can use the CONCAT function)

Change this:

dbo.COL_TBL_VCOURSE_NEW.FCT_TYP_CD & dbo.COL_TBL_VCOURSE_NEW.DEP_TYP_CD,

To

dbo.COL_TBL_VCOURSE_NEW.FCT_TYP_CD + dbo.COL_TBL_VCOURSE_NEW.DEP_TYP_CD

In both the select statement and the group by clause.

Upvotes: 1

Dhwani
Dhwani

Reputation: 7626

I removed &. Try this if this is working or not.

SELECT dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR AS [EHP Code], dbo.COL_TBL_VCOURSE_NEW.TNG_NA AS [Course Title], 
           dbo.COL_TBL_VCOURSE_NEW.FCT_TYP_CD, dbo.COL_TBL_VCOURSE_NEW.DEP_TYP_CD AS [Course Owner], 
           dbo.COL_TBL_VCOURSE_TYP.TNG_DESC AS [TYPE OF TRAINING], dbo.COL_V_Cost_GEMS_Detail.JOB_FCT_CD, dbo.COL_V_Cost_GEMS_Detail.JOB_GRP_CD, 
           dbo.COL_V_Cost_GEMS_Detail.ST_COST_SUM + dbo.COL_V_Cost_GEMS_Detail.IN_COST_SUM AS [Total Cost], SUM(dbo.COL_V_Cost_GEMS_Detail.STUDENTS) 
           AS SumOfSTUDENTS, SUM(dbo.COL_V_Cost_GEMS_Detail.INSTRUCTORS) AS SumOfINSTRUCTORS, SUM(dbo.COL_V_Cost_GEMS_Detail.ST_HOURS_SUM) 
           AS ST_HOURS, SUM(dbo.COL_V_Cost_GEMS_Detail.ST_COST_SUM) AS ST_COST, SUM(dbo.COL_V_Cost_GEMS_Detail.IN_HOURS_SUM) AS IN_HOURS, 
           SUM(dbo.COL_V_Cost_GEMS_Detail.IN_COST_SUM) AS IN_COST
FROM  dbo.COL_V_Cost_GEMS_Detail INNER JOIN
           dbo.COL_TBL_VCOURSE_NEW ON dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR = dbo.COL_TBL_VCOURSE_NEW.TNG_SYS_NR INNER JOIN
           dbo.COL_TBL_VCOURSE_TYP ON dbo.COL_TBL_VCOURSE_NEW.TNG_MDA_TYP_CD = dbo.COL_TBL_VCOURSE_TYP.TNG_TYP
WHERE (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%12%') AND (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%13%') AND 
           (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%2706%') AND (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%2707%') AND 
           (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%2331%')
GROUP BY dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR, dbo.COL_TBL_VCOURSE_NEW.TNG_NA, 
           dbo.COL_TBL_VCOURSE_NEW.FCT_TYP_CD, dbo.COL_TBL_VCOURSE_NEW.DEP_TYP_CD, dbo.COL_TBL_VCOURSE_TYP.TNG_DESC, 
           dbo.COL_V_Cost_GEMS_Detail.JOB_FCT_CD, dbo.COL_V_Cost_GEMS_Detail.JOB_GRP_CD, 
           dbo.COL_V_Cost_GEMS_Detail.ST_COST_SUM + dbo.COL_V_Cost_GEMS_Detail.IN_COST_SUM

OR u might want sum then:

SELECT dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR AS [EHP Code], dbo.COL_TBL_VCOURSE_NEW.TNG_NA AS [Course Title], 
               dbo.COL_TBL_VCOURSE_NEW.FCT_TYP_CD + dbo.COL_TBL_VCOURSE_NEW.DEP_TYP_CD AS [Course Owner], 
               dbo.COL_TBL_VCOURSE_TYP.TNG_DESC AS [TYPE OF TRAINING], dbo.COL_V_Cost_GEMS_Detail.JOB_FCT_CD, dbo.COL_V_Cost_GEMS_Detail.JOB_GRP_CD, 
               dbo.COL_V_Cost_GEMS_Detail.ST_COST_SUM + dbo.COL_V_Cost_GEMS_Detail.IN_COST_SUM AS [Total Cost], SUM(dbo.COL_V_Cost_GEMS_Detail.STUDENTS) 
               AS SumOfSTUDENTS, SUM(dbo.COL_V_Cost_GEMS_Detail.INSTRUCTORS) AS SumOfINSTRUCTORS, SUM(dbo.COL_V_Cost_GEMS_Detail.ST_HOURS_SUM) 
               AS ST_HOURS, SUM(dbo.COL_V_Cost_GEMS_Detail.ST_COST_SUM) AS ST_COST, SUM(dbo.COL_V_Cost_GEMS_Detail.IN_HOURS_SUM) AS IN_HOURS, 
               SUM(dbo.COL_V_Cost_GEMS_Detail.IN_COST_SUM) AS IN_COST
    FROM  dbo.COL_V_Cost_GEMS_Detail INNER JOIN
               dbo.COL_TBL_VCOURSE_NEW ON dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR = dbo.COL_TBL_VCOURSE_NEW.TNG_SYS_NR INNER JOIN
               dbo.COL_TBL_VCOURSE_TYP ON dbo.COL_TBL_VCOURSE_NEW.TNG_MDA_TYP_CD = dbo.COL_TBL_VCOURSE_TYP.TNG_TYP
    WHERE (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%12%') AND (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%13%') AND 
               (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%2706%') AND (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%2707%') AND 
               (dbo.COL_V_Cost_GEMS_Detail.RRDD NOT LIKE '%2331%')
    GROUP BY dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR, dbo.COL_TBL_VCOURSE_NEW.TNG_NA, 
               dbo.COL_TBL_VCOURSE_NEW.FCT_TYP_CD + dbo.COL_TBL_VCOURSE_NEW.DEP_TYP_CD, dbo.COL_TBL_VCOURSE_TYP.TNG_DESC, 
               dbo.COL_V_Cost_GEMS_Detail.JOB_FCT_CD, dbo.COL_V_Cost_GEMS_Detail.JOB_GRP_CD, 
               dbo.COL_V_Cost_GEMS_Detail.ST_COST_SUM + dbo.COL_V_Cost_GEMS_Detail.IN_COST_SUM

Upvotes: 1

Kenneth M. Nielsen
Kenneth M. Nielsen

Reputation: 487

It seems that you have an & try to change that!

Upvotes: 1

Related Questions