LogViewPlus Support » LogViewPlus Support » Help & Support » impossible to create a select query with sub select ?

RE: impossible to create a select query with sub select ?

LogViewPlus Support — Thu, 30 Nov 2023 08:39:58 GMT

Just a quick note to let you know that we have now released LogViewPlus v3.0.24 as a BETA release.

This release addresses SQL issue discussed above which was related to localization. The fix was relatively straight-forward, but unfortunately the change was bundled with a lot of other changes which needed additional testing before we could release. We would have liked to fix this one faster - apologies for the delay.

This release also adds the ability to embed a subquery statement into select field. The original SQL statement you posted should now be valid.

Thanks again for bringing this issue to our attention. Please do let me know if you have any further questions or issues.

Toby

impossible to create a select query with sub select ?

DL9442 — Thu, 30 Nov 2023 08:34:01 GMT

Hello,

I would have liked to create the following query to get an http response time distribution

SELECT 'bigger than 3sec', count(*) as nbrequestbelowtime, (SELECT count(*) as nb_of_request_total FROM CurrentView where cs-uri LIKE '%/suite/sites/cpr%') as total
FROM CurrentView
where cs-uri LIKE '%/suite/sites/cpr%' AND time-taken > 3000;

but after a while i get the following error

---- Query Failed ----

Unable to interpret SELECT statement. Field, function, or expression expected.

What could be wrong with this query ?

RE: impossible to create a select query with sub select ?

LogViewPlus Support — Wed, 11 Oct 2023 11:47:11 GMT

What version of LogViewPlus are you using? I am not able to recreate the problem locally:

I think this might be an issue with culture localization (this post might be a similar issue). The first error message you reported does not come from LogViewPlus, but the second one does.

RE: impossible to create a select query with sub select ?

DL9442 — Wed, 11 Oct 2023 11:27:37 GMT

If i execute the subselect alone, i get the following result

+---------------------+
| nbabovetime | total |
+---------------------+
| 21912 | 27061 |
+---------------------+

and if i try to extract the percentage with a outer select

SELECT nbabovetime,total, CAST(ROUND(100.0 * (nbabovetime / total), 2) AS DECIMAL) as Percent
FROM(
SELECT sum(isrequestabovetime) as nbabovetime , count(*) as total
FROM (
SELECT CASE WHEN time-taken > 1000 THEN 1 ELSE 0 END AS isrequestabovetime,cs-uri FROM CurrentView WHERE cs-uri LIKE '%/suite/webapi/%'
)
);

i get the Error in ROUND: 100.0 is not a valid value for Double.

Trying different other notations for 100.0 (100,0 or 100 since in Belgium we use different characters to separate decimals) gives

Function ROUND expects parameter 0 to be of type System.Decimal.

RE: impossible to create a select query with sub select ?

LogViewPlus Support — Mon, 09 Oct 2023 18:18:21 GMT

Thanks for reporting this issue.

I had a look at the Round function and couldn't find a matching error message, so I am not sure what could be going wrong. I have tested the 'Round' SQL statement above and it seems to be working fine. Do you know what data type you are passing into the Round function? Is '100.0' a string? Are you able to paste the full SQL statement you are trying to execute?

I also wanted to let you know that we have now implemented sub-queries in SELECT fields, so your original SQL statement will work in the next version of LogViewPlus (v3.0.23 or greater). Thanks for highlighting this issue.

RE: impossible to create a select query with sub select ?

DL9442 — Mon, 09 Oct 2023 09:22:18 GMT

Sorry, i've the following error now : Error in ROUND: 100.0 is not a valid value for Double.

RE: impossible to create a select query with sub select ?

LogViewPlus Support — Fri, 06 Oct 2023 14:48:08 GMT

That's a good solution - thanks for sharing.

To calculate the percentage, you can try:
[code language="sql"]SELECT nbabovetime,total, CAST(ROUND(100.0 * (nbabovetime / total), 2) AS DECIMAL) as Percent
FROM(
    SELECT sum(isrequestabovetime) as nbabovetime , count(*) as total
    FROM (
        SELECT CASE WHEN time-taken > 1000 THEN 1 ELSE 0 END AS isrequestabovetime,cs-uri FROM CurrentView WHERE cs-uri LIKE '%/suite/sites/cpr%'
    )
);[/code]

Hope that helps,

Toby

RE: impossible to create a select query with sub select ?

DL9442 — Fri, 06 Oct 2023 11:56:30 GMT

Thanks for the tips .... i've finally achieved my goal with the following query

SELECT nbabovetime,total FROM(
SELECT sum(isrequestabovetime) as nbabovetime , count(*) as total FROM (SELECT CASE WHEN time-taken > 1000 THEN 1 ELSE 0 END AS isrequestabovetime,cs-uri FROM CurrentView WHERE cs-uri LIKE '%/suite/sites/cpr%') );

Any clue to get the percentage at the end ? (nbabovetime/total)*100 is not accepted

RE: impossible to create a select query with sub select ?

LogViewPlus Support — Fri, 06 Oct 2023 10:11:02 GMT

Hi,

Thanks for reaching out. I think the problem here is that the LogViewPlus query engine does not support an embedded SQL statement as a field for a SELECT clause.

However, I think you can achieve a similar result with something like:

[code language="sql"]SELECT
'bigger than 3sec',
SUM(CASE WHEN time-taken > 3000 THEN 1 ELSE 0 END) AS nbrequestbelowtime,
COUNT(*) AS total
FROM CurrentView
WHERE cs-uri LIKE '%/suite/sites/cpr%'
GROUP BY cs-uri;[/code]
I have not tested that, but I think it should be close to what you are looking for.

Hope that helps,

Toby