DISTINCT returns duplicates

I use the DISTINCT keyword to avoid returning duplicates from the query
below (multivariable search for plants). However the result does contain
duplicates and I just can't figure out why. Please help!
TIA
CREATE PROCEDURE dbo.usp_MultivarSearch_Plantes
(
@.Value varchar(256) ,
@.CategoryID int ,
@.DescPlantHerb varchar(64) ,
@.AcclimatCanada varchar (64) ,
@.EnvCult varchar (145) ,
@.RareMenacee varchar (64) ,
@.PaysOrig int ,
@.PropTherapeut int ,
@.PropAutres int ,
@.Ingredients int
)
AS
set nocount on
SELECT DISTINCT
pTax.PlanteID ,
pTax.nomlatinscientifique ,
pTaxCat.CategoryID ,
pTax.descriptiontechniquebotanique ,
pCul.AcclimatationauCanada ,
pCul.environnementdeculture ,
pTax.raretemenacee ,
pPays.PaysID ,
pOPTH.ValueID ,
pOPA.ValueID ,
pOI.ValueID
FROM
PlantesTaxonomie pTax
left join PlantesTaxonomieCategory pTaxCat on pTax.PlanteID =
pTaxCat.PlanteID
left join PlantesCulture pCul on pCul.PlanteID = pTax.PlanteID
left join PlantesBiochimieChimiotype pbChType on pbChType.PlanteID =
pTax.PlanteID
left join PlantesBiochimieChimiotypePays pPays on pPays.ChimiotypeID =
pbChType.ID
left join PlantesOpp pOpp on pOpp.PlanteID = pTax.PlanteID
left join PlantesOppPropTherapeutique pOPTH on pOPTH.PlanteOppID = pOpp.ID
left join PlantesOppPropAutres pOPA on pOPA.PlanteOppID = pOpp.ID
left join PlantesOppIngredients pOI on pOI.PlanteOppID = pOpp.ID
WHERE
pTax.nomlatinscientifique like '%'+ @.Value +'%'
or pTaxCat.CategoryID = @.CategoryID
or pTax.descriptiontechniquebotanique like '%'+ @.DescPlantHerb +'%'
or pCul.AcclimatationauCanada like '%'+ @.AcclimatCanada +'%'
--and pCul.environnementdeculture= @.EnvCult
or pTax.raretemenacee like '%'+ @.RareMenacee +'%'
or pPays.PaysID = @.PaysOrig
or pOPTH.ValueID = @.PropTherapeut
or pOPA.ValueID = @.PropAutres
or pOI.ValueID = @.Ingredients
set nocount off
GOWithout sample data, it's difficult to tell; however, I'd check to make
sure that the values you think are duplicates are actually duplicates.
Look for extra spaces or carriage returns at the end of your varchar
values.
HTH,
Stu|||You need to include your DDL.
http://www.aspfaq.com/etiquette.asp?id=5006
Without knowing your keys we cannot determine why you are getting
duplicates.
My guess is that you are not joining on your full keys for at least one
join.
"alto" <altodorov@.hotmail.com> wrote in message
news:%23ew9spiaGHA.4612@.TK2MSFTNGP03.phx.gbl...
> I use the DISTINCT keyword to avoid returning duplicates from the query
> below (multivariable search for plants). However the result does contain
> duplicates and I just can't figure out why. Please help!
> TIA
> CREATE PROCEDURE dbo.usp_MultivarSearch_Plantes
> (
> @.Value varchar(256) ,
> @.CategoryID int ,
> @.DescPlantHerb varchar(64) ,
> @.AcclimatCanada varchar (64) ,
> @.EnvCult varchar (145) ,
> @.RareMenacee varchar (64) ,
> @.PaysOrig int ,
> @.PropTherapeut int ,
> @.PropAutres int ,
> @.Ingredients int
> )
> AS
> set nocount on
> SELECT DISTINCT
> pTax.PlanteID ,
> pTax.nomlatinscientifique ,
> pTaxCat.CategoryID ,
> pTax.descriptiontechniquebotanique ,
> pCul.AcclimatationauCanada ,
> pCul.environnementdeculture ,
> pTax.raretemenacee ,
> pPays.PaysID ,
> pOPTH.ValueID ,
> pOPA.ValueID ,
> pOI.ValueID
> FROM
> PlantesTaxonomie pTax
> left join PlantesTaxonomieCategory pTaxCat on pTax.PlanteID =
> pTaxCat.PlanteID
> left join PlantesCulture pCul on pCul.PlanteID = pTax.PlanteID
> left join PlantesBiochimieChimiotype pbChType on pbChType.PlanteID =
> pTax.PlanteID
> left join PlantesBiochimieChimiotypePays pPays on pPays.ChimiotypeID =
> pbChType.ID
> left join PlantesOpp pOpp on pOpp.PlanteID = pTax.PlanteID
> left join PlantesOppPropTherapeutique pOPTH on pOPTH.PlanteOppID =
pOpp.ID
> left join PlantesOppPropAutres pOPA on pOPA.PlanteOppID = pOpp.ID
> left join PlantesOppIngredients pOI on pOI.PlanteOppID = pOpp.ID
> WHERE
> pTax.nomlatinscientifique like '%'+ @.Value +'%'
> or pTaxCat.CategoryID = @.CategoryID
> or pTax.descriptiontechniquebotanique like '%'+ @.DescPlantHerb +'%'
> or pCul.AcclimatationauCanada like '%'+ @.AcclimatCanada +'%'
> --and pCul.environnementdeculture= @.EnvCult
> or pTax.raretemenacee like '%'+ @.RareMenacee +'%'
> or pPays.PaysID = @.PaysOrig
> or pOPTH.ValueID = @.PropTherapeut
> or pOPA.ValueID = @.PropAutres
> or pOI.ValueID = @.Ingredients
> set nocount off
> GO
>|||>I use the DISTINCT keyword to avoid returning duplicates from the query
>below (multivariable search for plants). However the result does contain
>duplicates and I just can't figure out why. Please help!
Are you sure for two rows that look like duplicates, that EVERY SINGLE
COLUMN is the same? Distinct applies to *every* column, not just the first
one.
A|||Ahhhh... I misunderstood the question. I thought the OP was trying to
remove dupes from the original query so that distinct would not be
necessary.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1146163975.702036.262020@.u72g2000cwu.googlegroups.com...
> Without sample data, it's difficult to tell; however, I'd check to make
> sure that the values you think are duplicates are actually duplicates.
> Look for extra spaces or carriage returns at the end of your varchar
> values.
> HTH,
> Stu
>|||Absolutely, I can see the same PK several times in the resultset in Query
Analyzer.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZa8fyiaGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Are you sure for two rows that look like duplicates, that EVERY SINGLE
> COLUMN is the same? Distinct applies to *every* column, not just the
> first one.
> A
>|||You should expect to get the same primary key multiple times because you are
joining many different tables. Some of these tables have more than one row
with that PK, because they are using it as a foreign key in a one to many
relationship.
As Aaron explained, look at all the other columns in your results, one of
them is different, which is what you are supposed to get.
"alto" <altodorov@.hotmail.com> wrote in message
news:uwRaO4iaGHA.4772@.TK2MSFTNGP05.phx.gbl...
> Absolutely, I can see the same PK several times in the resultset in Query
> Analyzer.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eZa8fyiaGHA.4580@.TK2MSFTNGP03.phx.gbl...
contain
>|||DISTINCT removes duplicates. If it appears that the query is
returning duplicates despite the DISTINCT there are two possibilities.
Most likely is that there is at least one difference that is not
immediately apparent. The other is a bug in SQL Server.
One issue that can't be ignored is that sometimes questions here that
involve DISTINCT (or GROUP BY) and "duplicates" are the result of a
misunderstanding of what DISTINCT does. DISTINCT operates on the
entire row. ANY difference in ANY column will result in seperate
rows.
One approach to narrowing down the problem is to play with the SELECT
list. Run the query - including the DISTINCT - specifying just one
column at a time in the SELECT list. Inspect the results closely,
looking for what appear to be duplicates. Apparent duplicates should
then be inspected with such tools as DATALENGTH and character by
character ASCII(substring()).
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 14:47:40 -0400, "alto" <altodorov@.hotmail.com>
wrote:

