Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Could someone kindly translate this QV statement to SQL statement for me?
SUM(AGGR(count(DISTINCT
{<
[ProcedureType] = {'*'} - {'ADJ'}
>}
[ProcedureID]), [ProcedureType],%KEY1, %KEY2, %KEY3))
Thanks
May be this
SELECT Sum(PROC_ID)
FROM(
SELECT
%KEY1,
%KEY2,
%KEY3,
Count(DISTINCT ProcedureID) as PROC_ID
FROM ....
WHERE ProcedureType not in ('ADJ')
GROUP BY
%KEY1,
%KEY2,
%KEY3);
Hi,
Thanks for the quick reply.
I ran your sql statement against my db. The total I am getting is much much bigger than the value
in the QV app.
Is there anything I'm missing?
Thanks
In the QV app, there's a variable which points to that QV command listed in my original post. This
number is much much smaller than from the sql command.
I am sorry. It was a mistake on my part. The number matches.
Thank you!!
Now that I know
[ProcedureType] = {'*'} - {'ADJ'} = ProcedureType not in ('ADJ')
Can you explain in English this part of the QV --> {'*'} - {'ADJ'}?
SELECT Sum(A.Sum_procedure)
FROM(
SELECT ProcedureType
%KEY1,
%KEY2,
%KEY3,
Count(DISTINCT ProcedureID) as Sum_procedure
FROM ....
WHERE ProcedureType not in ('ADJ')
GROUP BY
ProcedureType
%KEY1,
%KEY2,
%KEY3) A;
regards
Pradosh
Include all rows where ProcedureType has a value, but one of those values is not ADJ... So, include every row for ProcedureType except Null and ADJ
Hi Sunny,
Yes, I understand that's the outcome. I guess when I am reading the QV code. I am reading
it as '*' minus 'ADJ'. That's what's confusing to me. I guess I need to read up on the syntax documentation
in details.
I am not sure what you are confused about.... Think of it like this
'*' -> Everything which is not null
'Adj' -> Where the value is Adj
'*' - 'ADJ' -> Exclude 'Adj' from everything which is not null...
RowNo, ProecdureType
1, abc
2, def
3,
4, ADJ
5, AKJH
Assuming RowNo = 3 has ProcedureType Null, '*' -'ADJ' will include 1, 2, and 5
Does this make sense?