CODEWITHSUNDEEP
sql-server
Derek Jee
Derek Jee

Reputation: 147

Selecting from table where one column holds many data types as varchar

I have a log table which records changes in data in a table to a number of fields of different data types. It save the field name, the before and after saving values and more.. The table of data looks like this:

FieldName     Before      After

Category        lx           mt
AskingPrice   9.50        10.00
Name          Paul        Paula
AskingPrice   7.20         9.10
Category        za           pz

So the datatypes in the Before and After columns vary. When I try to write a query to select where the field is = AskingPrice and that the After value is greater than the Before value it errors with a data type conversion. After the error it does give the correct results but I guess the error is to do with it prechecking a data type in the column but choosing the first row.. The SQL I use is this:

SELECT FieldName, Before, After
FROM   Table WHERE Field = 'dblAskingPrice' and Before > After

Even if I cast the values it errors.. 

SELECT FieldName, Before, After FROM Table WHERE Field = 'dblAskingPrice' and 
Cast(Before as Numeric) > Cast(After as Numeric)

Now I can do a select inside another to select the fields and the second select to cast and compare but our software does not allow a select of a select statement.

Is there a way I can get this in one select statement without an error?

Upvotes: 2

Views: 1298

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

SQL databases do not guarantee the order items in the WHERE clause are evaluated.

I'll say it again: SQL databases do not guarantee the order items in the WHERE clause are evaluated.

Instead of the order items appear in your SQL command, a database will typically look at each condition and try to apply the conditions in the order it thinks will produce the result fastest and cheapest... typically, items that match best to an index or are the most restrictive will be applied first, with other items coming later. But you also can't rely on this behavior, because the query optimizer can do strange and unexpected things based on the statistics and load of the moment.

This means it's possible to try to Cast(Before as Numeric) before evaluating the Field = 'dblAskingPrice' expression that restricts you to records you know are safe to cast.

You can influence this by having an good index on FieldName,Before,After, but you can't guarantee things that way, especially given that the CAST() operation will typically not match up with the index. A Filtered Index might also help, but again: it's no guarantee.

You can sometimes get around this with a nested query, CTE, or a view that only includes certain records:

SELECT FieldName, Before, After
FROM (
    SELECT *
    FROM   [Table] 
    WHERE FieldName = 'dblAskingPrice'
) x
WHERE Cast(Before as Numeric) > Cast(After as Numeric)

But the best solution here typically involves fixing the broken schema. In this case, I suggest the way to fix the schema is to drop the table completely, and instead enable the change data capture feature that (as of Sql Server 2016 SP1) is now part of Sql Server Standard Edition, and use the auditing features built into Sql Server.

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33581

You can use a cte here to first filter out the rows you are concerned with. Then you can convert it to decimal. Something like this.

create table #something
(
    FieldName varchar(25)
    , Before varchar(25)
    ,After varchar(25)
)

insert #something
select 'Category', 'lx', 'mt' union all
select 'AskingPrice', '9.50', '10.00' union all
select 'Name', 'Paul', 'Paula' union all
select 'AskingPrice', '7.20', '9.10' union all
select 'Category', 'za', 'pz';

with MyCte as
(
    select *
    from #something
    where FieldName = 'AskingPrice'
)

select *
from MyCte
where CONVERT(decimal(7, 2), After) > CONVERT(decimal(7, 2), Before);

Upvotes: 1

Related Questions