>I use the DISTINCT keyword to avoid returning duplicates from the query
>below (multivariable search for plants). However the result does contain
>duplicates and I just can't figure out why. Please help!
>TIA
>CREATE PROCEDURE dbo.usp_MultivarSearch_Plantes
>(
> @.Value varchar(256) ,
> @.CategoryID int ,
> @.DescPlantHerb varchar(64) ,
> @.AcclimatCanada varchar (64) ,
> @.EnvCult varchar (145) ,
> @.RareMenacee varchar (64) ,
> @.PaysOrig int ,
> @.PropTherapeut int ,
> @.PropAutres int ,
> @.Ingredients int
> )
>AS
>set nocount on
>SELECT DISTINCT
>pTax.PlanteID ,
> pTax.nomlatinscientifique ,
> pTaxCat.CategoryID ,
> pTax.descriptiontechniquebotanique ,
> pCul.AcclimatationauCanada ,
> pCul.environnementdeculture ,
> pTax.raretemenacee ,
> pPays.PaysID ,
> pOPTH.ValueID ,
> pOPA.ValueID ,
> pOI.ValueID
>FROM
>PlantesTaxonomie pTax
> left join PlantesTaxonomieCategory pTaxCat on pTax.PlanteID =
>pTaxCat.PlanteID
> left join PlantesCulture pCul on pCul.PlanteID = pTax.PlanteID
> left join PlantesBiochimieChimiotype pbChType on pbChType.PlanteID =
>pTax.PlanteID
> left join PlantesBiochimieChimiotypePays pPays on pPays.ChimiotypeID =
>pbChType.ID
> left join PlantesOpp pOpp on pOpp.PlanteID = pTax.PlanteID
> left join PlantesOppPropTherapeutique pOPTH on pOPTH.PlanteOppID = pOpp.I
D
> left join PlantesOppPropAutres pOPA on pOPA.PlanteOppID = pOpp.ID
> left join PlantesOppIngredients pOI on pOI.PlanteOppID = pOpp.ID
>WHERE
>pTax.nomlatinscientifique like '%'+ @.Value +'%'
> or pTaxCat.CategoryID = @.CategoryID
> or pTax.descriptiontechniquebotanique like '%'+ @.DescPlantHerb +'%'
> or pCul.AcclimatationauCanada like '%'+ @.AcclimatCanada +'%'
> --and pCul.environnementdeculture= @.EnvCult
> or pTax.raretemenacee like '%'+ @.RareMenacee +'%'
> or pPays.PaysID = @.PaysOrig
> or pOPTH.ValueID = @.PropTherapeut
> or pOPA.ValueID = @.PropAutres
> or pOI.ValueID = @.Ingredients
>set nocount off
>GO
>|||> Absolutely, I can see the same PK several times in the resultset in Query
> Analyzer.
Yes, is the PK the only column in your resultset? NO! DISTINCT applies to
ALL COLUMNS, not just the PK.
A|||Here:
DDL
============================
CREATE TABLE [PlantesTaxonomie] (
[PlanteID] [int] NOT NULL ,
[nomlatinusite] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomlatinscientifique] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[famille] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[groupe] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[auteur] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomcommunanglais] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[nomcommercial] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomcommunfranais] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[cledeclassification] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[cledeclassificationimage] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[nomscommunsautres] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[synonymes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[formescitees] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomautresinformation] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[referencedeFloras] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[PFAFRating] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[epithete] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Nombredechromosomes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[identificationherbier] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptiontechniquebotanique] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[taxonssemblables] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[Cultivars] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[referenceautresinformation] [text] COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[nomcycledevie] [varchar] (192) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[codealphabetique] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[Typeplantes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[descriptiondelafleur] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[couleurdelafleur] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fleursramet] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[inflorescence] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[desriptiondesfeuilles] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[cotyledons] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Moisdefloraisondebut] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Moisdefloraisonfin] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Moisdefloraisonrecurrence] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Floraisondusemis] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fruitdescription] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[vigueurdesemences] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[longevitedesemences] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptionracines] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptiontronctiges] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypepHmin] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypepHmax] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypepHmoy] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypeprecipitationmin] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatbiotypeprecipitationmax] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatbiotypeprecipitationmoy] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatbiotypetemperature] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[plantehauteurmaximale] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[hauteurminimale] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[plantelargeur] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Morphologieousilhouette] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[plantephysiologie] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[plantevigueur] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[descriptionautresinformation] [text] COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[ensoleillementremarques] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[sourcegenetique] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[distributiongeographiqueabondance] [varchar] (144) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[pollenbiologieconservation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[semencessporebiologie] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[raretemenacee] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[particulariteautresinformation] [text] COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[datelastmodified] [datetime] NULL ,
[lastmodifiedby] [int] NULL ,
[IsDirty] [bit] NULL CONSTRAINT [DF_PlantesTaxonomie_IsDirty] DEFAULT (1),
[ApprovedBy] [int] NULL ,
CONSTRAINT [PK_PlantesTaxonomie] PRIMARY KEY CLUSTERED
(
[PlanteID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlantesTaxonomie_Plantes] FOREIGN KEY
(
[PlanteID]
) REFERENCES [Plantes] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [PlantesCulture] (
[PlanteID] [int] NOT NULL ,
[Paysregionsdeculture] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Paysfournisseurs] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[origineautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Climatprefere] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[AcclimatationauCanada] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[climatautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[maladiesinsectes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[Exigencesdefertilisation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Preparationduterrain] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[rotation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[tauxdesemis] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[transplantation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fertilite] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[agroautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[besoineneauarrosage] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[arrosagefrequence] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[humiditedusol] [varchar] (155) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[collectionourecoltesauvage] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[culturecomplet] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[cultureduree] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[engrais] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Exigencesdefertilisationculturale] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[essaisdeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[espacement] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[distancedeplantation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[plantationenpot] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[profondeurdeplantation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[cultureinvitro] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[autresnotesdeculture] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[modedeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatendroit] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[pH] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[regiondeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[sol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[qualitedusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[texturedusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[drainagedusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[structuredusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[niveauensoleillementrequis] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[zonederusticite] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fichetechniqueagricole] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Valeurculturale] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[besoinenmatiereorganique] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[environnementdeculture] [varchar] (145) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[controledeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[conditionsautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[cultureenforet] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[regiedeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[regiephytosanitaire] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Invasionparametrescontrole] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Restorationactivites] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Entretien] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Environnementurbainadaptation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Programmedeconservation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Problemesdeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[fichetechniquehorti] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Cultureautresnotes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[culturedesemisennature] [varchar] (145) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Maintenanceentretien] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[autresconditionsculture] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[datelastmodified] [datetime] NULL ,
[lastmodifiedby] [int] NULL ,
[IsDirty] [bit] NULL CONSTRAINT [DF_PlantesCulture_IsDirty] DEFAULT (1),
[ApprovedBy] [int] NULL ,
CONSTRAINT [PK_PlantesCulture] PRIMARY KEY CLUSTERED
(
[PlanteID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlantesCulture_Plantes] FOREIGN KEY
(
[PlanteID]
) REFERENCES [Plantes] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [PlantesBiochimieChimiotype] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PlanteID] [int] NOT NULL ,
[constituants] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[teneurpourcentage] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[partieplante] [int] NULL ,
[partieplanteautre] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptiondrogue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[structuremoleculaire] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[usagespotentiels] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[extrait] [int] NULL ,
[indicederefraction] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[pouvoirrotatoire] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[teneurenproduitspourcentage] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[odeuretsaveur] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[pointebullition] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[pointdefusion] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[pointdecongelation] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[pointdevaporisation] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[solubilite] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[autres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[testsmicroorganismes] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[testsanimauxlaboratoire] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[essaiscliniques] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[essaistoxicologiques] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
CONSTRAINT [PK_PlantesBiochimieChimiotype] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlantesBiochimieChimiotype_Plantes] FOREIGN KEY
(
[PlanteID]
) REFERENCES [Plantes] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [PlantesBiochimieChimiotypePays] (
[ChimiotypeID] [int] NOT NULL ,
[PaysID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesBiochimieChimiotypePays] PRIMARY KEY CLUSTERED
(
[ChimiotypeID],
[PaysID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PlantesOpp] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PlanteID] [int] NOT NULL ,
[OppType] [int] NULL ,
[OppName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[UsageType] [int] NULL ,
[UsageTypeID] [int] NULL ,
[UsageOther] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[autresinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[aspecteconomiques] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[propintellectuelle] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[DateLastModified] [datetime] NULL CONSTRAINT
[DF_PlantesOpp_DateLastModified] DEFAULT (getdate()),
[LastModifiedBy] [int] NULL ,
[IsDirty] [bit] NULL CONSTRAINT [DF_PlantesOpp_IsDirty] DEFAULT (1),
[ApprovedBy] [int] NULL ,
CONSTRAINT [PK_PlantesOpp] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlantesOpp_Plantes] FOREIGN KEY
(
[PlanteID]
) REFERENCES [Plantes] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [PlantesOppPropTherapeutique] (
[PlanteOppID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesOppPropTherapeutique] PRIMARY KEY CLUSTERED
(
[PlanteOppID],
[ValueID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PlantesOppPropAutres] (
[PlanteOppID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesOppPropAutres] PRIMARY KEY CLUSTERED
(
[PlanteOppID],
[ValueID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PlantesOppIngredients] (
[PlanteOppID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesOppIngredients] PRIMARY KEY CLUSTERED
(
[PlanteOppID],
[ValueID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
DATA
==============================
INSERT INTO [PlantesTaxonomie] VALUES(1,'Narthecium americanum','Abama
americana (Ker-Gawl.) Morong','Liliaceae','','','','','','',NU
LL,'','','','
','','','','','','','','','
','','','','','','','','','','8','8','8'
,'','','','','','','','','','','',''
,'','
INSERT INTO [PlantesTaxonomie] VALUES(79144,'Trillium cernuum L.','Trillium
cernuum L.','Liliaceae','','','whip-poor-will
flower','','','',NULL,'','','',' ','','','','','','','','','
','','','','','','','','','','8','8','8'
,'','','','','','','','','','',''
INSERT INTO [PlantesTaxonomie] VALUES(82756,'planta testae','planta
testae','','','','test plant','','plante de test','',NULL,'','','','
','','','','','','Description plante - herbier','','','
','','','','','','','','','','8','8','8'
,'','','','','','','','
INSERT INTO [PlantesCulture] VALUES(1,'','',' ','','','
','','','','','','','',' ','','','','','','','','','','','','',''
,'','
','3','','','','1','1','','','2','','1',
'','','','2','','
','','','','','3','','3','','','','','',
'',' ','Nov 18 2005 11:54:36:19
INSERT INTO [PlantesCulture] VALUES(82756,'','',' ','','bien acclimate','
','','','','','','','',' ','','','','','','','','','','','','',''
,'','
','3','','','','1','1','','','2','','1',
'','','','2','','
','','','','','3','','3','','','','','',
'',' ','Nov
INSERT INTO [PlantesBiochimieChimiotype]
VALUES(1,1,'','',1,'','','','',1,'','','
','','','','','','',' ','Le
mthyleugnol a des proprits antimicrobiennes (Grifin et al., 1998).
Proprits nutritives L'lmicine et le mthyleugnol possdent des p
INSERT INTO [PlantesBiochimieChimiotype]
VALUES(80,79142,'','',9,'ynhryeberyb',''
,'','',3,'','','','','','','','','',
'
',' ',' ',' ',' ')
INSERT INTO [PlantesBiochimieChimiotype]
VALUES(81,82756,'constituants...','10',9,'','description -
drogue...','','Usages potentiels...',3,'','','','','','','','','',' ',' ','
',' ',' ')
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(1,2)
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(1,19)
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(80,4)
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(81,36)
INSERT INTO [PlantesOpp] VALUES(18,79142,2,'fruit',-1,-1,'',' ',' ',' ','
','Nov 18 2005 3:01:55:547PM',48,1,NULL)
INSERT INTO [PlantesOpp] VALUES(19,82756,3,'opp test',-1,-1,'',' ',' ',' ','
','Nov 18 2005 4:28:34:680PM',2,1,NULL)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(1,1)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(1,7)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(5,1)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(13,3)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(13,7)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(13,11)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(14,5)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(14,9)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(14,12)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(19,3)
INSERT INTO [PlantesOppPropAutres] VALUES(1,2)
INSERT INTO [PlantesOppPropAutres] VALUES(5,3)
INSERT INTO [PlantesOppPropAutres] VALUES(13,2)
INSERT INTO [PlantesOppPropAutres] VALUES(19,2)
INSERT INTO [PlantesOppIngredients] VALUES(1,2)
INSERT INTO [PlantesOppIngredients] VALUES(5,3)
INSERT INTO [PlantesOppIngredients] VALUES(13,4)
INSERT INTO [PlantesOppIngredients] VALUES(19,3)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eJm8%23xiaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> You need to include your DDL.
> http://www.aspfaq.com/etiquette.asp?id=5006
> Without knowing your keys we cannot determine why you are getting
> duplicates.
> My guess is that you are not joining on your full keys for at least one
> join.
> "alto" <altodorov@.hotmail.com> wrote in message
> news:%23ew9spiaGHA.4612@.TK2MSFTNGP03.phx.gbl...
> pOpp.ID
>sql

DISTINCT not returning data in sorted order after specific no. of

Hi
I have the problem with the DISTINCT keyword. I used the following statement
to get the distinct values from the table:
SELECT DISTINCT trade_name FROM Customer
This works absolutely perfect getting the distinct values and getting in
sorted order. But my requirement needs the statement to be as :
SELECT DISTINCT RTRIM(trade_name) FROM Customer
With the above statement, it works fine only if the table contains few 100
of records. My table contains around 6000+ records and the above statement
returns DISTINCTINCT values but does not get them in sorted order.
The same statement works fine if the there are around say 500+ records. I
see that the results displayed are distinct and sorted as well.
The field in question (trade_name) of 30 chars (char(30))
Why is this happening.Without ORDER BY the sort order is undefined for any SQL query. Add
ORDER BY:
SELECT DISTINCT trade_name
FROM Customer
ORDER BY trade_name
SELECT DISTINCT RTRIM(trade_name)
FROM Customer
ORDER BY RTRIM(trade_name)
David Portas
SQL Server MVP
--

Displaying seconds value as [mm]:ss

Hello
I am returning a int value from a database which is for an elasped
time in seconds. I want to convert this to mm:ss, however I dont want
it roll over to hours or days.
I trying something along the lines of:
=String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
However this still allows values to go to hours/days, just doesn't
display them
So for example a value returned from the database of 19257 (seconds)
should be displayed as 320:57 ([mm]:ss)
This is achived in excel by
19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
Then change the format on the cell to [mm]:ss
Then 320:57 is displayed.
Any ideas would be great
Thanks
DavidYou could create custom function, something like this:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = seconds - ss * 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
and then call it from textbox expression:
=Code.Seconds2mmss(Sum(Fields!Airtime.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <david.glasgow@.unison.co.nz> wrote in message
news:9f43b964.0408112015.79b5c9b9@.posting.google.com...
> Hello
> I am returning a int value from a database which is for an elasped
> time in seconds. I want to convert this to mm:ss, however I dont want
> it roll over to hours or days.
> I trying something along the lines of:
> =String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
> However this still allows values to go to hours/days, just doesn't
> display them
> So for example a value returned from the database of 19257 (seconds)
> should be displayed as 320:57 ([mm]:ss)
> This is achived in excel by
> 19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
> Then change the format on the cell to [mm]:ss
> Then 320:57 is displayed.
> Any ideas would be great
> Thanks
> David|||Thanks for that, the final code that returned the result was:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Thanks again for your help

Displaying messages to users

Hello,

I need some help coming up with a good concept for returning stored procedure messages to a user. Here is my setup:

Stored Proc:
@.ReturnCode INT - OUT
@.ReturnDetails VARCHAR(150) - OUT

If my stored procedure returns a value of less than 1 for the @.ReturnCode I want to display the error message in @.ReturnDetails, Now I have a DAL that returns back the @.ReturnCode to the codebehind ( via a function ) but how can i display the @.ReturnDetails to the user, I would like to use a javascript alert, I know how to display the message but my question is what is the best design for returning the message to the user?

I was thinking about passing in the caller when i instantiate my DAL, but I don't really like that idea, My other idea was have my function return a string like "-1|Invalid Password" and parsing it but don't like that either. Also is there a way to find out the page caller from my DAL? That would help a lot.

Your ideas are greatly appreciated.

Have you thought about having your DAL return a hashtable instead of just a return value? You could then do something like

string errorMessage;
if (int.Parse(myHashTable["ReturnValue"].ToString()) < 0)
// Return the error message to the users
errorMessage = myHashTable["ReturnMessage"].ToString();

Another alternative would be to pass a reference variable to your DAL, and populate this reference variable with the error message. Continue to use the integer return value, but have your DAL function populate the string with the return message to the user. You can then test the return value, and show the error if the return value is < 0. Try this:

string returnMessage ="";
if (MyDALFunction(ref returnMessage) < 0)
// Show your message
Response.Write(returnMessage);

Hope this helps...