Sytax error near Distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sytax error near Distinct

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-13 : 10:13:00

Following 1st select runs fine, but when I added a column as in 2nd select, I got: Incorrect syntax near the keyword 'distinct'.
Why? I could not take out the distinct. What is the right syntax?
Thanks!

SELECT distinct right(AP_CHK_NO,6),
convert(decimal(10,2),F_PAY_AMT),
convert(varchar(10), AP_CHK_DAT, 105)
FROM AP_CHECKS
WHERE (BANK_NO = '20')and AP_CHK_DAT ='2004/12/08'

SELECT '01', distinct right(AP_CHK_NO,6),
convert(decimal(10,2),F_PAY_AMT),
convert(varchar(10), AP_CHK_DAT, 105)
FROM AP_CHECKS
WHERE (BANK_NO = '20')and AP_CHK_DAT ='2004/12/08'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-13 : 10:20:09

DISTINCT must always appear immediately after the SELECT.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-13 : 10:29:55

I have this piece to union all the previous one. It has distincts that are not right after Select. Maybe because they are in count() and sum(), then they are OK.

select '02','005290003184',count(distinct F_PAY_AMT),sum(distinct convert(decimal(10,2),F_PAY_AMT)), ''
FROM AP_CHECKS
WHERE (BANK_NO = '20')and AP_CHK_DAT ='2004/12/08'
group by AP_CHK_DAT

jhermiz

3564 Posts

Posted - 2004-12-13 : 10:32:47

That is because you are applying the DISTINCT to aggregate functions.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-13 : 10:58:18

this works:
select distinct ...
select count(distinct col1), sum(distinct col1)
select distinct count(distinct col1), sum(distinct col1)

this does not work:
select col1, distinct col2, ...

Go with the flow & have fun! Else fight the flow

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-13 : 11:02:19

So back to my original question, here are the options I can think of if I need to have a column return before the distinct column.

1. Use sub-select to glue them together.
Select '01', (select distinct XYZ from ...) as a
I have tried several variations, but have not get it working for me.

2. Store the 01 in a temp table to join it.

3. Create a new column in the main table, and populate it with '01's.

I hate to go down to 2 or 3, they are ugly.

What is your recommandation? Best practice? Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-13 : 11:06:45

????
You have your constant and a load of distinct rows. What do you want to put in all those rows if it isn't the constant - you must put something in each row.

select distinct '01', right(AP_CHK_NO,6),
convert(decimal(10,2),F_PAY_AMT),
convert(varchar(10), AP_CHK_DAT, 105)

will give you this. The constant won't affect the other distnct values as it is the same for each row.

There is no such thing as a distinct column. Resultsets are square so rows are distinct.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-13 : 11:24:29

Thank you for all the helps!
I guess I got lost on where to put my DISTINCT.
Here is the right one for those of you who are following the post.

SELECT distinct'01', right(AP_CHK_NO,6),
convert(decimal(10,2),F_PAY_AMT),
convert(varchar(10), AP_CHK_DAT, 105)
FROM AP_CHECKS
WHERE (BANK_NO = '20')and AP_CHK_DAT ='2004/12/08'
union all
select '02', count(distinct F_PAY_AMT),sum(distinct convert(decimal(10,2),F_PAY_AMT)),
''
FROM AP_CHECKS
WHERE (BANK_NO = '20')and AP_CHK_DAT ='2004/12/08'
group by AP_CHK_DAT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources