pr3mium
Posts: 31
|
| Posted: 02/17/2004, 10:33 PM |
|
Say I have two tables, which I join using query builder.
How could I make the select distinct query to prevent any duplicates to be viewed? One of the tables contains two ntext fields and if I use SQL datasource type and test the syntax, the CCS says: "The text, ntext, or image data type cannot be selected as DISTINCT"
Thanks,
|
 |
 |
peterr
Posts: 5971
|
| Posted: 02/17/2004, 10:43 PM |
|
You can change the Data Source from "Table" to "SQL" and then use the DISTINCT keyword in your SQL. Basically the same what you did. However, you need to utilize a valid SQL syntax. CCS doesn't produce such error messages. It is your database that says it, and CCS only shows you the error message from your database.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
pr3mium
Posts: 31
|
| Posted: 02/17/2004, 11:15 PM |
|
I'd like to perform a following query:
SELECT DISTINCT *
FROM emp_view LEFT JOIN Personal ON
emp_view.PA04002 = Personal.emp_kood
WHERE PA04003 LIKE '%{s_PA04003}%'
AND ( emp_view.PA04078 = 0 )
ORDER BY pri
The emp_view table has 79 fields and Personal table has 14. There are two ntext fields in Personal table, which prevents me to distinct select the records.
Any ideas, how to perform the query to prevent duplicates and get past the ntext fields (these are not necessary in a mentioned query). Is there any other way to make the query than just typing in SELECT DISTINCT field1,field2,field3,field4, etc for 91 times? If not, then I just have to accept it, because fortunately there are only two records that occur twice in emp_view table.
|
|
|
|
DonB
|
| Posted: 02/18/2004, 2:03 PM |
|
One way is to use a SELECT in the WHERE. Then you can SELECT * FROM
sometable WHERE somekey IN (SELECT DISTINCT somekey FROM sometable).
See also the EXISTS keyword in your SQL language. Also consider a NOT IN
that finds and "substracts" the duplicates. If you can get your hands on a
copy of SQL For Smarties by Joe Celko, that is an excellent reference for
these types of complexities.
I prefer not to type in long lists of column names, not because it's a lot
of typing, but because the database might change later and you will have to
go back and edit queries all over the place.
--
DonB
http://www.gotodon.com/ccbth
"pr3mium" <pr3mium@forum.codecharge> wrote in message
news:6403310f553994@news.codecharge.com...
> I'd like to perform a following query:
> SELECT DISTINCT *
> FROM emp_view LEFT JOIN Personal ON
> emp_view.PA04002 = Personal.emp_kood
> WHERE PA04003 LIKE '%{s_PA04003}%'
> AND ( emp_view.PA04078 = 0 )
> ORDER BY pri
> The emp_view table has 79 fields and Personal table has 14. There are two
ntext fields in Personal table, which prevents me to distinct select the
records.
> Any ideas, how to perform the query to prevent duplicates and get past the
ntext fields (these are not necessary in a mentioned query). Is there any
other way to make the query than just typing in SELECT DISTINCT
field1,field2,field3,field4, etc for 91 times? If not, then I just have to
accept it, because fortunately there are only two records that occur twice
in emp_view table.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
GeorgeS
|
| Posted: 02/23/2004, 9:49 PM |
|
My understanding is that Distinct is used with one field
& Distinct Row with all fields as in your example.
|
|
|
|
DonB
|
| Posted: 02/24/2004, 6:00 AM |
|
Caution - I'm pretty sure that "DISTINCT ROW" is not an ANSI standard
construct, and I am not aware if any database other than Access supports it.
--
DonB
http://www.gotodon.com/ccbth
"GeorgeS" <GeorgeS@forum.codecharge> wrote in message
news:6403ae5fce0717@news.codecharge.com...
> My understanding is that Distinct is used with one field
> & Distinct Row with all fields as in your example.
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
