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 mvp

hi all,
i use a query that returns a set of projects and customers for my
report. i am using a multi value parameter to filter the projects in
the data. because the grouping is done in the report itself, when i
try to set the mvp from my query each project apears multiple times on
the dropdown list. is there a way to get the distinct projects from
the query? - to get each project to apear once?.
i dont want to create another dataset to get the distinct projects
becuase the query is quite heavy.
or maybe is there a way to create another dataset to query from my
main dataset?
thanks in advance
offaYou can't do a query on an existing dataset. For my parameter lists I have
dedicated datasets. Perhaps if all the query is doing is getting is the
distinct projects it won't be a compute intensive query. Also, you might
want to see what sort of index you have on the table.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<offa23@.hotmail.com> wrote in message
news:1188265077.769587.273140@.k79g2000hse.googlegroups.com...
> hi all,
> i use a query that returns a set of projects and customers for my
> report. i am using a multi value parameter to filter the projects in
> the data. because the grouping is done in the report itself, when i
> try to set the mvp from my query each project apears multiple times on
> the dropdown list. is there a way to get the distinct projects from
> the query? - to get each project to apear once?.
> i dont want to create another dataset to get the distinct projects
> becuase the query is quite heavy.
> or maybe is there a way to create another dataset to query from my
> main dataset?
> thanks in advance
> offa
>

Distinct issue

Hi

I wanna write a proc that returns Distinct CustomerID's in a table and returns result in output parameter

When i try this, i get error - incorrect syntax near distinct. Any ideas??

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
select
@.CustCount = distinct(CustomerID)
from
Orders
Where
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

hi,

what you want to return, count of distinct ids or distinct ids itself ?

regards,

satish

|||

hehe.. er ofcourse. I wanted to count the distinct ids, but i didnt count

i worked it out

cheers brother

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
SELECT
@.CustCount =Count(DISTINCT(CustomerID))
FROM
Orders
WHERE
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

|||

good you got it working :),

cheers ,

satish

DISTINCT function does not remove the duplicate member

Hi All,

The following query returns 4 members ([Gross Sales], [Trade Sales], [Intercompany Sales], [Trade Sales]) while it should return only 3. DISTINCT function does not remove the duplicated member [Trade Sales].

SELECT

{[Measures].[Amount]}

ON AXIS(0),

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after))

ON AXIS(1)

FROM [ADVENTURE WORKS]

Any inputs will be appreciated.

David.

DISTINCT must follow SELECT:

Code Snippet

SELECT

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after)) ON AXIS(1),

{[Measures].[Amount]}

ON AXIS(0),

FROM [ADVENTURE WORKS]

Adamus

|||

Seems to be a bug, since if you statically list the members, Distinct() seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Distinct({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales],

[Account].[Accounts].[Intercompany Sales],

[Account].[Accounts].[Trade Sales]}) ON AXIS(1)

FROM [ADVENTURE WORKS]

