cancel
Showing results for 
Search instead for 
Did you mean: 

Evaluate date field, use only if not null/empty in query.

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member194571
Active Participant
0 Kudos

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

Former Member
0 Kudos

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.

former_member194571
Active Participant
0 Kudos

select LastReqTime, BlockedOn, UncommitOps

FROM sa_conn_info()

WHERE NULLIF (LastReqTime, '') < DATEADD(mi,-60,GETDATE())

  AND BlockedON > 0 AND UncommitOps = 0

Former Member
0 Kudos

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!

VolkerBarth
Active Participant
0 Kudos

> 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

Former Member
0 Kudos

Volker,

I had to read that a few times. I think

the below text should sum it up.

"Select ouch FROM Possible_headache

WHERE ISPAINFUL sql_logic AND ISCONFUSING sql_syntax

(WHERE NULLIF (comprehension, ''))"

I probably hosed the syntax, but you get the idea.
Thanks again!

John

Former Member
0 Kudos

FYI, I tried to mark this one as correct but the option wasn't avail, just helpful, so I marked your reply to my comment with the clarification. Thanks again.

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

former_member194571
Active Participant
0 Kudos

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

Former Member
0 Kudos

As usual, you've answered my question and taught me something at the same time. Believe it or not, I'm unused to actually having to ask for help. Usually Im the "GoTo" guy, but I'm new to SQL and I face a steep learning curve. Thanks again for everything!

Answers (2)

Answers (2)

yzzhang
Advisor
Advisor
0 Kudos

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

former_member182948
Active Participant
0 Kudos

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

Former Member
0 Kudos

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.