[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Collation problem with UNION query

Hi Experts

I have two local querys that Im doing a union query on  I get a message about a collation problem, which is odd becashse they are the same collation in the database, any ideas - heres the code.


SELECT     RecordTypeFlg, ReportingFirmIdentification, ReportingFirmCodeType, TradingDate, TradingTime, BUYSELL, TradingCapacity, InstrumentIdentification, 
                      InstrumentIdentifierType, InstrumentDescription, UnderlyingInstrumentIdentification, InstrumentType, MaturityExerciseDeliveryDate, DerivativeType, 
                      PutCallIdentifier, StrikePrice, PriceMultiplier, UnitPrice, PriceNotation, Quantity, CounterPartyCode, CounterPartyCodeType, VenueIdentification, 
                      TransRef, Report_Status, Client_CounterPaty_2_Code, Client_Code_Type, Venue_Type_Code, Unit_Price_Type_Code, Reserved, End_Of_Record
FROM         dbo.qryExport_Aii
UNION
SELECT     RecordTypeFlg, ReportingFirmIdentification, ReportingFirmCodeType, TradingDate, TradingTime, BUYSELL, TradingCapacity, InstrumentIdentification, 
                      InstrumentIdentifierType, InstrumentDescription, UnderlyingInstrumentIdentification, InstrumentType, MaturityExerciseDeliveryDate, DerivativeType, 
                      PutCallIdentifier, StrikePrice, PriceMultiplier, UnitPrice, PriceNotation, Quantity, CounterPartyCode, CounterPartyCodeType, VenueIdentification, 
                      TransRef, Report_Status, Client_CounterPaty_2_Code, Client_Code_Type, Venue_Type_Code, Unit_Price_Type_Code, Reserved, End_Of_Record
FROM         dbo.qryExport_ISIN

Open in new window

SQL.bmp
0
MrDavidThorn
Asked:
MrDavidThorn
  • 4
  • 4
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
Can you double check once more - in the tables - 1) qryExport_Aii & 2) qryExport_ISIN - Properties - whether they are having the same collation.

Table - Properties
 Collation
Raj
0
 
Rajkumar GsSoftware EngineerCommented:
0
 
Rajkumar GsSoftware EngineerCommented:
You can check collasion of table and its column by this easiest way - Please see screen-shot
Table Properties
Please let me know, if you need any more help
Raj
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MrDavidThornAuthor Commented:
when I go to the extended properties off the view the collation is both latin1_general_CI_AS for both
0
 
MrDavidThornAuthor Commented:
ok it effects just one field from the union query, one of the views in the union query has an output the other one just has '' as the output.
0
 
MrDavidThornAuthor Commented:
seem to have found the problem, one view is outputting '' as a coloumn the other view is outputting the field, the field that has no value is using the default server collation, while the field output is using the view collation, how do I force the coloumn to use the table default collation
0
 
MrDavidThornAuthor Commented:
If I amend my view to have COLLATE Latin1_Gerneral_C1_AS at the end it throws a syntax error, how can I force my view to have a collation of Latin1_Gerneral_C1_AS
0
 
Rajkumar GsSoftware EngineerCommented:
You can specify the collation after the column name, which is having this issue.
For eg:-
select SomeColumnName COLLATE SQL_Latin1_General_CP1_CI_AS  
from YourTableName

Open in new window


Raj
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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