"Steve Kass" <skass@drew.edu> wrote in message
news:eINzvlwZEHA.524@TK2MSFTNGP09.phx.gbl...
> Please cross-post only by sending a single message to all groups!
>
>
> Johnny,
>
> There's nothing other than misspelling DISTINCT wrong as far as SQL
> Server (non-compact edition) goes. I suggest you cut and paste exactly
> what doesn't work, and also cut and paste the full error message, into
> your post, so we don't waste time puzzling over a retyped version that
> may or may not be the query you're running. The CREATE TABLE statement
> is important to see, too, since data types may matter.
>
> Steve Kass
> Drew University
>
> Johnny O. wrote:
>
> >Cross-posted from sqlserver.ce...
> >
> >Is is possible to construct a query SQLCE recognizes which will return a
> >list of distinct entries which are a substring of a column's text? I have
a
> >series of customer account numbers in column CAccount, such as:
> >
> >CAccount
> >10-23452
> >10-23456
> >10-23555
> >12-12455
> >12-13444
> >15-22421
> >
> >I'd like to bind a listbox to a dynamically populated data set which
> >includes the distinct entries of the first two characters in each
customer
> >account--ie, the listbox for the six entries above should appear as:
> >10
> >12
> >15
> >
> >For the life of me, I can't construct the query. This is the best guess I
> >had:
> >
> >SELECT DISTINCT SUBSTRING(CAccount,1,2) FROM
-OR-
> >SELECT DISCTINCT (SUBSTRING(CAccount,1,2)) FROM tblCustomers
> >
> >but I when I try to run the query in QueryAnalyzer, I get an error (The
> >specified argument value for the function is not valid)
> >
> >Is there no way to dynamically build this query? I suppose I could create
a
> >temp table, read the SUBSTRING into the table, and SELECT DISTINCT on it,
> >but I'd prefer not to add the complexity, etc.
> >
> >thx
> >
> >
> >
> >
>

Johnny O. wrote:

>Cross-posted from sqlserver.ce...
>
>Is is possible to construct a query SQLCE recognizes which will return a
>list of distinct entries which are a substring of a column's text? I have a
>series of customer account numbers in column CAccount, such as:
>
>CAccount
>10-23452
>10-23456
>10-23555
>12-12455
>12-13444
>15-22421
>
>I'd like to bind a listbox to a dynamically populated data set which
>includes the distinct entries of the first two characters in each customer
>account--ie, the listbox for the six entries above should appear as:
>10
>12
>15
>
>For the life of me, I can't construct the query. This is the best guess I
>had:
>
>SELECT DISTINCT SUBSTRING(CAccount,1,2) FROM tblCustomers -OR-
>SELECT DISCTINCT (SUBSTRING(CAccount,1,2)) FROM tblCustomers
>
>but I when I try to run the query in QueryAnalyzer, I get an error (The
>specified argument value for the function is not valid)
>
>Is there no way to dynamically build this query? I suppose I could create a
>temp table, read the SUBSTRING into the table, and SELECT DISTINCT on it,
>but I'd prefer not to add the complexity, etc.
>
>thx
>
>
>
>