Set Analysis Questions - need help

[Anonymous]

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

[sunny_talwar]

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);

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

I am sorry.  It was a mistake on my part.  The number matches. 

Thank you!!

[Anonymous]

Now that I know

[ProcedureType] = {'*'} - {'ADJ'} = ProcedureType not in ('ADJ')

Can you explain in English this part of the QV --> {'*'} - {'ADJ'}?

[pradosh_thakur]

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

[sunny_talwar]

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

[Anonymous]

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.

[sunny_talwar]

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?