Hi everyone, :) :)
I have a problem if you could help. In a Calculation of my Subject Query, I have the following,
If ([XXX_View].[XXX].[TYPE_CD] ='18') THEN
(1)
else
(0)
Unfortunately, this doesn't give me unique/distinct value for every row with the TYPE_CD='18'.
The main Query is a combination of Unions between four different tables, so I'm trying to get
the distinct values without missing around with my main Query.
Do you know a way to use just a count statement to get the distinct values?
thanks so much for your time.
Getting close but not yet. After using the following in the Calculation of the Subject Query (from the datasource)
count (distinct if ( [UCM Physical View].[STATE_OF_UNION].[REC_TYPE_CD] = 18 ) then ( ([UCM Physical View].[STATE_OF_UNION].[UCM_PACTV_DATA_PROJ_REC_ID] ) else NULL )
Getting the following error,
RQP-DEF-0143 Invalid object.
The SQL Query is involved with four unions between four main tables. To clear things up, I'm pulling the unique Employee IDs from each table in the following fashion in the SQL
Select depart_num,employee_name, departmenta_employee_id from department1 UNION
Select depart_num,employee_name, departmentb_employee_id from department1 UNION
Select depart_num,employee_name, departmentc_employee_id from department1 UNION
Select depart_num,employee_name, departmentd_employee_id from department1
So departmentb_employee_id includes the unique employee_id from all four departments.
Since it's UNION, I have to have the same # of columns.
So, now, I'm try to get a distinct count for departmentb_employee_id based on their depart_num = 18, and
that is where I get stuck.
Thanks again from your time. :'(