CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 sql code using distint fails: The text, ntext, or image data type cannot be selected as DISTINCT

Print topic Send  topic

Author Message
cecibela
Posted: 04/29/2004, 4:26 PM

can anyone see what is wrong with this code? Here is the thing. without 'CONVERT(VARCHAR(8000),..' it causes the following error:
Warning: odbc_exec(): SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image data type cannot be selected as DISTINCT., SQL state 37000 in SQLExecDirect in

But if I put in the code, it works fine except that I do not get the data for the 'd.pub_authornames'!! I am hitting again MS SQL server from MySQL on windows 2000.
---start---
SELECT distinct
a.spec_facid,
a.spec_description,
b.bio_lastname,
b.bio_firstname,
d.pub_pubtype,
d.pub_pubname,
d.pub_pubdate,
d.pub_volume,
d.pub_spage,
d.pub_epage,
d.pub_publocaltype,
d.pub_pubdate,
CONVERT (VARCHAR(400),d.pub_authornames),
d.pub_title
FROM vPharmacy_Specialties a, vPharmacy_Biographical b, vPharmacy_PublicationAuthors c, vPharmacy_Publications d
where a.spec_facid = b.bio_facid AND c.puba_facid = b.bio_facid AND c.puba_id = d.pub_id
AND a.spec_description ='PRCW' AND d.pub_pubtype='Journal Article'
AND d.pub_pubdate>='1/1/2000'
---end ---
if you know how to fix this please let me know. thank you.
p3ptools

Posts: 19
Posted: 04/30/2004, 12:16 AM

Hello,

a couple of things

1) Have you tried running the above query in Query Analyzer
to see whats being returned.

2) Ensure your conversion VARCHAR(400) matches the data definition
of the column d.pub_authornames

3) Try aliasing d.pub_authornames to another names such as
'aname'

4) in CCS ensure the column output datatype is set to either text or memo.

Stephen
_________________
Sometimes the complaints will be false...
View profile  Send private message
ceci
Posted: 04/30/2004, 2:01 PM

thank you for your reply. I tried your suggestion and I am still having problems. One of the problems is that I am pulling the data from someone else's machine and I don't know what the data-definition is.

You see, if I get rid of the 'DISTINCT' word it works find BUT it returns duplicates.

So may be my question should be, how to I eliminate duplicates from the code above? Someone said that I should use UNION ALL b/c distinct does not work on a column that is of text type. But I can't get it to work either.

Any other ideas? thank you again.
peterr


Posts: 5971
Posted: 04/30/2004, 2:28 PM

Your database error message looks clear.
And looking at:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=U...%3DN%26tab%3Dwg

Quote :
"Distinct is actually implemented as Group By, so you have to obey rules for
the Group By clause. Here is from books OnLine:
"You cannot use GROUP BY or HAVING on ntext,text,image, or bit columns
unless they are in a function that returns a value having another data type.
Examples of such functions are SUBSTRING and CAST."

The answer looks clear in the sense that if any of your fields is ntext, text or image then you simply won't be able to use DISTINCT or GROUP BY, both used to filter or group duplicate records.
Probably the SUBSTRING and CAST functions mentioned in the above quote could help.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com
View profile  Send private message
peterr


Posts: 5971
Posted: 04/30/2004, 2:34 PM

BTW, I recommend that you don't limit yourself only to these (CCS) forums when looking for answers to SQL issues. There are plenty of SQL resources out there, Google and even someone on Microsoft newsgroups may be able to help you.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.