Aggregate Query Errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Aggregate Query Errors

Author

Topic

silver_surfer21
Starting Member

21 Posts

Posted - 2011-01-06 : 12:29:39

Hi, I am trying to write an aggregation query based on a view of data from another table, however the query keeps raising the error
Msg 537, Level 16, State 3, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function

which I cannot understand as it does not contain any LEFT or SUBSTRING functions. The View does, and I believe it threw a warning similar to this but it still ran and provided me with results. Could anyone suggest what the problem may be and how I could resolve it please?

The query is as follows:

SELECT  AVG(decPrice),Postout,application_Year, Application_Month
FROM         Postcode_DateSplit GROUP BY PostOut, application_year,application_month

while the sql for the view is:

SELECT     ID, decPrice, Application_Date, Postcode, HouseType, NewBuild, LeaseStatus, SAON, PAON, Thoroughfare, Locality, Town, District, County, Easting, Northing, 
                      strAddress, LEFT(Application_Date, CHARINDEX('-', Application_Date) - 1) AS Application_Year, SUBSTRING(Application_Date, CHARINDEX('-', Application_Date) + 1, 2) 
                      AS Application_Month, LEFT(Postcode, CHARINDEX(' ', Postcode) - 1) AS PostOut
FROM         dbo.AllEWdata
WHERE     (Postcode IS NOT NULL) AND (Postcode <> '\N')

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-06 : 16:07:30

The error is in the source data itself, exposed by the view:

LEFT(Application_Date, CHARINDEX('-', Application_Date) - 1) AS Application_Year
, SUBSTRING(Application_Date, CHARINDEX('-', Application_Date) + 1, 2) ,CHARINDEX('-', Application_Date) + 1, 2) 
                      AS Application_Month

1. Why is application_date NOT in a datetime format? this is flaw #1 in the table itself.
2. Assuming 1 is correct, and these are strings...it would appear that some of them do not have the '-' dash mark. SOOO..if there is no hashmark, the CHARINDEX('-', Application_Date) - 1) failes because a string cannot have a position less than 0
3. Please put Application_Date as a datetime field if possible, that way--no substrings necessary.
4. Why was a date field not stored as datetime again?

Poor planning on your part does not constitute an emergency on my part.

silver_surfer21
Starting Member

21 Posts

Posted - 2011-01-07 : 05:01:10

Thanks for the reply.

The data has been passed to us by another firm and there's been a great deal of problems with getting it imported properly. The only way I've been able to get it into SQL Server at all was to have them provide it to me in a series of access DB files which could then be imported into SQL. Unfortunately the data in Access is all in text format, and so has simply been imported into SQL the same way. Is it possible for me to re-import the data and change the formats as I go? Or just alter the date formats in the sql tables as they are just now?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-07 : 07:55:45

Check the date column to see if they can all be cast as dates.

Select Application_Date
FROM [table]
Where isDate(Application_Date) = 0

That would return any "non dates" in the Application date field. If no records are returned, then you can likely just convert them in SQL (load into staging table, insert to your final table with correct datatypes. You can clean up the data once it is in staging).

Poor planning on your part does not constitute an emergency on my part.

silver_surfer21
Starting Member

21 Posts

Posted - 2011-01-10 : 05:10:59

I have altered the table so that the date is now stored as a date, and have attempted to run the view with the code

SELECT     ID, decPrice, Application_Date, Postcode, HouseType, NewBuild, LeaseStatus, SAON, PAON, Thoroughfare, Locality, Town, District, County, Easting, Northing,strAddress, LEFT(Postcode, CHARINDEX(' ', Postcode) - 1) AS PostOut
FROM         dbo.AllEWdata
WHERE     (Postcode IS NOT NULL) AND (Postcode <> '\N')

but this still gives the error relating to the LEFT function, which I suspect is because there are some null values turning up in the postcodes in the table data. I thought the WHERE condition might take care of this but it seems not. What's the easiest way I could construct the view so the function will not look at null postcodes?

Thanks for help so far

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-10 : 07:40:50

Did you modify the VIEW to remove this stuff:
LEFT(Application_Date, CHARINDEX('-', Application_Date) - 1) AS Application_Year, SUBSTRING(Application_Date, CHARINDEX('-', Application_Date) + 1, 2)
AS Application_Month

and replace it with:
YEAR(Application_Date) as Application_Year, MONTH(Application_Date) as Application_Month

With the values stored as dates you don't have to use a string function to get info from the date field.

Poor planning on your part does not constitute an emergency on my part.

silver_surfer21
Starting Member

21 Posts

Posted - 2011-01-10 : 07:48:03

I have tried re-running the view using only the code in my above post, nothing relating to the date at all, just so I could check that the problem was not related to the postcode, before adding the date functions in. It appears that there is a null value in one of the postcode records causing the same error as I mentioned before. I am perfectly happy to tell the view to just ignore any records where the postcode is null, (or perhaps does not contain a space, as this is what I'm using for the left function) but am unsure how to alter the query structure to do this.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources