In sql,How to sort row number using cast in nvarchar

Question

0.00/5 (No votes)

See more:

SQL

declare @Company_Code int=5
declare @i int =1
--while @i<5991
--begin
--insert into test values('test')
SELECT distinct @Company_Code,
       --CustomerID,
       'bc'+ cast(row_number()over(order by customerID) as varchar)as BillCode,
       AddressLine1,
       AddressLine2,
       City_Suburb,
       StateAbbreviation,
       Zip_Postcode,
       CC.Country_Code,       
       Phone ,
       Phone as phone_ext,	   
       Email,
       '' as Fax,
       GETDATE() as create_date_time,
       'SYSTEM' as create_user_id,
       GETDATE() as last_update_date_time,
       'SYSTEM' as last_update_user_id
from MTrackDataImport..rawCustomerSISU C
	LEFT OUTER JOIN CD_country CC
	ON (CC.Company_Code = @Company_Code
	AND C.Country = CC.Country_Name)

This is my query. I am getting results but it not get sorted. So plz help me. Only sorting is not working. So how to sort please tell me.

Posted 15-Jun-14 20:26pm

Member 10685464

Updated 15-Jun-14 21:29pm

Kornfeld Eliyahu Peter

v2

Add a Solution

Comments

Murali Vijay 16-Jun-14 2:57am

You didn't mention whether you want asc or desc in row_number

Member 10685464 16-Jun-14 2:59am

I tried that one also but it didn't work

Murali Vijay 16-Jun-14 3:06am

post the code you have tried

Member 10685464 16-Jun-14 3:08am

declare @Company_Code int=5
SELECT distinct @Company_Code,
'bc'+ cast(row_number()over(order by customerID asc) as nvarchar(100))as BillCode,
isnull(AddressLine1,'') as AddressLine1,
isnull(AddressLine2,'') as AddressLine1,
isnull(City_Suburb,'') as City_Suburb,
isnull(StateAbbreviation,'') as StateAbbreviation,
Zip_Postcode,
CC.Country_Code,
Phone ,
Phone as phone_ext,
Email,
'' as Fax,
GETDATE() as create_date_time,
'SYSTEM' as create_user_id,
GETDATE() as last_update_date_time,
'SYSTEM' as last_update_user_id

from MTrackDataImport..rawCustomerSISU C

LEFT OUTER JOIN CD_country CC
ON (CC.Company_Code = @Company_Code
AND C.Country = CC.Country_Name)

this is my query

Murali Vijay 16-Jun-14 3:28am

try this

declare @Company_Code int=5

with CTE
AS( SELECT distinct @Company_Code,
'bc'+ cast(row_number()over(order by customerID asc) as nvarchar(100))as BillCode,
isnull(AddressLine1,'') as AddressLine1,
isnull(AddressLine2,'') as AddressLine1,
isnull(City_Suburb,'') as City_Suburb,
isnull(StateAbbreviation,'') as StateAbbreviation,
Zip_Postcode,
CC.Country_Code,
Phone ,
Phone as phone_ext,
Email,
'' as Fax,
GETDATE() as create_date_time,
'SYSTEM' as create_user_id,
GETDATE() as last_update_date_time,
'SYSTEM' as last_update_user_id

from MTrackDataImport..rawCustomerSISU C

LEFT OUTER JOIN CD_country CC
ON (CC.Company_Code = @Company_Code
AND C.Country = CC.Country_Name))

select BillCode,StateAbbreviation from cte

Member 10685464 16-Jun-14 3:29am

Thanks but still it's not working

Member 10685464 16-Jun-14 4:08am

Actually i am getting wrong records if i use row_number why it is happen plz help me.Means i am getting 1st record as 2nd means customer id=1 records show in customer id 2 . so what is problem i didn't understand please help me.

Member 10685464 16-Jun-14 5:45am

i got the solution actually customer ID is nvarchar field in table so i am converting that field in integer.And now it's working

coded007 19-Jun-14 11:11am

I hope we can't generate bill code by customerid? Memebre10685464 please clear this

Member 10685464 20-Jun-14 1:17am

No actually for we use customer id for getting row number only.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

R_Srinivasa_Reddy · Accepted Answer · 2014-06-24T22:17:00

-- method 1 : you can do this by customerID instead of BillCode (because there is no diff in prefix)

SELECT distinct @Company_Code,
--CustomerID,
'bc'+ cast(row_number()over(order by customerID) as varchar)as BillCode,
AddressLine1,
AddressLine2,
City_Suburb,
StateAbbreviation,
Zip_Postcode,
CC.Country_Code,
Phone ,
Phone as phone_ext,
Email,
'' as Fax,
GETDATE() as create_date_time,
'SYSTEM' as create_user_id,
GETDATE() as last_update_date_time,
'SYSTEM' as last_update_user_id
from MTrackDataImport..rawCustomerSISU C
LEFT OUTER JOIN CD_country CC
ON (CC.Company_Code = @Company_Code
AND C.Country = CC.Country_Name)

order by customerID

-- method 2 : you can do this by common table expression CTE

;with CTE as (SELECT distinct @Company_Code,
--CustomerID,
'bc'+ cast(row_number()over(order by customerID) as varchar)as BillCode,
AddressLine1,
AddressLine2,
City_Suburb,
StateAbbreviation,
Zip_Postcode,
CC.Country_Code,
Phone ,
Phone as phone_ext,
Email,
'' as Fax,
GETDATE() as create_date_time,
'SYSTEM' as create_user_id,
GETDATE() as last_update_date_time,
'SYSTEM' as last_update_user_id
from MTrackDataImport..rawCustomerSISU C
LEFT OUTER JOIN CD_country CC
ON (CC.Company_Code = @Company_Code
AND C.Country = CC.Country_Name)

)
select * from cte order by BillCode