How get One by one record from Data Base Table ,for stored procedure based on ranking

Question

0.00/5 (No votes)

See more:

I have Table in MySQL consist of following columns ..

SQL

1.Id           int(11)  AI true
2.service Id    varchar(10)
3.FedTaxId      varchar(10)
4.Authority     varchar(20)
5.Account       Int(11)

like these my table exist,here I am not set any rank at the time of creation ,But I have requirement like these ..

SQL

1   101  12345 12-123  Nevada     589758.9-35
2   102  12346 12-124  California 1247895555
3   103                Idaho      55555555

like that I have data in MySQL,I want to set format of "######.#-##" like these for "Nevada" account Only,so I need Only One record for at a time for My stored procedure,To verify the "State Name" is "Nevada" or not Based On That ,I am using "Concat()" Method to achieve the goal but here ,I am struck..Based on Ranking How to get the record until ,all rows are Exhausted unfortunately...How get Only One By One record ...with out using any cursor inside stored procedure.

Posted 13-Jun-13 3:35am

sairam pamidi

Updated 1-Jul-13 3:50am

Sudhakar Shinde

v3

Add a Solution

Comments

Sudhakar Shinde 1-Jul-13 10:00am

Really not able to understand what is that you want to achieve. If you have tried something then share your code for better understanding.

Maciej Los 1-Jul-13 11:32am

Agree ;)

Adarsh chauhan 5-Jul-13 2:54am

Can't say about others but your question is not clear to me.. could you please explain a bit clearly what exactly you want to achieve??

sairam pamidi 5-Jul-13 5:54am

hello Adarsh ,
My table is consist of some record, example Take 3 records..In that I want to set format like these "######.#-##" at the time user query the table information .....only for the state Name have the "Neveda"

I need nevada state like "5555555.5-55" format
other state "1111111111" like that ,I think These helping to u to ,understand the question.

Adarsh chauhan 5-Jul-13 6:06am

your table doesn't have any column as 'state' is it any computed result or you are referring to account column as state?? Also tell me one thing do you mean a query like select * from abc where authority='NEVEDA'???
and if you want to save account as 123456.4-23 then why are you taking the column as int???

sairam pamidi 5-Jul-13 6:17am

sorry,for that state means "authority"....yes,Here I don't want to save in that format ,and also value stored in database like"555555555" But I want to show for nevada state at the time selection from table "555555.5-55".

Adarsh chauhan 5-Jul-13 6:38am

see the posted answer.
Is it what you want to do???

2 solutions

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)

Adarsh chauhan · Accepted Answer · 2013-07-05T00:36:00

Hi,

as per the details what i think you want to do can be done as follows in sql server.

create table test
(
Id  int, 
serviceId    varchar(10),
FedTaxId      varchar(10),
Authority     varchar(20),
Account       Int
)

insert into test values (101,'12345','12-123','nevada',555555555),
insert into test values (102,'12346','12-124','California',1247895555),
insert into test(Id,Authority,Account) values (103,'Idaho',55555555)


select ID,serviceId,FedTaxId,Authority, 
case when authority='Nevada' then 
(
LEFT(Convert(varchar,Account),6)+'.'+substring (Convert(varchar,Account),7,1)+'-'+substring (Convert(varchar,Account),8,2)
)
else Convert(varchar,Account) end as Account
from test

* I am assuming that you want result as 6digits.1digit-2digits(######.#-##) format

result:

ID	serviceId	FedTaxId	Authority	Account
101	12345	12-123	Nevada	555555.5-55
102	12346	12-124	California	1247895555
103	NULL	NULL	Idaho	55555555

sairam pamidi · Accepted Answer · 2013-07-06T00:03:00

Solution 2

SQL

SELECT ID,serviceId,FedTaxId,Authority,
CASE WHEN authority='Nevada' THEN
(concat(substring(Account,1,6),".",substring(Account,7,2),"-" ,substring(Account,9,1) )
)ELSE convert(Account,char(10)) END AS Account
FROM test;

It's for Mysql data base.,used The http://sqlfiddle.com/[^]

Posted 6-Jul-13 0:03am