Cannot use DISCTINCT command when combining to columns in my query????

[MIKEJ3131]

Hello,

i am having a problem getting unique records when combining two columns in my query. Can the DISTINCT command be used with the CONCAT command or the || || operator. I am using MS Query and when i enter in my query with DISTINCT, it says "DISTINCT is not a SELECTed operator" and then it wont work. Can someone please help me out here. This is my query that i am using that works. This query returns a list of users by their last name then a comma then their first name, and then it sorts the names.

SELECT GE_USER.LNAME||', '||GE_USER.FNAME
FROM MSA.DE_ACCOUNT_EXEC DE_ACCOUNT_EXEC, MSA.DE_DEAL_HDR DE_DEAL_HDR, MSA.GE_USER GE_USER, MSA.GE_USER_JOB GE_USER_JOB
WHERE DE_DEAL_HDR.DEAL_ID = DE_ACCOUNT_EXEC.DEAL_ID AND GE_USER.GAB_ID = DE_ACCOUNT_EXEC.GAB_ID AND GE_USER.GAB_ID = GE_USER_JOB.GAB_ID AND ((GE_USER_JOB.JOB_ID=1))
ORDER BY UPPER(CONCAT(GE_USER.LNAME,GE_USER.FNAME))

Now when i go to add distinct in that qwuery, it will not work.

SELECT DISTINCT GE_USER.LNAME||', '||GE_USER.FNAME
FROM MSA.DE_ACCOUNT_EXEC DE_ACCOUNT_EXEC, MSA.DE_DEAL_HDR DE_DEAL_HDR, MSA.GE_USER GE_USER, MSA.GE_USER_JOB GE_USER_JOB
WHERE DE_DEAL_HDR.DEAL_ID = DE_ACCOUNT_EXEC.DEAL_ID AND GE_USER.GAB_ID = DE_ACCOUNT_EXEC.GAB_ID AND GE_USER.GAB_ID = GE_USER_JOB.GAB_ID AND ((GE_USER_JOB.JOB_ID=1))
ORDER BY UPPER(CONCAT(GE_USER.LNAME,GE_USER.FNAME))

Am i doing something wrong here?

 

[LindyBob]

Try this: SELECT DISTINCT CONCAT(x,y) ..