dimanche 28 juin 2015

rank over union with joined tables sql

I need to make a complex SQL query and add row numbers to it. My query has 3 tables joined, added a custom row with union. No matter how I try, I get syntax errors, please help me finding the solution.

Main query:

select null as EAN, null as CustomsCode, ProductId as SupplierItemCode, '![CDATA['+Product.Name+']' as ItemDescription, '![CDATA['+Product.ShortDescription+']' as ItemNote, null as VATType, 'CU' as PackageType, Quantity as OrderQuantity, 'darab' as UnitOfMeasure, UnitPriceExclTax as OrderedUnitNetPrice from [Order] inner join OrderItem on [Order].Id=OrderItem.OrderId Inner join Product on OrderItem.ProductId=Product.Id where OrderId='150960' UNION select null as EAN, null as CustomsCode, '00001' as SupplierItemCode, '![CDATA[Szállítási díj]' as ItemDescription, '![CDATA[A termék postázási költsége]' as ItemNote, null as VATType, 'CU' as PackageType, '1' as OrderQuantity, 'darab' as UnitOfMeasure, OrderShippingExclTax as OrderedUnitNetPrice from [Order] Where [Order].Id='150960'

I need to add rank() to this table without getting same numbers as row number My version was:

select Row_Number() OVER (Order by ProductID) as LineNumber, null as EAN, null as CustomsCode, ProductId as SupplierItemCode, '![CDATA['+Product.Name+']' as ItemDescription, '![CDATA['+Product.ShortDescription+']' as ItemNote, null as VATType, 'CU' as PackageType, Quantity as OrderQuantity, 'darab' as UnitOfMeasure, UnitPriceExclTax as OrderedUnitNetPrice from [Order] inner join OrderItem on [Order].Id=OrderItem.OrderId Inner join Product on OrderItem.ProductId=Product.Id where OrderId='150960' UNION select Row_Number() OVER (Order by Id) as LineNumber, null as EAN, null as CustomsCode, '00001' as SupplierItemCode, '![CDATA[Szállítási díj]' as ItemDescription, '![CDATA[A termék postázási költsége]' as ItemNote, null as VATType, 'CU' as PackageType, '1' as OrderQuantity, 'darab' as UnitOfMeasure, OrderShippingExclTax as OrderedUnitNetPrice from [Order] Where [Order].Id='150960'

Which resulted row numbers: 1,1,2, I got same result with Rank()

Can anyone help?

Aucun commentaire:

Enregistrer un commentaire