DISTINCT w/ character data

Hello,
I need to eliminate duplicates from records containing a text data type.
Here is the query I try :
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
--
And here is the error I get :
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
--
The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?
TIADISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):
SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;
David Portas
SQL Server MVP
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"alto" wrote:

> Hello,
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
> --
> select NewsGroup.NewsGroupID,
> (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
> -- Line 10
> NewsGroup.OnlineFlag
> from...
> where...
> --
> And here is the error I get :
> --
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> --
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what
I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
> TIA
>
>sql

distinct query for table containing records more than 69347

What would be the best way to avoid distinct clause in query to get the result.My Query looks like

select distinct Col1 ,col2,col3,col4 from ABC where (1=1) group by Col1 ,col2,col3,col4 order by col1

ABC Contains records like

Col1 col2 col3 col4

a 1 1 1

a 2 1 1

B 2 2 2

B 1 1 1

;

;

;

how is the performance of disticnt query on Number of Rows ? HOw can i Improve my query performance if want to use distinct in my query? will adding indexes help to boost performance?

Thanks

You didn't tell us what is your desired outcome...

The WHERE clause can be removed.

The GROUP BY forces what you display.

Do you wish to collapse so that all 'a'/'B', etc is in one row?

If so, then something like:

Code Snippet

SELECT
Col1,
col2 = sum( Col2 ),
Col3 = sum( Col3 ),
Col4 = sum( Col4 )
FROM ABC
GROUP BY Col1

|||There is no reason you need both Distinct AND group by that I know of. How is the performance of the query as you have it? And why the where (1=1)... Is this part of a larger generated query?

Distinct email addresses in 2 tables with different field names

Hi everyone,
I have 2 tables with table A containing an 'email' field and table B
containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it possible
to issue a query that would return only the unqiue email addresses in these
3 fields? So a long list with no duplicate emails(distinct).
Thank you
Maz.Hi Maz,
Please don't post questions independently in multiple newsgroups. You
question has already been answered in .programming.
--
Jacco Schalkwijk
SQL Server MVP
"Maziar Aflatoun" <maz88@.rogers.com> wrote in message
news:STSEb.32573$2We1.12257@.news04.bloor.is.net.cable.rogers.com...
> Hi everyone,
> I have 2 tables with table A containing an 'email' field and table B
> containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it
possible
> to issue a query that would return only the unqiue email addresses in
these
> 3 fields? So a long list with no duplicate emails(distinct).
> Thank you
> Maz.
>
>

Distinct Count on non-numeric column AS 2000

I have a cube with a fact table containing figures as movements within months, therefore duplicating the associated references.

I need to be able to count the distinct references (text column), but in AS 2000 I cannot get a correct answer.

If I do a distinctcount on the Members of the dimension I get a lower count than there actually is.

I have tried placing the distinct references in a separate table joined to the fact table on the reference and then counting them, but that just gives me the total figure all the time. If I use distinctcount I get the same answer as using distinct count on the dimension, i.e. wrong!

This is very frustrating - does anyone have any ideas?

I know it would be fine in As2005 but we cannot upgrade just yet.

Thank you

Try to create a table in SQL with an identity column for each reference.

Change the way the fact tavle is loaded to the cube (may be using a view) and do the DistinctCount over the newly created column.

Hope it helps.

|||

Thanks for your suggestion.

I already have a table containing all the distinct references, which I build from the fact table (which is a proper table, not a view - I have encountered problems with counts when using views before!) link to the fact table by the reference and then create a hidden dimension with one level, the reference; it is the DistinctCount of these members which is coming out wrong, although when you look at the count of the dimension level it is correct.

Therefore I added an id column to this table and a corresponding level to the dimension and tried a DistinctCount of the Descendants of the dimension at the id level, but am still getting the incorrect count. I have looked at examples of the individual references that it is failing to count but cannot see any reason why.

|||

One question: are you doing the DistinctCount in the relational or in the OLAP?

If you do the DistinctCount in the relational and then feed it to the OLAP you must be aware that the sum of DistinctCounts is different from DistincCount of the sum.

I use views 99.9% of the time without problems.

My suggestion is to create a view for the facts to be able to return the id, the other metrics columns and all dimenstion columns. Then in the cube create the DistinctCount measure over the id column.

|||

I am now completely baffled by AS's inability to do a count.

As recommended I have added a numeric id column to the fact table relating to the reference I need to count, populated it, then created another cube as a copy of my original one but with only 1 measure, the DistinctCount of the ID column.

Even before I merge these 2 cubes into a virtual cube I can see that the count is STILL wrong - it shows in AS as 25538 whereas checking via SQL (i.e. select count(distinct ID) from FactCube) gives the correct answer of 25995.

This was the same incorrect number I was getting before when I tried a calculated member - where have the other 457 rows gone? I know of no other way to do this and really need some advice as to why AS appears unable to do a proper count. It is vital to the cube that this functionality is available.

We use Sybase as our relational database, in case this has any bearing on the matter.

Thank you

Rachel

|||

Hi,

I use Sybase IQ and little Sybase ASE as the source without problems. I think I had a problem with group by in IQ 12.6. However, it should not be relevant here.

Make sure the Relational query and OLAP query are comparable. Are you sure you do not have any other fact data, besides that query?

Get the select that AS2000 send to Sybase and analyze it. Then run it to compare the data.

DistinctCount is a very slow process, but in my experience accurate.

|||Thank you for that advice - I went through the SQL statement and eventually tracked down the problem to some dodgy data in one of the dimensions. Thank goodness for that!

Display images ...

Hello,
I have a customstrings.xml file containing the following:
<string id="sidContactDisplayFormat">{0} - {1} - {2}</string>
<string
id="sidContactFormatFields">contact_name,contact_p hone,contact_vip_flag</string>
In the ascx file I have:
DataStore("ContactsQuickFind").SetCustomColumn("co ntact_display",
"sidContactDisplayFormat", "sidContactFormatFields", "sidContactEmptyString")
which is using the field referenced in the customstring file.
then the display is done through a dropdownlist:
<aw:DropdownList ID="ddlContactQuickFind" RunAt="server"
PreserveItems="true" DataBindMode="GetData"
DataSourceName="ContactsQuickFind" DataTextField="contact_display"
DataValueField="contact_id" ItemDataSourceName="Contact"
ItemDataField="contact_id" OnSelectedIndexChanged="DoContinue"
AutoPostBack="true" Width="300">
<asp:ListItem Value="1">sidNoneDecorated</asp:ListItem>
<asp:ListItem Value="0">sidSelectResultCount</asp:ListItem>
</aw:DropdownList>
My question is I want to display the VIP flag instead of the file value in
the display:
<img src="http://pics.10026.com/?src=http://altiris3/AeXHD/images/icnVIP.gif" />
How can I do this?
Thanks
Dominique
I think you got the wrong newsgroup here... this has nothing to do with xml
and databases.
You may want to try one of the more generic xml groups.
Best regards
Michael
"Dominique" <Dominique@.discussions.microsoft.com> wrote in message
news:4C6C5A88-65C1-4A43-8996-4B2846ED0975@.microsoft.com...
> Hello,
> I have a customstrings.xml file containing the following:
> <string id="sidContactDisplayFormat">{0} - {1} - {2}</string>
> <string
> id="sidContactFormatFields">contact_name,contact_p hone,contact_vip_flag</string>
> In the ascx file I have:
> DataStore("ContactsQuickFind").SetCustomColumn("co ntact_display",
> "sidContactDisplayFormat", "sidContactFormatFields",
> "sidContactEmptyString")
> which is using the field referenced in the customstring file.
> then the display is done through a dropdownlist:
> <aw:DropdownList ID="ddlContactQuickFind" RunAt="server"
> PreserveItems="true" DataBindMode="GetData"
> DataSourceName="ContactsQuickFind" DataTextField="contact_display"
> DataValueField="contact_id" ItemDataSourceName="Contact"
> ItemDataField="contact_id" OnSelectedIndexChanged="DoContinue"
> AutoPostBack="true" Width="300">
> <asp:ListItem Value="1">sidNoneDecorated</asp:ListItem>
> <asp:ListItem Value="0">sidSelectResultCount</asp:ListItem>
> </aw:DropdownList>
> My question is I want to display the VIP flag instead of the file value in
> the display:
> <img src="http://pics.10026.com/?src=http://altiris3/AeXHD/images/icnVIP.gif" />
> How can I do this?
> Thanks
> --
> Dominique
|||thanks
Dominique
"Michael Rys [MSFT]" wrote:

> I think you got the wrong newsgroup here... this has nothing to do with xml
> and databases.
> You may want to try one of the more generic xml groups.
> Best regards
> Michael
> "Dominique" <Dominique@.discussions.microsoft.com> wrote in message
> news:4C6C5A88-65C1-4A43-8996-4B2846ED0975@.microsoft.com...
>
>

Display images ...

Hello,
I have a customstrings.xml file containing the following:
<string id="sidContactDisplayFormat">{0} - {1} - {2}</string>
<string
id="sidContactFormatFields"> contact_name,contact_phone,contact_vip_f
lag</str
ing>
In the ascx file I have:
DataStore("ContactsQuickFind").SetCustomColumn("contact_display",
"sidContactDisplayFormat", "sidContactFormatFields", "sidContactEmptyString"
)
which is using the field referenced in the customstring file.
then the display is done through a dropdownlist:
<aw:DropdownList ID="ddlContactQuickFind" RunAt="server"
PreserveItems="true" DataBindMode="GetData"
DataSourceName="ContactsQuickFind" DataTextField="contact_display"
DataValueField="contact_id" ItemDataSourceName="Contact"
ItemDataField="contact_id" OnSelectedIndexChanged="DoContinue"
AutoPostBack="true" Width="300">
<asp:ListItem Value="1">sidNoneDecorated</asp:ListItem>
<asp:ListItem Value="0">sidSelectResultCount</asp:ListItem>
</aw:DropdownList>
My question is I want to display the VIP flag instead of the file value in
the display:
<img src="http://pics.10026.com/?src=http://altiris3/AeXHD/images/icnVIP.gif" />
How can I do this?
Thanks
--
DominiqueI think you got the wrong newsgroup here... this has nothing to do with xml
and databases.
You may want to try one of the more generic xml groups.
Best regards
Michael
"Dominique" <Dominique@.discussions.microsoft.com> wrote in message
news:4C6C5A88-65C1-4A43-8996-4B2846ED0975@.microsoft.com...
> Hello,
> I have a customstrings.xml file containing the following:
> <string id="sidContactDisplayFormat">{0} - {1} - {2}</string>
> <string
> id="sidContactFormatFields"> contact_name,contact_phone,contact_vip_f
lag</s
tring>
> In the ascx file I have:
> DataStore("ContactsQuickFind").SetCustomColumn("contact_display",
> "sidContactDisplayFormat", "sidContactFormatFields",
> "sidContactEmptyString")
> which is using the field referenced in the customstring file.
> then the display is done through a dropdownlist:
> <aw:DropdownList ID="ddlContactQuickFind" RunAt="server"
> PreserveItems="true" DataBindMode="GetData"
> DataSourceName="ContactsQuickFind" DataTextField="contact_display"
> DataValueField="contact_id" ItemDataSourceName="Contact"
> ItemDataField="contact_id" OnSelectedIndexChanged="DoContinue"
> AutoPostBack="true" Width="300">
> <asp:ListItem Value="1">sidNoneDecorated</asp:ListItem>
> <asp:ListItem Value="0">sidSelectResultCount</asp:ListItem>
> </aw:DropdownList>
> My question is I want to display the VIP flag instead of the file value in
> the display:
> <img src="http://pics.10026.com/?src=http://altiris3/AeXHD/images/icnVIP.gif" />
> How can I do this?
> Thanks
> --
> Dominique|||thanks
--
Dominique
"Michael Rys [MSFT]" wrote:

> I think you got the wrong newsgroup here... this has nothing to do with xm
l
> and databases.
> You may want to try one of the more generic xml groups.
> Best regards
> Michael
> "Dominique" <Dominique@.discussions.microsoft.com> wrote in message
> news:4C6C5A88-65C1-4A43-8996-4B2846ED0975@.microsoft.com...
>
>

Display image in Crystal Report 10

I have to generate a report containing data and images in the database, e.g. product information + product image (kept as "image" type in MSSQL database). The information and images are kept in one table named "ProductInfo". The no. of images may vary depending on user input, e.g. on-shelf date. The expected output of the report is similar to below:

<<image1>>
Item: ABC Shampoo
Product No.: 1111

<<image2>>
Item: XXX Toothpaste
Product No.: 2222

<<image3>>
Item: YYY Shaver
Product No.: 3333

User will input the date range in a VB form and a Crystal Report Preview will show the output. I am currently using VB.NET 2003, Crystal Report 10, MSSQL 2000. Would you please help? Thanks very much.

Regards,Store image path in database field, then use picture box in detail section to display pictures.

U can use graphic location property in format editor of picture box...