• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1328
  • Last Modified:

SQL Except COLLATE Issue

Please take a look at the code below.  I am having an issue finding exactly where to put the

COLLATE SQL_Latin1_General_CP1_CI_AS

USE TSP
SELECT  a.Customer_Num,
        --a.Customer_Name,
        a.Sales_Doc_Num,
        b.Quantity,
        b.Item_Number,
        b.Item_Description
        -- b.Warehouse_Code,
        --b.Purchasing_Status,
        --[PO_Number]=CASE WHEN Purchasing_Status='none' THEN '' WHEN Purchasing_Status='needs purchase' THEN 'needs purchase' ELSE d.PO_Number END,
        --[Vendor_Name] = CASE WHEN Purchasing_Status='none' THEN '' WHEN Purchasing_Status='needs purchase' THEN '' ELSE d.Vendor_Name END
FROM    dbo.tspvSalesDocument a 
        LEFT JOIN dbo.tspvSalesLineItem b ON a.Sales_Doc_Num = b.Sales_Doc_Num
        LEFT JOIN dbo.tspvSopToPop c ON a.Sales_Doc_Num = c.Sales_Doc_Num AND b.Line_Num=c.SO_Line_Item_Seq
        LEFT JOIN dbo.tspvPurchaseLineItem d ON c.PO_Number = d.PO_Number
                                                AND c.PO_Line_Item_Seq = d.Line_Seq
                                                AND b.Line_Num = c.SO_Line_Item_Seq
WHERE   a.Customer_Num = 'usc01pkg003'
EXCEPT  
--trying to compare the equipment setup in CRM
SELECT  RTRIM(TSP_trfeq_PackageSummaryIdName),
        TSP_SalesOrders,
        [Quantity] = COUNT(TSP_trfeq_model),
        TSP_trfeq_model,
        TSP_trfeq_ProductNameName 
FROM    TSP_MSCRM.dbo.TSP_trfeq
WHERE   TSP_trfeq_PackageSummaryIdName = 'usc01pkg003'
GROUP BY TSP_trfeq_PackageSummaryIdName,
        TSP_SalesOrders,
        TSP_trfeq_model,
        TSP_trfeq_ProductNameName
UNION ALL
SELECT  RTRIM(b.TSP_trfeq_PackageSummaryIdName),
        b.TSP_SalesOrders,
        [Quantity] = COUNT(TSP_ia_PartNumber),
        TSP_ia_PartNumber,
        TSP_ia_ProductIDIdName      
FROM    TSP_MSCRM.dbo.TSP_InstalledAccessories a
        LEFT JOIN TSP_MSCRM.dbo.TSP_trfeq b ON a.TSP_is_EquipmentIDId = b.TSP_trfeqId
WHERE   b.TSP_trfeq_PackageSummaryIdName = 'usc01pkg003'
GROUP BY TSP_trfeq_PackageSummaryIdName,
        b.TSP_SalesOrders,
        TSP_ia_PartNumber,
        TSP_ia_ProductIDIdName

Open in new window


I use the Collate in the following way with the same to databases in the queries above:

SELECT  a.TSP_AccountIdName,
        a.TSP_gppackageaccount,
        b.TSP_trfeq_model,
        b.TSP_trfeq_ProductNameName,
        b.TSP_trfeq_SerialNumber,
        d.Item_Number
FROM    dbo.TSP_packagesummary a
        LEFT JOIN dbo.TSP_trfeq b ON a.TSP_packagesummaryId = b.TSP_trfeq_PackageSummaryId
        RIGHT JOIN tsp.dbo.tspvSalesDocument c ON a.TSP_gppackageaccount = c.Customer_Num COLLATE SQL_Latin1_General_CP1_CI_AS
        RIGHT JOIN tsp.dbo.tspvSalesLineItem d ON c.Sales_Doc_Num = d.Sales_Doc_Num
                                                  AND d.Item_Number COLLATE SQL_Latin1_General_CP1_CI_AS = b.TSP_trfeq_model
WHERE   TSP_gppackageaccount = 'usc01pkg003'  

Open in new window

0
r270ba
Asked:
r270ba
  • 6
  • 3
  • 3
3 Solutions
 
DcpKingCommented:
You're changing one table's collation sequence into something different so it can be compared with another table. So, decide which table will be your "base" table, find out its collation sequence, and then apply that instruction to any of the other tables as they appear in the query, in the join clauses, just as you have done in your second example, where the tables labeled "c" and "d" appear not to have the same setting as "a" and "b".

hth

Mike
0
 
Kevin CrossChief Technology OfficerCommented:
If Item_Number is what you would put the COLLATE on when creating conditions, then you can do this:

USE TSP
SELECT  a.Customer_Num,
        --a.Customer_Name,
        a.Sales_Doc_Num,
        b.Quantity,
        b.Item_Number COLLATE SQL_Latin1_General_CP1_CI_AS AS Item_Number,
        b.Item_Description
        -- b.Warehouse_Code,
        --b.Purchasing_Status,
        --[PO_Number]=CASE WHEN Purchasing_Status='none' THEN '' WHEN Purchasing_Status='needs purchase' THEN 'needs purchase' ELSE d.PO_Number END,
        --[Vendor_Name] = CASE WHEN Purchasing_Status='none' THEN '' WHEN Purchasing_Status='needs purchase' THEN '' ELSE d.Vendor_Name END
FROM    dbo.tspvSalesDocument a 
        LEFT JOIN dbo.tspvSalesLineItem b ON a.Sales_Doc_Num = b.Sales_Doc_Num
        LEFT JOIN dbo.tspvSopToPop c ON a.Sales_Doc_Num = c.Sales_Doc_Num AND b.Line_Num=c.SO_Line_Item_Seq
        LEFT JOIN dbo.tspvPurchaseLineItem d ON c.PO_Number = d.PO_Number
                                                AND c.PO_Line_Item_Seq = d.Line_Seq
                                                AND b.Line_Num = c.SO_Line_Item_Seq
WHERE   a.Customer_Num = 'usc01pkg003'
EXCEPT  
--trying to compare the equipment setup in CRM
SELECT  RTRIM(TSP_trfeq_PackageSummaryIdName),
        TSP_SalesOrders,
        [Quantity] = COUNT(TSP_trfeq_model),
        TSP_trfeq_model COLLATE SQL_Latin1_General_CP1_CI_AS AS Item_Number,
        TSP_trfeq_ProductNameName
FROM    TSP_MSCRM.dbo.TSP_trfeq
WHERE   TSP_trfeq_PackageSummaryIdName = 'usc01pkg003'
GROUP BY TSP_trfeq_PackageSummaryIdName,
        TSP_SalesOrders,
        TSP_trfeq_model,
        TSP_trfeq_ProductNameName
UNION ALL
SELECT  RTRIM(b.TSP_trfeq_PackageSummaryIdName),
        b.TSP_SalesOrders,
        [Quantity] = COUNT(TSP_ia_PartNumber),
        TSP_ia_PartNumber COLLATE SQL_Latin1_General_CP1_CI_AS AS Item_Number,
        TSP_ia_ProductIDIdName
FROM    TSP_MSCRM.dbo.TSP_InstalledAccessories a
        LEFT JOIN TSP_MSCRM.dbo.TSP_trfeq b ON a.TSP_is_EquipmentIDId = b.TSP_trfeqId
WHERE   b.TSP_trfeq_PackageSummaryIdName = 'usc01pkg003'
GROUP BY TSP_trfeq_PackageSummaryIdName,
        b.TSP_SalesOrders,
        TSP_ia_PartNumber,
        TSP_ia_ProductIDIdName

Open in new window


I hope that helps!
0
 
r270baAuthor Commented:
mwvisa1 that does not work.  Still have the resolution issue.

dcpking...do you think you could put that in the code?  I understand collation, I am just having a hard time figuring out my error.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Kevin CrossChief Technology OfficerCommented:
Please define "does not work"; are you getting an error? If so, please paste the exact error in a code snippet.
0
 
r270baAuthor Commented:
@mwvisa1

Sorry!  I thought I had posted the error.  I know that wasn't a lot of help :).

Here it is:


Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the EXCEPT operation.
0
 
Kevin CrossChief Technology OfficerCommented:
I wonder if we are focusing on the wrong field. Do you know the collation for each column involved. I would look at the description field first.
0
 
DcpKingCommented:
Are you getting the error from the feq_model table or the installed_accessories table in the except clause?

I may be wrong (I don't have multiple collations sequence tables here to try out, but, if it's the second part, you could change things:

From this:
SELECT  RTRIM(b.TSP_trfeq_PackageSummaryIdName),
        b.TSP_SalesOrders,
        [Quantity] = COUNT(TSP_ia_PartNumber),
        TSP_ia_PartNumber COLLATE SQL_Latin1_General_CP1_CI_AS AS Item_Number,
        TSP_ia_ProductIDIdName
FROM    TSP_MSCRM.dbo.TSP_InstalledAccessories a
        LEFT JOIN TSP_MSCRM.dbo.TSP_trfeq b ON a.TSP_is_EquipmentIDId = b.TSP_trfeqId

Open in new window


To this

SELECT  RTRIM(b.TSP_trfeq_PackageSummaryIdName),
        b.TSP_SalesOrders,
       a.Quantity,
        a.TSP_ia_PartNumber,
        a.TSP_ia_ProductIDIdName
FROM
(	select	COUNT(TSP_ia_PartNumber) as 'Quantity',
			TSP_ia_PartNumber as 'Item_Number',
			TSP_ia_ProductIDIdName
	from	TSP_MSCRM.dbo.TSP_InstalledAccessories
	COLLATE SQL_Latin1_General_CP1_CI_AS
)  a
LEFT JOIN	TSP_MSCRM.dbo.TSP_trfeq b 
ON 			a.TSP_is_EquipmentIDId = b.TSP_trfeqId

Open in new window


As I say, I haven't an appropriate system to test it on, but I think that should work for you.

hth

Mike
0
 
r270baAuthor Commented:
Please bare with me.  I have been out of the office for the past couple of weeks and am just now returning.  This question is not abandoned.  I hope to pick it back up this week.

Ron
0
 
DcpKingCommented:
Welcome back. Hopefully at least one of us got it right for you!
0
 
r270baAuthor Commented:
Sorry guys but none of these work.  I have actually found a different way to do it.  How should I award points?
0
 
r270baAuthor Commented:
Makes sense.  Unfortunately, the path I chose was no where related to the issue at hand.  I changed to sub queries and no longer needed to collate.  Thank you guys for your help and I will split points for the help.

Thanks again guys.
0
 
r270baAuthor Commented:
Please see my comments on the accepted solution.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now