Using Generate() instead of Distinct() also seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Generate(DESCENDANTS({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after),

{[Account].[Accounts].CurrentMember}) ON AXIS(1)

FROM [ADVENTURE WORKS]

|||

Thx, Deepak.

The question remains: is it just a bug or there is some hidden meaning to this behavior?

Curiously, if instead of DISTINCT you apply other functions that should remove duplicates,

the duplicates still remain, for example, UNION with an empty set:

UNION(DESCENDANTS({[Account].[Accounts].[Gross Sales], [Account].[Accounts].[Trade Sales]}, 0, self_and_after), {})

displaying records in two tables

HI,
Need some help in displaying the records in two table( from a single
dataset).
To be precise i have a dataset which returns say 10 rows, i want to
display the odd numbered rows( i mean the alternate rows) in the first
table and then the even rows in the second table. I'm using RS 2K.
This is a do or die situation with RS reports, else i might have to all
my reports using crystal reports. Appreciate if some one can quickly
fill my hope.
Thanks in advance,
--VenkatSimple enough.
First create your initial table. Set the Visibility/Hidden property of
the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
will give you your odd numbered rows.
Copy that table and paste the copy into the report body. Edit the
Visibility/Hidden property of the copy's detail row to
=IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
your even numbered rows.
Make sure you set the property for the row and not individual cells or
you'll end up with weird white spaces showing up.
It's probably also possible to set up filters for the dataset that
would accomplish this but you would have to find an alternative to the
RowNumber() function as it can't be used in filters. If your datset
includes an incrementally numbered field that could work.
Best regards
toolman
venkat.oar@.gmail.com wrote:
> HI,
> Need some help in displaying the records in two table( from a single
> dataset).
> To be precise i have a dataset which returns say 10 rows, i want to
> display the odd numbered rows( i mean the alternate rows) in the first
> table and then the even rows in the second table. I'm using RS 2K.
> This is a do or die situation with RS reports, else i might have to all
> my reports using crystal reports. Appreciate if some one can quickly
> fill my hope.
> Thanks in advance,
> --Venkat|||Please note my editting below:
toolman wrote:
> Simple enough.
> First create your initial table. Set the Visibility/Hidden property of
> the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> will give you your odd numbered rows.
> Copy that table and paste the copy into the report body. Edit the
> Visibility/Hidden property of the copy's detail row to
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> your even numbered rows.
EDITTING: The above should be reversed.
=IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
mod 2 = 1, True,False) will HIDE the odd numbers.
Sorry for the mix-up.
> Make sure you set the property for the row and not individual cells or
> you'll end up with weird white spaces showing up.
> It's probably also possible to set up filters for the dataset that
> would accomplish this but you would have to find an alternative to the
> RowNumber() function as it can't be used in filters. If your datset
> includes an incrementally numbered field that could work.
> Best regards
> toolman
> venkat.oar@.gmail.com wrote:
> > HI,
> >
> > Need some help in displaying the records in two table( from a single
> > dataset).
> >
> > To be precise i have a dataset which returns say 10 rows, i want to
> > display the odd numbered rows( i mean the alternate rows) in the first
> > table and then the even rows in the second table. I'm using RS 2K.
> >
> > This is a do or die situation with RS reports, else i might have to all
> > my reports using crystal reports. Appreciate if some one can quickly
> > fill my hope.
> >
> > Thanks in advance,
> > --Venkat|||Thank you for your response, i will implement it to see if every thing
works fine, mean while can you tell me how to restrict the # of columns
displayed per table
Appreciate your help.
--Venkat
toolman wrote:
> Please note my editting below:
> toolman wrote:
> > Simple enough.
> > First create your initial table. Set the Visibility/Hidden property of
> > the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> > will give you your odd numbered rows.
> > Copy that table and paste the copy into the report body. Edit the
> > Visibility/Hidden property of the copy's detail row to
> > =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> > your even numbered rows.
> EDITTING: The above should be reversed.
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
> numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
> mod 2 = 1, True,False) will HIDE the odd numbers.
> Sorry for the mix-up.
> > Make sure you set the property for the row and not individual cells or
> > you'll end up with weird white spaces showing up.
> > It's probably also possible to set up filters for the dataset that
> > would accomplish this but you would have to find an alternative to the
> > RowNumber() function as it can't be used in filters. If your datset
> > includes an incrementally numbered field that could work.
> > Best regards
> > toolman
> >
> > venkat.oar@.gmail.com wrote:
> > > HI,
> > >
> > > Need some help in displaying the records in two table( from a single
> > > dataset).
> > >
> > > To be precise i have a dataset which returns say 10 rows, i want to
> > > display the odd numbered rows( i mean the alternate rows) in the first
> > > table and then the even rows in the second table. I'm using RS 2K.
> > >
> > > This is a do or die situation with RS reports, else i might have to all
> > > my reports using crystal reports. Appreciate if some one can quickly
> > > fill my hope.
> > >
> > > Thanks in advance,
> > > --Venkat

displaying found duplicate - both records

I would like to look at both records returned as duplicates. I am using the
following script to find the duplicates but can it only returns one record
not both. I would like to see the entire record so that I can make a
determination on more than just the criteria that I selected as the basis of
the record being a duplicate.
SELECT Patient.Last_Name,
Patient.First_Name,
Patient.Birthdate
COUNT(*) AS Dupes
FROM Patient
WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
Payor_Key = 36)
GROUP BY Last_Name, First_Name, Birthdate
HAVING (COUNT(*) > 1)
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1In order to do that, you may need to put your current query as "Result
Table", something like:
SELECT P1.Last_Name,
P1.First_Name,
P1.Birthdate,
Dupes
FROM Patient P1,
(SELECT P2.Last_Name P2LastName,
P2.First_Name P2FirstName,
P2.Birthdate P2Birthday
COUNT(*) AS Dupes
FROM Patient P2
WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FROM
Patient_Elg WHERE
P2.Payor_Key = 36)
GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
HAVING (COUNT(*) > 1)) DupCount
where P1.Last_Name = P2LastName
and P1.First_Name = P2FirstName
and P1.Birthdate = P2Birthday
This code has not been tested yet but just an idear.
Perayu
"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:5685c45d8a0c6@.uwe...
>I would like to look at both records returned as duplicates. I am using the
> following script to find the duplicates but can it only returns one record
> not both. I would like to see the entire record so that I can make a
> determination on more than just the criteria that I selected as the basis
> of
> the record being a duplicate.
> SELECT Patient.Last_Name,
> Patient.First_Name,
> Patient.Birthdate
> COUNT(*) AS Dupes
> FROM Patient
> WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
> Payor_Key = 36)
> GROUP BY Last_Name, First_Name, Birthdate
> HAVING (COUNT(*) > 1)
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200510/1|||This did work for me after I made some tweaks. Thanks allot.
Perayu wrote:
>In order to do that, you may need to put your current query as "Result
>Table", something like:
>SELECT P1.Last_Name,
> P1.First_Name,
> P1.Birthdate,
> Dupes
>FROM Patient P1,
> (SELECT P2.Last_Name P2LastName,
> P2.First_Name P2FirstName,
> P2.Birthdate P2Birthday
> COUNT(*) AS Dupes
> FROM Patient P2
> WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FRO
M
>Patient_Elg WHERE
> P2.Payor_Key = 36)
> GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
> HAVING (COUNT(*) > 1)) DupCount
>where P1.Last_Name = P2LastName
> and P1.First_Name = P2FirstName
> and P1.Birthdate = P2Birthday
>This code has not been tested yet but just an idear.
>Perayu
>
>[quoted text clipped - 13 lines]
Message posted via http://www.webservertalk.com

Displaying Dynamically created columns

Hello,
I have a stored procedure that accepts input parameters and depending
on the input, returns multiple columns. For example depending on the month
range passed in, the stored procedure creates and populates temporary table
with columns for each month and returns the values in the temporary table.
How do I display these dynamically created columns using Reporting Services
(the number of coulmns returned by the stored proc varies between different
runs). Any help is appreciated.Sunil,
You would need to use the matrix control and then edit the
matrix_columngroup to use the data returned from your stored proc.
Hope this helps.
Bill Youngman
Anexinet, Inc.
"Sunil Oliver" <SunilOliver@.discussions.microsoft.com> wrote in message
news:D11DBA4E-C99F-4E37-8C0D-7F9D76192001@.microsoft.com...
> Hello,
> I have a stored procedure that accepts input parameters and
depending
> on the input, returns multiple columns. For example depending on the month
> range passed in, the stored procedure creates and populates temporary
table
> with columns for each month and returns the values in the temporary table.
> How do I display these dynamically created columns using Reporting
Services
> (the number of coulmns returned by the stored proc varies between
different
> runs). Any help is appreciated.

Display stored procedure results in a view

If a stored procedure returns a table, is it possible to return that as a
view. Something like this (although I know that this SQL doesn't work)...
CREATE VIEW vw_Equipment
AS
EXEC proc_Equipment_List
Thanks,
CraigYou could use OPENROWSET or OPENQUERY, like:
CREATE VIEW vw_Equipment
AS
SELECT ... FROM OPENQUERY(..., 'EXEC proc_Equipment_List')
I wouldn't use this for production code, though, I find it to be a bit of a
hack.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:061063E2-ADE5-420E-8D23-C48D7F3B29F0@.microsoft.com...
> If a stored procedure returns a table, is it possible to return that as a
> view. Something like this (although I know that this SQL doesn't work)...
> CREATE VIEW vw_Equipment
> AS
> EXEC proc_Equipment_List
>
> Thanks,
> Craig|||Hi Craig,
You just need to remember to set the dataaccess bit or the server to on
using the sp_serveroption. By default the server instance has data access
set off
Greg O
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OgZC9EbmFHA.3816@.tk2msftngp13.phx.gbl...
> You could use OPENROWSET or OPENQUERY, like:
>
> CREATE VIEW vw_Equipment
> AS
> SELECT ... FROM OPENQUERY(..., 'EXEC proc_Equipment_List')
> I wouldn't use this for production code, though, I find it to be a bit of
> a hack.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:061063E2-ADE5-420E-8D23-C48D7F3B29F0@.microsoft.com...
>|||Here is the example i just posted in another thread.
exec sp_serveroption 'srv','data access','true'
go
if object_id('_v','v') is not null
drop view _v
go
create view _v
as
select *
from openquery(srv,'set fmtonly off; exec sp_lock')x
go
select * from _v
go
-oj
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:061063E2-ADE5-420E-8D23-C48D7F3B29F0@.microsoft.com...
> If a stored procedure returns a table, is it possible to return that as a
> view. Something like this (although I know that this SQL doesn't work)...
> CREATE VIEW vw_Equipment
> AS
> EXEC proc_Equipment_List
>
> Thanks,
> Craig

Display output parameters from stored procedure

Hi,

I have a report that uses a stored procedure. The stored procedure has 3 parameters 1 input and two output and also returns a resultset.

I have managed to hide the user prompt by changing the prompt for the two output parameters to an empty string.

The report runs fine and the user is not prompted for these two values, however the report does not show the values that are returned by these output parameters.

I could add extra fields to the resultset and not bother with the ouput parameters but this seems extremely inefficient as the resultset could contain several thousand rows and the fields would be the same for every single row.

Is it possible to display output paramater values?

Thanks In Advance

Chris

Chris,

have you thought of redesigning your stored procedure?

split it into two sp's, one containg the resultset, the second containg your output parameters, which should be redefined as "normal" parameters and brought back as a resultset via select @.parm1, @.parm2.

cheers,
Markus

Display ONLY Month, year from SQL2k

Tried this:

SELECT CONVERT(varchar,fieldMonthYear,107) 'Month in Question' FROM ....

That returns: Apr 01, 2006
What I need is this: April 2006 or even Apr 2006. But no date for the day.

Is there a way I can trim the center 4 characters of this now converted varchar? This is in a datalist, btw. Thanks!

bs.

SELECT Left(CONVERT(varchar,getdate(),107), 4) + RIGHT(CONVERT(varchar,getdate(),107), 4) AS 'Month in Question'|||

Thank you.

while you gave an acceptable answer, I found another way - I'll still mark your answer as right because it answered my question originally.

This is what I used to get it to work:

SELECT CONVERT(VARCHAR(10), DATENAME(MM,fieldMonthYear)) + ', ' + CONVERT(VARCHAR(4), Year(fieldMonthYear)) AS 'Month in Question' FROM tblTableName

God bless...
iSheahan

|||

There is a better way to format datetime from client side.

<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource3">
<ItemTemplate>

LastActivityDate:
<asp:Label ID="DateLabel" runat="server" Text=' <%# DataBinder.Eval(Container.DataItem, "fieldMonthYear","{0:MMM yyyy}") %>'> </asp:Label>

</ItemTemplate>

</asp:DataList>

Or simply: <asp:Label ID="Label1" runat="server" Text=' <%# Eval("fieldMonthYear","{0:MMM yyyy}") %>'> </asp:Label>

Enjoy.