Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #868

Re: Collation error

Show all headers | View raw

Create view FS_Customer as

select  
T1.CMCUSTID as CustomerID,  
T1.CMCUNAME as CustomerName,  
T1.CMCUAD1 as CustomerAddress1,  
T1.CMCUAD2 as CustomerAddress2,  
T1.CMCUCITY as CustomerCity,  
T1.CMCUSTAT as CustomerState,  
T1.CMCUZIP as CustomerZip,  
T1.CMCUCNTR as CustomerCountry,  
T1.CMCONTCT as CustomerContact,  
T1.CMPHONE as CustomerContactPhone,  
T1.CMFAX as CustomerContactFax,  
T1.CMBILLTO as BillToCustomerID,  
T1.CMB2NAME as BillToName,  
T1.CMB2AD1 as BillToAddress1,  
T1.CMB2AD2 as BillToAddress2,  
T1.CMB2CITY as BillToCity,  
T1.CMB2STAT as BillToState,  
T1.CMB2ZIP as BillToZip,  
T1.CMB2CNTR as BillToCountry,  
T1.CMACTCNT as AccountingContact,  
T1.CMACPOS as AccountingContactPosition,  
T1.CMACFONE as AccountingContactPhone,  
T1.CMACFAX as AccountingContactFax,  
T1.CMCSR as CSR,  
T1.CMREGION as SalesRegion,  
T1.CMCOMCD as CommissionCode,  
T1.CMCLASS as CustomerClass1,  
T1.CMCLASS$2 as CustomerClass2,  
T1.CMCLASS$3 as CustomerClass3,  
T1.CMCLASS$4 as CustomerClass4,  
T1.CMCLASS$5 as CustomerClass5,  
T1.CMCLASS$6 as CustomerClass6,  
T1.CMCLASL as CustomerClass7,  
T1.CMCLASL$2 as CustomerClass8,  
T1.CMTOPLVL as IsTopLevel,  
T1.CMCLVL as CustomerLevel,  
T1.CMTRCLAS as TradeClassName,  
T1.CMCNTCUR as CustomerControllingCode,  
T1.VCCMCURCOD as CustomerCurrencyCode,  
T1.CMOKCURR as CurrencyConversionProcessed,  
T1.CMCRREP as CreditRepresentative,  
T1.CMCREDIT as CreditLimitControllingAmount,  
T1.CMCRAUTH as CreditLimitMaintainedUserID,  
T1.CMCRDATE as CreditLimitMaintainedDate,  
T1.CMAR as ARCustomerCode,  
T1.CMOEHOLD as OrderEntryCreditHoldCode,  
T1.CMSPHOLD as ShipmentCreditHoldCode,  
T1.CMLCROVA as MaximumPastDueControllingAmount,  
T1.CMCROVDY as MaximumPastDueDays,  
T1.CMCROVP as MaximumPastDuePercent,  
T1.CMCBOORD as IncludeOpenOrdersInCreditConsumed,  
T1.CMCBRORD as IncludeReleasedOrdersInCreditConsumed,  
T1.CMCBCM as IncludeOpenCreditMemosInCreditConsumed,  
T1.CMCRUPPR as CheckCreditAtUpperLevels,  
T1.CMACASGN as IsCreditHoldAssignmentAutomatic,  
T1.CMCRHOLD as IsCustomerOnCreditHold,  
T1.CMACREMV as IsCreditHoldRemovalAutomatic,  
T1.CMDBID as DunBradstreetNumber,  
T1.CMDBSIC as DunBradstreetClass,  
T1.CMDBRATE as DunBradstreetRating,  
T1.CMDBDATE as DunBradstreetMaintainedDate,  
T1.CMBANK1 as BankReference1,  
T1.CMBANK2 as BankReference2,  
T1.CMPRTFMT as PrintFormat,  
T1.CMPRTLNG as PrintLanguage,  
T1.CMPRNT as ItemPrintCode,  
T1.CMARTYPE as AROpenItemBalanceForwardCode,  
T1.CMSTMTCD as PrintStatement,  
T1.CMSTMTCY as StatementCycle,  
T1.CMINTRST as ServiceChargeInterestRate,  
T1.CMPAYHOW as TermsCode,  
convert(float,T1.CMTRMPC1) / 10 as TermsDiscountPercent1,  
T1.CMTRMDA1 as TermsDiscountDays1,  
convert(float,T1.CMTRMPC2) / 10 as TermsDiscountPercent2,  
T1.CMTRMDA2 as TermsDiscountDays2,  
convert(float,T1.CMTRMPC3) / 10 as TermsDiscountPercent3,  
T1.CMTRMDA3 as TermsDiscountDays3,  
convert(float,T1.CMTRMPC4) / 10 as TermsDiscountPercent4,  
T1.CMTRMDA4 as TermsDiscountDays4,  
T1.CMTRMNET as TermsDiscountNetDays,  
T1.CMPAYWEN as TermsMonthlyPaymentDay,  
T1.CMCUTOFF as TermsMonthlyCutoffDay,  
T1.CMDELAY as TermsMonthlyDelay,  
T1.CMSACNO as SalesAccount,  
T1.CMCGACNO as CostOfGoodsSoldAccount,  
T1.CMARACNO as AccountsReceivableAccount,  
T1.CMFTACNO as FreightAccount,  
convert(float,T1.CMTRADE) / 10 as DefaultSurchargeDiscountPercent,  T1.CMPRIOR as CustomerPriority,  
T1.CMENDUSR as EndUserCustomerID,  
T1.CMFOB as FOBPoint,  
T1.CMFRTCD as FreightChargeCode,  
T1.CMCARR as CarrierName,  
T1.CMRTCODE as DistributionZone,  
T1.CMSHCODE as DistributionRegion,  
T1.CMMULSHP as AllowMultipleOrderShipments,  
T1.CMMITSHP as AllowMultipleLineItemShipments,  
T1.CMPALLOC as AllowPartialAllocations,  
T1.CMPSHPIT as AllowPartialShipments,  
T1.CMPARTL as CustomerAcceptsPartialShipments,  
T1.CMCLEXP as IsCOAutoclosedAfterExpiration,  
T1.CMCLCOM as AutocloseAfterShipment,  
T1.CMCLEXPD as DaysAfterExpirationToAutocloseCO,  
T1.CMCLQTY as AutocloseQuantityVariance,  
T1.CMTRLT as TransportationLeadTimeDays,  
T1.CMSHIPLT as ShipmentLeadTimeDays,  
T1.CMONTIME as DaysEarlyConsideredOnTimeShipment,  
T1.CMONTIML as DaysLateConsideredOnTimeShipment,  
convert(float,T1.CMRCPTVU) / 10 as UnderShipmentQuantityTolerancePercent,  
convert(float,T1.CMRCPTVO) / 10 as OverShipmentQuantityTolerancePercent,  
T1.CMOTCOMP as ForceOnTimeShipment,  
T1.CMQTCOMP as ForceOnTimeShipmentVariance,  
T1.CMSTKRM as PreferredShipmentStockroom,  
T1.CMBIN as PreferredShipmentBin,  
T1.CMEXMPT as FederalPrimaryTaxExemptReason,  
T1.CMTXCERT as FederalPrimaryTaxExemptCertificateNumber,  
T1.CMEXS as StateSecondaryTaxExemptReason,  
T1.CMSTXCER as StateSecondaryTaxExemptCertificateNumber,  
T1.CMEXC as CountyTaxExemptReason,  
T1.CMCTXCER as CountyTaxExemptCertificateNumber,  
T1.CMEXM as MunicipalTaxExemptReason,  
T1.CMMTXCER as MunicipalTaxExemptCertificateNumber,  
T1.CMFTAXCD as FederalPrimaryTaxCode,  
T1.CMSTAXCD as StateSecondaryTaxCode,  
T1.CMCTAXCD as CountyTaxCode,  
T1.CMMTAXCD as MunicipalTaxCode,  
T1.CMVATCUS as VATCustomerCode,  
T1.CMEDI as IsEDITradingPartner,  
T1.CMLSTMNT as LastMaintainedDate,  
T1.CMEMAIL as CustomerContactEmail,  
T1.CMCADATE as CustomerStartDate,  
T1.CMSTATUS as CustomerStatus,  
T1.CMBKCUST as TradingPartnerBank,  
T1._CMSTRUC_OwnRow as ParentCustomerKey,  
T1._CSTCLASS_OwnRow as TradeClassKey,  
T1._Ts as CustomerRowVersion,  
T1._Row as CustomerKey  
from Mfg_CUSTMAST T1  

AS you can see, it is a pretty straight forward view.

Thanks again.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Collation error Lackey <lackeysc@gmail.com> - 2011-12-13 09:21 -0800
  Re: Collation error Erland Sommarskog <esquel@sommarskog.se> - 2011-12-13 23:32 +0100
    Re: Collation error Lackey <lackeysc@gmail.com> - 2011-12-13 14:48 -0800
      Re: Collation error Erland Sommarskog <esquel@sommarskog.se> - 2011-12-14 08:22 +0000
        Re: Collation error Lackey <lackeysc@gmail.com> - 2011-12-14 06:23 -0800
          Re: Collation error Erland Sommarskog <esquel@sommarskog.se> - 2011-12-14 23:53 +0100
    Re: Collation error Lackey <lackeysc@gmail.com> - 2011-12-13 14:53 -0800

csiph-web