May 18, 2012 at 10:59 am
I execute the following Statement:
Select 'DATED_TEXT','DATED_TEXT_RTF',DATED_TEXT_RTF, count(*) AS RecordCount From DATED_TEXT group by DATED_TEXT_RTF;
I get the following error:
Msg 306, Level 16, State 2, Line 582
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
The column is a Text Column.
How can I avoid the error?
I'm trying to get cadinality and identify whether the column is populated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2012 at 11:13 am
Welsh Corgi (5/18/2012)
I execute the following Statement:
Select 'DATED_TEXT','DATED_TEXT_RTF',DATED_TEXT_RTF, count(*) AS RecordCount From DATED_TEXT group by DATED_TEXT_RTF;
I get the following error:
Msg 306, Level 16, State 2, Line 582
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
The column is a Text Column.
How can I avoid the error?
I'm trying to get cadinality and identify whether the column is populated.
Cast the column as varchar(max).
May 18, 2012 at 11:42 am
Thanks Lynn but I tried casting the column but I can't get the syntax right.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2012 at 12:05 pm
Welsh Corgi (5/18/2012)
Thanks Lynn but I tried casting the column but I can't get the syntax right.
CREATE TABLE dbo.ATest(
TestId INT IDENTITY(1,1),
TestData TEXT
);
INSERT INTO dbo.ATest (TestData)
SELECT 'Row1' UNION ALL
SELECT 'Row1' UNION ALL
SELECT 'Row1' UNION ALL
SELECT 'Row2' UNION ALL
SELECT 'Row2' UNION ALL
SELECT 'Row3' UNION ALL
SELECT 'Row4' UNION ALL
SELECT 'Row4' UNION ALL
SELECT 'Row5' UNION ALL
SELECT 'Row5';
GO
SELECT * FROM dbo.ATest;
GO
-- this will fail
SELECT TestData, COUNT(TestData) FROM dbo.ATest GROUP BY TestData;
GO
-- this will work
SELECT CAST(TestData AS VARCHAR(MAX)), COUNT(CAST(TestData AS VARCHAR(MAX))) FROM dbo.ATest GROUP BY CAST(TestData AS VARCHAR(MAX));
GO
DROP TABLE dbo.ATest;
GO
May 18, 2012 at 12:09 pm
Also:
CREATE TABLE dbo.ATest(
TestId INT IDENTITY(1,1),
TestData TEXT
);
INSERT INTO dbo.ATest (TestData)
SELECT 'Row1' UNION ALL
SELECT 'Row1' UNION ALL
SELECT 'Row1' UNION ALL
SELECT 'Row2' UNION ALL
SELECT 'Row2' UNION ALL
SELECT 'Row3' UNION ALL
SELECT 'Row4' UNION ALL
SELECT 'Row4' UNION ALL
SELECT 'Row5' UNION ALL
SELECT 'Row5';
GO
SELECT * FROM dbo.ATest;
GO
-- this will fail
SELECT TestData, COUNT(TestData) FROM dbo.ATest GROUP BY TestData;
GO
-- this will work
SELECT CAST(TestData AS VARCHAR(MAX)), COUNT(CAST(TestData AS VARCHAR(MAX))) FROM dbo.ATest GROUP BY CAST(TestData AS VARCHAR(MAX));
GO
-- this will work using a cte to convert the column
WITH BaseData AS (
SELECT
CAST(TestData AS VARCHAR(MAX)) TestData
FROM
dbo.ATest
)
SELECT TestData, COUNT(TestData) FROM BaseData GROUP BY TestData;
GO
DROP TABLE dbo.ATest;
GO
May 18, 2012 at 1:10 pm
Thanks Lynn.
Your good.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2012 at 5:55 am
They slightly changed the requirements on me and unfortunately I'm not getting the right numbers.
I execute this statement:
SELECT 'ACCOUNT' AS TableName,'ACCOUNT_ID' AS ColumnName, CAST(ACCOUNT_ID AS VARCHAR(MAX)) AS DistinctValues, MIN(CAST(ACCOUNT_ID AS VARCHAR(MAX))) AS MinValue,MAX(CAST(ACCOUNT_ID AS VARCHAR(MAX))) AS MaxValue FROM ACCOUNT GROUP BY CAST(ACCOUNT_ID AS VARCHAR (MAX));
TableNameColumnNameDistinctValuesMinValueMaxValue
ACCOUNTACCOUNT_ID10 10 10
ACCOUNTACCOUNT_ID8 8 8
ACCOUNTACCOUNT_ID9 9 9
What I should get is the following:
TableNameColumnNameDistinctValuesMinValueMaxValue
ACCOUNTACCOUNT_ID3 8 10
I noticed that I'm missing the distinct operator, when I add it I get a syntax error.
It is not grouping correctly.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2012 at 6:04 am
I tried to do this using the example but I'm getting a sysntax error.
SELECT 'ACCOUNT' AS TableName,'ACCOUNT_ID' AS ColumnName,
CAST(DISTINCT(TestData) AS VARCHAR(MAX)) AS DistinctValues,
MIN(CAST(TestData AS VARCHAR(MAX))) AS MinValue,MAX(CAST(TestData AS VARCHAR(MAX))) AS MaxValue
FROM #ATest
GROUP BY CAST(TestData AS VARCHAR (MAX));
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2012 at 6:14 am
Total guess having not seen sample data or DDL.
SELECT TableName, ColumnName, COUNT(ColumnName) AS DistinctValues,
MIN(MinValue), MAX(MaxValue)
FROM (SELECT
'ACCOUNT' AS TableName,
'ACCOUNT_ID' AS ColumnName,
--CAST(ACCOUNT_ID AS VARCHAR(MAX)) AS DistinctValues,
MIN(CAST(ACCOUNT_ID AS VARCHAR(MAX))) AS MinValue,
MAX(CAST(ACCOUNT_ID AS VARCHAR(MAX))) AS MaxValue
FROM ACCOUNT
GROUP BY CAST(ACCOUNT_ID AS VARCHAR(MAX))
) a
GROUP BY TableName, ColumnName;
If I'm right, dissect what I've done and rethink your logic as this is complicating a simple query.
May 21, 2012 at 6:22 am
It is close. It returns 9 as the Min value when it should be 8.
I'm using a concatenate function in Excel.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2012 at 6:48 am
Welsh Corgi (5/21/2012)
It is close. It returns 9 as the Min value when it should be 8.I'm using a concatenate function in Excel.
Without DDL and sample data that reflects what you're saying, I'm not sure what you expect to be done.
Currently, from what you're saying, the first query (Lynn's) returns this: -
SELECT *
FROM (VALUES('ACCOUNT','ACCOUNT_ID',10,10),
('ACCOUNT','ACCOUNT_ID',8,8),
('ACCOUNT','ACCOUNT_ID',9,9))a(TableName,ColumnName,MinValue,MaxValue);
So, if we add this into the query I posted, you're saying that it returns: -
TableName ColumnName DistinctValues
--------- ---------- -------------- ----------- -----------
ACCOUNT ACCOUNT_ID 3 9 10
But if I execute it: -
SELECT TableName, ColumnName, COUNT(ColumnName) AS DistinctValues,
MIN(MinValue), MAX(MaxValue)
FROM (SELECT *
FROM (VALUES('ACCOUNT','ACCOUNT_ID',10,10),
('ACCOUNT','ACCOUNT_ID',8,8),
('ACCOUNT','ACCOUNT_ID',9,9))a(TableName,ColumnName,MinValue,MaxValue)
) a
GROUP BY TableName, ColumnName;
I correctly get: -
TableName ColumnName DistinctValues
--------- ---------- -------------- ----------- -----------
ACCOUNT ACCOUNT_ID 3 8 10
May 21, 2012 at 7:03 am
Welsh Corgi (5/21/2012)
It is close. It returns 9 as the Min value when it should be 8.I'm using a concatenate function in Excel.
It returns 9 when it should be 8? How do you know it should be 8? Why not 7 or 10?
You didn't post any sample data.
Ok, I guess you can see some value which have only 8 digits (or characters), but function returns a different number. It happens... Especially when you have some "invisible" characters such as trailing spaces, new line terminators (char(10) and char(13)), tabs etc...
May 21, 2012 at 7:31 am
It starting to look obvious, at least to me, that we are starting to cross threads here and things are getting fragmented and confused. Either pick one of you your forum threads regarding this issue and link the others to it, or start a new one and link all of the ones you have active right now to that new one.
Please, lets get this down to one thread and keep it there. It will keeps things organized better and we won't have to jumb around threads to figure out what is going on.
May 21, 2012 at 7:51 am
The reason that I;m not getting the correct result is due to the data being cast to character. For character data this is the expected result from the MIN/MAX functions.
CREATE TABLE #TmpAct(
Id INT IDENTITY(1,1),
ACCOUNT_ID TEXT
);
INSERT INTO #TmpAct (ACCOUNT_ID)
SELECT '8' UNION ALL
SELECT '9' UNION ALL
SELECT '10'
SELECT '#TmpAct' AS TableName,'ACCOUNT_ID' AS ColumnName, COUNT(DISTINCT CAST(ACCOUNT_ID AS VARCHAR(MAX))) AS NumDistinctValues, MIN(CAST(ACCOUNT_ID AS VARCHAR(MAX))) AS MinValue, MAX(CAST(ACCOUNT_ID AS VARCHAR(MAX))) AS MaxValue FROM #TmpAct;
I have closed the other thread.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2012 at 8:09 am
Much easier to answer you now.
SELECT '#TmpAct' AS TableName, 'ACCOUNT_ID' AS ColumnName,
COUNT(DISTINCT b.ACCOUNT_ID) AS NumDistinctValues,
MIN(c.ACCOUNT_ID) AS MinValue,
MAX(c.ACCOUNT_ID) AS MaxValue
FROM #TmpAct a
CROSS APPLY (SELECT CAST(a.ACCOUNT_ID AS VARCHAR(MAX))) b(ACCOUNT_ID)
CROSS APPLY (SELECT CAST(b.ACCOUNT_ID AS INT)) c(ACCOUNT_ID);
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply