Caché iSql result differs from CSP/COS method with (nearly) same query... Help!

[Micky Hulse]

I am stumped.

When I run this SQL via Caché iSql app:

<https://gist.github.com/1128261#file_query1.txt>

I get the desired 46 results.

When I put the above query in the context of a CSP method (link to code below), the result set returns 45 results.

<https://gist.github.com/1128261#file_test.csp>

What could possibly cause the query to differ by one item?

Long story short, I am expecting today's story to appear at the top of the query... Unfortunately, that's the one story missing from the CSP method result set.

Any tips?

Thanks so much for you help!

Cheers,
Micky

[Micky Hulse]

Note: I updated the date in the raw SQL to match the exact date
generated by the CSP method.

Now, the only only difference between the queries is:

AND sc.subCategoryId = 191173

(raw SQL)

... versus:

"AND sc.subCategoryId %INLIST ? " _

(CSP method)

... but I don't think that is the problem.

Could it be query cache? This query _was_ living on a CSP page with
these headers:

<script language="cache" method="OnPreHTTP" arguments="" returntype="%Boolean">

do %response.SetHeader("Content-Type", "application/xml")
set %response.CharSet = "utf-8"
do %response.ExpiresSet("3600") // 3600 seconds (60 minutes) from now.

quit 1 // Exit.

</script>

And I _was_ using

set rs = ##class(%ResultSet).%New("%DynamicQuery:SQL")

... instead of:

set rs = ##class(%ResultSet).%New()

I MUST be overlooking something really obvious here! (If so, I
apologize in advance for wasting all ya'lls time).

Thanks so much!

Cheers,
Micky

[Micky Hulse]

Sent from my iPhone

[Micky Hulse]

I noticed that the CSP version will update about a day later... In other words, the latest "story" will show up a day too late and the latest stories will not show (until a day, more or less) later.

I must be overlooking something obvious here.

Any tips? Has anyone else been in a similar situation?

Sent from my iPhone

[Andrey]

Hi Micky!

The problem here is the method you use to get the value for "now".

set now = $zdate(+$h, 3)
gives you "2011-08-07", which is the %TimeStamp "2011-08-07 00:00:00".

On other hand, your most recent "story" probably has "runDate" like
"2011-08-07 10:12:14", which is greater then your calculated
"now" (because of time part).

As for the right results that you are getting running the query from
the management portal - have no idea, maybe it is because of some dark
magic inside the portal code :)

WBR,
Andrey

[Micky Hulse]

On Mon, Aug 8, 2011 at 3:57 AM, Andrey <andre...@gmail.com> wrote:
> The problem here is the method you use to get the value for "now".

Andrey... THANKS!!!!

You just made my Monday! :D

I added these two lines:

set soon = $piece($SYSTEM.SQL.DATEADD("d", +2, now), " ")

... and further down the script:

set sc = rs.Execute(then, soon, catList)

That gave me the results I needed! WOOT! Thanks so much for the assist
on this one, I really appreciate it! :D

> On other hand, your most recent "story" probably has "runDate" like
> "2011-08-07 10:12:14", which is greater then your calculated
> "now" (because of time part).

Actually, our "runDate"s look like:

2011-08-07 00:00:00.0

But maybe "BETWEEN" does not include the two timestamps that it's
querying between?

> As for the right results that you are getting running the query from
> the management portal - have no idea, maybe it is because of some dark
> magic inside the portal code :)

Yah, the differing results totally threw me for a loop!

Actually, I was using a Java app called Caché iSql... I am not sure
who wrote it... I guess I assumed it came packaged with Caché, but now
I am thinking it is an app specific to our DTI software.

Anyway, I probably should have tested the raw SQL via the SMP to see
how the results varied from the other two sources.

Well, thanks a billion Andrey! I greatly appreciate the help!!!

Have an excellent day!

Cheers,
Micky