Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
How to exclude duplicates from Select with UNION?
Message
 
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows XP SP2
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01108675
Message ID:
01108678
Views:
14
This message has been marked as the solution to the initial question of the thread.
On the second thought, why do I need UNION at all? I can try with left JOIN from Queue_Names and put filters into join condition...

This worked fine:
lcFilter1 = [Employee_Queue_Profiles.cUsGrLink_fk = '] + crsUsGrLink.cUsGrLink_pk + [']
lcFilter2 = [Queue_Names.cDepartment_Code = '] + ;
	strtran(left(cDepartments.cDepartment_Code,1),'C','O') + [']

text TO lcSQLStr TEXTMERGE noshow
   SELECT IIF(ISNULL(Employee_Queue_Profiles.cEmployee_Queue_Profiles_pk),0,1) as iSelected, ;
   NVL(Employee_Queue_Profiles.iDefault_queue_flag,0) as iDefault_Queue_flag, ;
   Queue_Names.cQueue_names_pk, Queue_Names.cQueue_name, ;
   Queue_Names.cDepartment_code, Queue_Names.cQueue_type_code, ;
   Queue_Names.cQueue_type_id ;
   FROM Queue_Names LEFT JOIN Employee_Queue_profiles ;
   ON Queue_Names.cQueue_Names_pk = Employee_Queue_Profiles.cQueue_Names_fk ;
   AND Employee_Queue_Profiles.iActive_Flag = 1 AND <<m.lcFilter1>> ;
   WHERE Queue_Names.cQueue_Name ;
   NOT LIKE 'UNASSIGNED%' AND <<m.lcFilter2>> ;
   ORDER BY 4
ENDTEXT
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform