Invalid Column Name' While Using the Having

invalid column name' while using the HAVING

Aggregation is required , as you have no access to alias total_calories

SELECT   type,SUM(calories) AS total_calories 
FROM     exercise_logs 
GROUP BY type 
HAVING   SUM(calories) > 150;

Getting sql error invalid column name while using Having

A few ways you can approach this. Hopefully the following is syntactically correct as obviously unable to directly test.

You can use a derived query or CTE such as

with d as (
 LocationId, 
 (
   3959 *
   acos(cos(radians(37)) * 
   cos(radians(Latitude)) * 
   cos(radians(Longitude) - 
   radians(-122)) + 
   sin(radians(37)) * 
   sin(radians(Latitude)))
 ) AS distance 
 FROM dbo.UserLocation
)
select *
from d
where distance < 28
order by distance

You could also use an apply

select LocationId, distance
from dbo.UserLocation
cross apply(values(
   3959 *
   Acos(Cos(Radians(37)) * 
   Cos(Radians(Latitude)) * 
   Cos(Radians(Longitude) - 
   Radians(-122)) + 
   Sin(Radians(37)) * 
   Sin(Radians(Latitude)))
    ))v(distance)
where distance < 28
order by distance

SQL Server: Invalid Column Name

Whenever this happens to me, I press Ctrl+Shift+R which refreshes intellisense, close the query window (save if necessary), then start a new session which usually works quite well.

SQL Server show invalid column name when the column name on the table exists

You have a DROP statement before the insert. That's why you get an warning that the column is missing.

You may want to move the DROP statement before the CREATE one, or even use the new syntax DROP TABLE IF EXISTS ... if it is available in your edition.

Getting Invalid column name in trigger

You can change the insert from values to an insert from select.

CREATE TRIGGER [TR_CURSO] ON [CURSO]
AFTER INSERT
AS
BEGIN
    UPDATE
        TOTALES
    SET
        TOTAL_CURSOS = ( SELECT COUNT(CODIGO) FROM CURSO );
    
    INSERT INTO BITACORA (
      MOMENTO,
      DESCRIPCION
    )
    SELECT 
      SYSDATETIME() AS MOMENTO,
      CONCAT (
        'SE INSERTO EL MAESTRO CON RFC DE: ', RTRIM(RFC_I), 
        ' DEL CURSO: ', RTRIM(NOMBRE), 
        ' CON COSTO DE: ', COSTO, 
        ' CON FECHA DE: ', GETDATE(), 
        ' CON LA DURACION DE: ', DURACION, 
        'Y EL CODIGO DE: ', RTRIM(CODIGO), 
        ' POR EL USUARIO: ', CURRENT_USER
      ) AS DESCRIPCION
    FROM INSERTED;
    
END;

Demo on db<>fiddle here

Invalid Column Name issue with SSMS 18

This is a known issue with SSMS 18.9, and a hotfix is currently being developed.

https://twitter.com/SysAdminDrew/status/1382869366702624774?s=20

Invalid column name x and There is already an object named 'x' in the database

Don't copy into a temporary table only to delete it afterwards. Just insert directly from the original.

Also:

• Don't roll your own IDENTITY column with SELECT MAX. Use a proper IDENTITY column and get the previous value using OUTPUT or SCOPE_IDENTITY().
• Don't quote every column name with [] when it doesn't need it. I haven't bothered removing them because I haven't got all day.
• I must say, I'm unclear why you have those joins, they don't appear necessary, but I've left them in.

CREATE OR ALTER PROCEDURE [dbo].[CopyShip]
     @ShipId int,
     @ShipName nvarchar(150),
     @ShipCode nvarchar(8)
AS

SET NOCOUNT ON;

INSERT INTO Ships (
  [CruiselineId], [Name], [ShipCode], [ClassId],
  [guestNumber], [staffNumber], [creationYear],
  [weight], [length], [passagerDeck], [handicapCabins], [nationality]
)
SELECT
  [CruiselineId], @ShipName, @ShipCode, [ClassId],
  [guestNumber], [staffNumber], [creationYear],
  [weight], [length], [passagerDeck], [handicapCabins], [nationality]
FROM [dbo].Ships 
WHERE ShipId = @ShipId;

DECLARE @NewShipId int = SCOPE_IDENTITY();

-- CREATING COPY OF Cabins
INSERT INTO CabinCategory (
  [ShipId], [CabinType], [BalconySize], [MinSize], [MaxSize],
  [NoOfBeds], [Category], [HexCodes], [Description],
  [LongDescription], [LongDescriptonSE], [LongDescriptionNO],
  [HeaderDescriptionSE], [HeaderDescriptionNO],
  [FreeTextField], [FreeTextFieldSE], [FreeTextFieldNO], [CabinCategoryIdOld]
)
SELECT
  @NewShipId, [CabinType], [BalconySize], [MinSize], [MaxSize],
  [NoOfBeds], [Category], [HexCodes], [Description],
  [LongDescription], [LongDescriptonSE], [LongDescriptionNO],
  [HeaderDescriptionSE], [HeaderDescriptionNO],
  [FreeTextField], [FreeTextFieldSE], [FreeTextFieldNO],
  [CabinCategoryIdOld]
FROM [dbo].CabinCategory cc
WHERE ShipId = @ShipId;

INSERT INTO CabinToBenefits (
  [BenefitsId]
 ,[CabinCategoryId]
 ,[ShipId]
)
SELECT B.[BenefitsId]
      ,bse2.CabinCategoryId
      ,@NewShipId
FROM [dbo].[CabinToBenefits] B
JOIN dbo.CabinCategory bse on B.CabinCategoryId = bse.CabinCategoryId
JOIN dbo.CabinCategory bse2 on bse2.[Category] = bse.Category and B.ShipId = bse2.ShipId
WHERE ShipId = @ShipId;

---- CREATING COPY OF RestaurantToSeating
INSERT INTO RestaurantToSeating (
  ShipId,
  RestaurantId,
  SeatingId
)
SELECT @NewShipId,
       bse.RestaurantId,
       rs.SeatingId 
FROM RestaurantToSeating rs
JOIN dbo.Restaurant r on rs.RestaurantId = r.RestaurantId 
JOIN dbo.Restaurant bse on r.Category = bse.Category and rs.ShipId = bse.ShipId
where rs.ShipId = @ShipId;

---

Related Topics