Thank you Scott, I am getting an error
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
Thank you TaraKizer, But the following query returned nothing.
select cast(round(value,0) as INT)
from CV3BasicObservation (nolock)
where isnumeric(value) >= 110
These are the different types of values in this column, need only values >=110
0.2
134
4.9
93
29
80
5.5
<0.01
1.3
<2
<70
06MAY08
06-11-05
ALX 1604
9.81
109.5
2130
EZF 6592
2
0000
-68.7
ISNUMERIC returns true/false, for whether or not it is a numeric value. You are using this function to EXCLUDE the non-numeric values so that your query doesn't throw an error.
WHERE ISNUMERIC(yourcolumn) = 1 AND ...the rest of your stuff
1 Like
You can't just use ISNUMERIC() for a couple of reasons.
One, ISNUMERIC() accepts a lot of different formats, for example:
SELECT ISNUMERIC('10E0'),ISNUMERIC('2,3444'),ISNUMERIC('4D1'),ISNUMERIC('$')
All of those return "1" telling you the value "is numeric". Hmm, no, not really.
Two, even if the value truly isn't numeric, such as 'abc', SQL may execute the second part of the WHERE before or at the same time as the first. Thus, you can get non-numeric errors even with the check for numeric.
2 Likes
It worked, 
Thank you
For example, this code works:
SELECT *
FROM (
SELECT CAST('111' AS varchar(60)) AS value UNION ALL
SELECT 'abc'
) AS test_data
WHERE ISNUMERIC(value) = 1 AND CAST(value AS int) >= 110
but replace that WHERE with this one, and it fails:
WHERE CAST(value AS int) >= 110 AND ISNUMERIC(value) = 1
Ah, you think, just code them in order ... but there is no guarantee that SQL will run code in the order that you write it. This code could work fine for a while and suddenly fail. Naturally you don't want that.
SQL does "promise", otoh, to evaluate CASE statements in the order written. Thus, a CASE statement is a safe way to do this type of verification.
I put the WHERE ISNUMERIC(column)=1 in a derived table and then everything else goes outside of the derived table, that way I don't have to worry about the order that SQL processes it. But I'm also lucky that I've never actually had to support data like this, so I've only ever used the function when answering questions on the Internet. Hehe
select ...
from (select ... from table1 where isnumeric(column1) = 1) t
where ...
Even then, you have an issue in what "ISNUMERIC()" actually checks for. It accepts all of these types:
any size int
decimal / numeric
smallmoney / money
float / real
which leads to what I listed before:
--all these strings yield isnumeric = 1
SELECT ISNUMERIC('10E0'),ISNUMERIC('2,3444'),ISNUMERIC('4D1'),ISNUMERIC('$')
Yep understood. Those are all numeric data. Depending on the actual data, it may not be an easy way to filter the data out.
Thank you all, Sorry for bringing this back again, I have some more questions,
- The values which are in the field Itemcode='CreatinineLevel' have only numeric values a as I have given the condition bo.itemname =' Creatinine Level' so If I give a condition bo.value >=110 I do not get the right values. Is it possible to use CONVERT ??
I am talking about the query posted at the top
I do not understand your question. Post the query, sample data, expected output and what output you are getting.
1 Like
Sorry about the previous question, Please ignore it,
I have this sql, which is pulling more than 10642616 row(s)
Can I add ‘to’ and ‘from’ parameters based on alert created when (which comes in the sixth row in the select statement) ? so that I can get less data and less time to execute ? Should I declare a date variable ?
select cv.idcode as 'RHRN',
cv.visitidcode as 'Encounter',
cv.CurrentLocation as 'Location',
cpGrp.displayname as 'AttendingGroup' ,
ar.Description as 'AlertDescription',
ar.createdwhen as 'AlertCreatedWhen',
cast(bo.value as INT) as 'CreatinineLevel',
bo.CreatedWhen as 'LabResultCreatedWhen',
ctv.value as 'ClassTypeValue',
isnull (o.ordersetname,'') as 'OrderSetName',
o.idcode as 'OrderID',
o.name as 'OrderName',
me.DosageLow as 'Dose',
me.Uom as 'UOM',
o.RequestedDtm as 'OrderRequestedDTM' ,
o.OrderStatusCode as 'OrderStatus',
o.StopDtm 'OrderStopDTM'
into #tmp1
from cv3order o (nolock)
inner join cv3clientvisit cv (nolock) on o.ClientGUID = cv.ClientGUID and o.ChartGUID = cv.ChartGUID
inner join cv3CareProviderVisitRole cpvrGrp (nolock) on cpvrGrp.clientVisitGuid = cv.guid
inner join cv3CareProvider cpGrp (nolock)on cpGrp.guid = cpvrGrp.providerGuid and cpvrGrp.rolecode = 'attending group'
inner join cv3alertRepository ar (nolock) on ar.ClientVisitGUID = cv.guid
inner join cv3CatalogClassTypeValue ctv (nolock) on ctv.CatalogMasterGUID = o.OrderCatalogMasterItemGUID
inner join cv3ClassType ct (nolock) on ct.GUID = ctv.ClassTypeGUID
inner join CV3MedicationExtension me(nolock) on me.guid = o.guid
inner join CV3BasicObservation bo (nolock) on bo.ClientVisitGUID = cv.guid
where ar.mlmname = 'AHS_AKI_Creatinine_Result'
and ct.code = 'Acute Kidney Injury - AKI' and ctv.value in ('renally eliminated', 'nephrotoxicity')
and bo.ItemName = 'Creatinine LEVEL'
and cv.CurrentLocation not LIKE 'FMC-55%'
and cv.CurrentLocation not like 'FMC-37B%'
and cv.CurrentLocation not like 'ACH%'
and 1 = case when bo.value like '%[^0-9]%'
then 0 when cast(bo.value as int) >= 110 then 1 else 0 end
select
RHRN,
Encounter,
max(LabResultCreatedWhen) as 'lastResultWhen'
into #tmp2 -- get the most recent lab result
from #tmp1
group by
RHRN,
Encounter
select
RHRN,
Encounter,
max(AlertCreatedWhen) as 'lastAlertWhen'
into #tmp3 -- get the most recent alert
from #tmp1
group by
RHRN,
Encounter
select t1.*
from #tmp1 t1
inner join #tmp2 t2 on t1.LabResultCreatedWhen = t2.lastResultWhen
inner join #tmp3 t3 on t1.AlertCreatedWhen = t3.lastAlertWhen
order by t1.RHRN
drop table #tmp1
drop table #tmp2
drop table #tmp3
Yes, you can filter it further by adding the condition to the WHERE clause.
AND ar.createdwhen >= 'some begin date AND ar.createdwhen < 'some end date'
Yes you can use a variable. Just replace the variable for the values (some begin/end date).
1 Like
It would also be non-SARGable, meaning that an index could not seek because of the formula around the VALUE column in the WHERE clause..
1 Like
Thanks, I do not want to use the dates in the where clause, Instead I used the following,
DECLARE @theDate varchar(60)
SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59''' and in the where clause I used
ar.createdwhen BETWEEN @theDate
This gave me an error in the select statement where I select first from the temp table
DECLARE @beginDate date = '2010-01-01', @endDate date = '2010-09-01'
...WHERE ... ar.createdwhen >= @beginDate AND ar.createdwhen < @endDate
1 Like
Just wondering, Is it possible to use the cast in the #tmp1 table selection as the value returned for creatinine level is all numeric (stored in VARCHAR)
select t1.*
from #tmp1 t1
inner join #tmp2 t2 on t1.LabResultCreatedWhen = t2.lastResultWhen
inner join #tmp3 t3 on t1.AlertCreatedWhen = t3.lastAlertWhen
where cast(CreatinineLevel as NUMERIC) >= 110
I don't understand what you are asking. But if you want to use a value that was returned from a different query, just save it into a variable and then use the variable in the next query. A variable isn't required (there are other ways), but I didn't want to complicate it.
1 Like
My question was, the temp tables has the numeric value of Creatinine level only even though the value stored in varchar,
so Can I use the where condition >=110 in there ?