Need help here. I have 2 questions based on this query.
SELECT cdd.FieldID,cdd.PlanTypeID,pt.IsFinancial
FROM ClientDataDictionary cdd
INNER JOIN #tblPlanTypes pt ON cdd.PlanTypeID = pt.PlanTypeID
INNER JOIN ClientDataDictionaryFieldType cddftype
ON cdd.FieldId = cddftype.FieldID AND cdd.PlanTypeId = cddftype.PlanTypeID
WHERE cdd.ClientId = @ClientID AND cdd.IsHidden = 0
AND cddftype.FieldTypeID = 4
AND cddftype.FieldID = cddftype.ParentFieldIDSectionTitle
AND (SELECT COUNT(cddftype2.FieldID)
FROM ClientDataDictionaryFieldType cddftype2
Where cddftype2.ParentFieldIDSectionTitle = cdd.FieldID
AND cddftype2.PlanTypeID = cdd.PlanTypeID
AND EXISTS(Select tbl.FieldID From #tblSelectedFields tbl
Where tbl.FieldID = cddftype2.FieldID AND tbl.PlantypeID =cdd.PlanTypeID COLLATE SQL_Latin1_General_CP1_CI_AS)
) != 0 COLLATE SQL_Latin1_General_CP1_CI_AS
- Where do I place my
COLLATE
keyword in this query? I know that temp tables have different collate "type". - I tried using
COLLATE DATABASE_DEFAULT
however, the error still occurs. Will this resolve the issue?
Please help me here. Thanks!
[UPDATE 1]
ClientID
is set to UNIQUEIDENTIFIER
data type.
[UPDATE 2]
here are the table definitions
#tblPlanTypes
PlanTypeId nvarchar(50),
PlanType nvarchar(max),
Prefix NVARCHAR(10),
IsFinancial BIT,
TotalCount BIGINT,
PlanCount BIGINT
ClientDataDictonary
[ClientDataDictionaryId] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF_ClientDataDictionary_ClientDataDictionaryId] DEFAULT (newid()),
[ClientId] [uniqueidentifier] NOT NULL,
[PlanTypeId] [uniqueidentifier] NULL,
[FieldId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FieldText] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FieldType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DefaultValue] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HelpText] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsHidden] [bit] NULL CONSTRAINT [DF_ClientDataDictionary_IsHidden] DEFAULT ((0)),
[IsSummable] [bit] NULL,
[IsRequired] [bit] NULL,
[IsContractRenewal] [bit] NULL,
[HasAlert] [bit] NULL CONSTRAINT [DF_ClientDataDictionary_HasAlert] DEFAULT ((0)),
[AlertMessage] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CentralLanguageId] [uniqueidentifier] NOT NULL,
[LookupTypeName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RowVersion] [timestamp] NULL,
[LastUpdated] [datetime] NULL CONSTRAINT [DF_ClientDataDictionary_LastUpdated] DEFAULT (getutcdate()),
[Sequence] [int] NULL
ClientDataDictionaryFieldType
[ClientDataDictionaryFieldTypeID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClientDataDictionaryFieldType_ClientDataDictionaryFieldType] DEFAULT (newid()),
[PlanTypeID] [uniqueidentifier] NULL,
[FieldID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FieldTypeID] [int] NULL,
[ParentFieldIDSectionTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
#tblSelectedFields
FieldID nvarchar(255),
PlanTypeID UniqueIdentifier,
FieldText nvarchar(MAX),
sequence int,
FieldType nvarchar(100),
IsFinancial bit
[UPDATE 3]
Tried the suggestion by @Mack, but a new error emerged. Expression type uniqueidentifier is invalid for COLLATE clause.
This is caused by the @ClientID having a data type of uniqueidentifier. Any suggestions?
Best Answer
First of all - if you include the descriptions of the tables in your example people here will be able to help you a lot quicker.
Second to debug this query yourself break it into logical parts(separate any subqueries, remove tables one at a time), then remove the where clauses one by from each logical part until your query works, this will help you identify the cause of the problem, remember that the join clauses could also be the problem.
Finally, the code below will allow you to determine the collation type for each column you are querying, it's from this article by Pinal Dave
Thanks for the table definitions... At first glance, your collate issue lies with this field
used in this join:
INNER JOIN #tblPlanTypes pt ON cdd.PlanTypeID = pt.PlanTypeID
if you apply the COLLATE to the join you should resolve your problem.