I had to add the name column, but same behavior:
CREATE VIEW LOOK AS
SELECT T.[name] AS [SITE_NAME]
,AC.[name] AS [LOGGER_FIELD]
,AC.[SITE_ID]
FROM sys.[tables] AS T
INNER JOIN (SELECT [object_id]
,[system_type_id]
,[user_type_id]
,[name]
,CONVERT(INT, RIGHT([name], 6)) AS SITE_ID
FROM sys.[all_columns]
WHERE [system_type_id] = 59
AND [name] NOT LIKE '%[_]%'
AND [name] LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]'
AND ([name] LIKE 'X0%'
OR [name] LIKE 'A0%'
OR [name] LIKE 'G0%'
)
) AS AC
ON AC.[object_id]=T.[object_id]
INNER JOIN sys.[types] AS TY
ON TY.[system_type_id]=AC.[system_type_id]
AND TY.[user_type_id]=AC.[user_type_id]
SELECT * FROM look WHERE SITE_ID = 17588
Conversion failed when converting the nvarchar value 'IN_Tot' to data type int.
I finally gave up and created a table over the view before the next set of processing. It's not elegant, but since the table data are already rendered, it only has numerical site_ids and I can continue on.
I thank everyone who offered suggestions. I still learned a lot, again, from you.
I just hate giving up.
if you did the conversion
after you filter your columns I think it could work.
try this
CREATE VIEW LOOK AS
with cteSites as
(
SELECT [object_id]
,[system_type_id]
,[user_type_id]
,[name]
FROM sys.[all_columns]
WHERE [system_type_id] = 59
AND [name] NOT LIKE '%[_]%'
AND [name] LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]'
AND ([name] LIKE 'X0%' OR [name] LIKE 'A0%' OR [name] LIKE 'G0%')
)
SELECT T.[name] AS [SITE_NAME]
,AC.[name] AS [LOGGER_FIELD]
,CONVERT(INT, RIGHT(ac.[name], 6)) AS SITE_ID
FROM sys.[tables] AS T
INNER JOIN cteSites AS AC
ON AC.[object_id]=T.[object_id]
INNER JOIN sys.[types] AS TY
ON TY.[system_type_id]=AC.[system_type_id]
AND TY.[user_type_id]=AC.[user_type_id]
go
SELECT * FROM look WHERE SITE_ID = 17588
Still get:
Conversion failed when converting the nvarchar value 'IN_Tot' to data type int.
Doesn't seem right, does it?
I'm using version
Microsoft SQL Server 2016 (RTM) - 13.0.1601.5
I have no idea if this matters, really, because likely it's data and resources, and how the optimizer is going after the data.
We are just starting our migration from Oracle to SQL Server, and I'm surely going to have other cases where this kind of thing is necessary, so I'd like to get it straight in my head.
what does this return?
SELECT [object_id]
,[system_type_id]
,[user_type_id]
,[name]
FROM sys.[all_columns]
WHERE [system_type_id] = 59
AND [name] NOT LIKE '%[_]%'
AND [name] LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]'
AND ([name] LIKE 'X0%' OR [name] LIKE 'A0%' OR [name] LIKE 'G0%')
)
Actually I think this:
could be written like so
AND [name] LIKE '[AGX]0%[0-9][0-9][0-9][0-9][0-9]'
I know this doesn't solve the original issue, but still ...
1 Like
It returns the 713 rows that are in the view (and now table), with only values that are number for the last 6 digits.
You need to use quotes around the SITE_ID comparison to avoid forcing a numeric conversion:
SELECT * FROM dbo.LN_TABLES_COLUMNS_V WHERE SITE_ID = '17588';
--rather than WHERE SITE_ID = 17588;
1 Like
Only because you're forcing it to be an int, which is causing the issue of invalid conversions.
CONVERT(INT,RIGHT(AC.name,6)) AS SITE_ID
If you need to consider leading zeros or not, code the comparison that way. Unless you have a CHECK condition to prevent non-numeric chars in the last 6 bytes, any varchar column will eventually get garbage in it rather than numerics.
WHERE SITE_ID IN ('017588', '17588')
I am forcing it to be INT because of business requirements.
The invalid I'm receiving when using that column is because of the sequence in which the optimizer is rendering the query. If I treat site_id as a literal in the query as you show, the same error returns.
If you read up further there is a good discussion on the root cause.
The band-aid fix is to make the view a table, and then the query over it works just fine. Not elegant, but it works.
if the table on disk thing is working for you just create a swl job that runs ever 15 minutes or however many time you get new data (hourly?) and dump the data to a flat table using that view.
or use a store procedure that does the select from that view into a #tēmptable and select from that
1 Like
Yes, I have a series of steps that run to get the data from the data dictionary in a view, write it to a table, truncate the final output table and then populate the final output. A mapping dashboard web service then accesses the table and displays is at 'near' real-time.
They are validating the data and the maps in a bunch of different ways, when that gets a thumbs up, I'll schedule the jobs on their time requirements.
When I set out to do this, I wanted to make it real time with views over data, since everything comes from inside of the database. The data loggers populate on different times, but near real time will have to do for them, at least for now.
You can try modifying the above to: TRY_CONVERT(INT,RIGHT(AC.name,6)) AS SITE_ID
The problem is that when you query the view - SQL Server expands the view and optimizes the query based on the query that defines the view and the added criteria (WHERE SITE_ID = 17588). When SQL Server does that - the filtering for non-integer values is not performed until after the CONVERT...
TRY_CONVERT should return a NULL for those rows that cannot be converted - which will then be excluded when the WHERE clause is processed.
2 Likes
Good suggestion, good information. I'm not getting the error, and will add it to the testing scenario.
Thank you very much!