Invalid Column

Transact-SQL
1

Working a new SQL query and coming up with an Invalid column and not sure why this is.

DECLARE @start_date date
DECLARE @end_date date
SET @start_date = '01-01-2020'
SET @end_date = '12-31-2020'

SELECT 
    s.STATE,
	DATE
	MILEAGE
FROM tbSTATES s 
LEFT OUTER JOIN (
    SELECT 
            STATE,
		DISPATCH_DATE AS DATE,
		SUM(MILEAGE) AS MILEAGE
    FROM Fuel_Table f
    CROSS APPLY ( VALUES
	   (UL_STATE_1, MILEAGE_S1),
	   (UL_STATE_2, MILEAGE_S2),
	   (UL_STATE_3, MILEAGE_S3),
	   (UL_STATE_4, MILEAGE_S4),
	   (UL_STATE_5, MILEAGE_S5),
	   (UL_STATE_6, MILEAGE_S6),
	   (UL_STATE_7, MILEAGE_S7),
	   (UL_STATE_8, MILEAGE_S8)
    ) AS ca1 (STATE, MILEAGE)
    WHERE STATE LIKE '[A-Z]%' AND DATE BETWEEN @start_date AND @end_date
    GROUP BY STATE
) AS f ON f.STATE = s.ABBREVIATION
ORDER BY s.STATE

The error shows in the WHERE clause that DATE is an invalid column. Can someone advise where my error is or why DATE is considered an invalid column please? FYI: @start_date & @end_date will be parameters passed but hard coded for testing purposes.

2

please provide the error?

3

It literally states DATE is an invalid column... that is it.

Msg 207, Level 16, State 1, Line 27
Invalid column name 'DATE'.
4

try wrapping it with brackets [DATE] which table is that supposed to be coming from?
Actually it might this

DISPATCH_DATE AS DATE

Date is a reserved SQL word leave it as DISPATCH_DATE. DATE is an alias of DISPATCH_DATE so cannot be used in the where clause

5

AH, I think I got it working now...

Only thing now is if there is a 'null' value in the mileage column for any state I would like to put a '0' instead of a blank space. Anyone ideas how to do that?

Changed Query to:

DECLARE @start_date date
DECLARE @end_date date
SET @start_date =  '01-01-2020'
SET @end_date = '12-31-2020'

SELECT 
    s.STATE,
	MILEAGE
FROM tbSTATES s 
LEFT OUTER JOIN (
    SELECT 
        STATE,
		SUM(MILEAGE) AS MILEAGE
	FROM Fuel_Table f
    CROSS APPLY ( VALUES
	   (UL_STATE_1, MILEAGE_S1),
	   (UL_STATE_2, MILEAGE_S2),
	   (UL_STATE_3, MILEAGE_S3),
	   (UL_STATE_4, MILEAGE_S4),
	   (UL_STATE_5, MILEAGE_S5),
	   (UL_STATE_6, MILEAGE_S6),
	   (UL_STATE_7, MILEAGE_S7),
	   (UL_STATE_8, MILEAGE_S8)
    ) AS ca1 (STATE, MILEAGE)
    WHERE STATE LIKE '[A-Z]%' AND DISPATCH_DATE BETWEEN @start_date AND @end_date
    GROUP BY STATE
) AS f ON f.STATE = s.ABBREVIATION
ORDER BY s.STATE
6

ISNULL or COALESCE, read up on it

7

Thanks. I have already read up on it and not sure I am applying it correctly as I still get a NULL response.

I tried changing line
SUM(MILEAGE) AS MILEAGE
to
SUM(ISNULL(MILEAGE, 0)) AS MILEAGE

I also tried using coalesce in place of ISNULL with same result.

I additionally tried, as an alternate, changing the cross apply to

(UL_STATE_1, (ISNULL(MILEAGE_S1, 0)))

(all lines inclusive but left out here for brevity) with the ISNULL both in the SUM statement as well as without and still getting a NULL response
,

8

[quote="K3JAE, post:5, topic:18683"]

SELECT 
    s.STATE,
	MILEAGE = coalesce(f.MILEAGE, 0)
FROM tbSTATES s
1 Like
9 
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = '01-01-2020'
SET @end_date = '12-31-2020'

declare @states table(stateid int identity(1,1), STATE varchar(50), ABBREVIATION char(2))
declare @Fuel_Table  table(UL_STATE_1 varchar(50) , [DATE] datetime, MILEAGE_S1 decimal(6,3), DISPATCH_DATE datetime )


insert into @states
select distinct top 50 name, UPPER(LEFT(name,2)) from sys.all_columns


SELECT 
    s.STATE,
	isnull(MILEAGE,0) as MILEAGE,
	coalesce(f.MILEAGE, 0) _MILEAGE
FROM @states s 
LEFT OUTER JOIN (
    SELECT 
        STATE,
		sum(MILEAGE) as MILEAGE
	FROM @Fuel_Table f
    CROSS APPLY ( VALUES
	   (UL_STATE_1, MILEAGE_S1)
    ) AS ca1 (STATE, MILEAGE)
    WHERE STATE LIKE '[A-Z]%' AND DISPATCH_DATE BETWEEN @start_date AND @end_date
    GROUP BY STATE
) AS f ON f.STATE = s.ABBREVIATION
ORDER BY s.STATE
10

@jeffw8713 - bingo... thank you sir!

11

No, Date is not a reserved SQL word, as a quick Google for "SQL Server reserved keywords" will prove.

1 Like