on 05-18-2016 2:17 PM
We have this query:
select LastReqTime,
BlockedOn,
UncommitOps
FROM sa_conn_info()
WHERE LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedON > 0 AND UncommitOps = 0
Normal data example would be 2016-05-17 17:03:42.721 in LastReqTime.
As one can see by my code, I'm checking for LastReqTime older than 60 minutes. If the field is null or empty, the code errors. I need an IF or CASE or even a nested select statement that will allow me to check the LastReqTime for notnull, I would guess !='' OR perhaps a field length greater than 0. I was hoping for a valid date/time evaluation, but apparently it's quite a bit more complicated than that Since I need results fast I figured I'd ask the experts here (of course it would be here). Thanks in advance.
Hi John,
I guess your problem is that things aren't the way we might intuitively expect them to be.
While LastReqTime looks like a datetime, it technically is a varchar (255) as you'll see in the sa_conn_info reference. As a bonus, an empty LastReqTime is not null, but an empty string (blank string?). I can't currently retrieve a row with an empty value, in case of doubt check the length of a value that you have trouble with or retrieve the ASCII () function. Assuming it is an empty string, check for NULLIF (LastReqTime, '') instead of just LastReqTime. You might also use the ISDATE () function which indicates if the string value is a valid date and/or time.
HTH
Volker
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Volker,
That explains quite a bit. I've done extensive programming and scripting, but this is my first foray into SQL queries. The logic and construct is different from what I know, and unfortunately I need a working module as soon as possible. I'll ask Professor Google and see if I can figure out how to work NULLIF into my query. Thanks for your reply.
Thanks Volker,
So after looking at the NULLIF function help section,
if the LastReqTime = '' , NULLIF will return a null to the query,
which I assume will stop the processing. If LastReqTime isn't
empty, LastReqTime will be returned to the query and the
processing will continue. TBH, I was trying to come up with
CASE/IF statements on my own, and not having much luck,
but your suggestion is elegant in its simplicity, which is how
I like to code; the fewer moving parts the better!
> a null to the query,which I assume will stop the processing
Just to confirm your correct assumptions - for your further journey into the three-value logic wonderland...
A "<" comparison with a NULL value will evaluate to UNKNOWN, and UNKNOWN AND whatever will again evaluate to UNKNOWN, and the WHERE clause will only return rows that evaluate to TRUE, not those evaluated as UNKNOWN or FALSE.
Best regards
Volker
Volker,
If you're willing, can you please tell me how to sort the returns on LastReqTime so that the one with the oldest time is on the top of the list? And the one with the most ocurrences in LastReqTime?
Basically, if I need to drop the BlockedOns, I want to know when they began and which BlockedOn number has the most occurrences so I can drop either by date first, or # first.
Sample output:
I've already asked Professor Google and tried several different methods with abysmal results.
Apparently my understanding of SQL logic really is as bad as the joke code I put in the other
comment.
I stumbled upon Order By, and found that will sort by date and BlockedOn value, depending on which one I use as an argument. Next foray is to try to figure out how to get it to show ALL returns, and not just the first 115, without having to click to sort .
select LastReqTime,
BlockedOn,
UncommitOps
from sa_conn_info()
WHERE LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedOn > 0 AND UncommitOps = 0 ORDER BY LastReqTime
Just keep in mind that, again, LastReqTime is a varchar, so sort order will be alphabetically rather than chronologically. For values representing a correct datetime value, that will work.
To retrieve more than the default 115 rows, I suggest you change the Interactive SQL options (menu Tools item Options...).
My usual changes are
- Category "SQL Anywhere" tab "Results"
-- Maximum number of rows to display: 50000
-- Truncation length: 32750
- Category "Editor" tab "Text Completion" (I don't precisely remember if they're both checked by default)
-- uncheck "Open automatically"
-- uncheck "Complete automatically"
HTH
Hi John,
I wonder if you could try add 'LastReqTime is not null', like this:
select LastReqTime,
BlockedOn,
UncommitOps
FROM sa_conn_info()
WHERE LastReqTime is not null and LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedON > 0 AND UncommitOps = 0
Regards,
Zhang Yun-zheng
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi John,
Unfortunately, I don't fully understand your issue.
So I talk based on my guess.
ISDATE function tests if a string argument can be converted to a date (/datetime).
You can rewrite the query as follows.
SELECT * FROM
(select LastReqTime,
BlockedOn,
UncommitOps
FROM sa_conn_info()
WHERE ISDATE(LastReqTime) = 1
) SubQuery1
WHERE LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedON > 0 AND UncommitOps = 0
Please see the following document for more information about ISDATE function.
http://dcx.sap.com/index.html#sqla170/en/html/81f9466d6ce21014894fd76b048c76ab.html
Regards,
Koichi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Kiochi.
Thank you for your response. I guess I should have explained a bit more when I posted.
The main problem I had was that null values in LastReqTime were causing a "cannot convert"
error when the query tried to see if the LastReqTime was older than 60 minutes. I need
a way to have the query not process a record if LastReqTime is empty or null.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.