October 17, 2008 at 11:27 am
Hi all,
I have a table called case and there is an insert script to insert a record into this table.
select * from case_form
test1arc
test2
test3hct
test 4ber
tech1chr
tech2ikr
file1wri
file2sbn
insert into case_form (case_name,case_seq,case_cred) select 'test',max(case_sequence)+1, 'are' from case_form where case_name='test';
This script will add the record with case_seq as '5'.
But we would like the script to add the record with correct seq number to be alphabetical(case_name).
If anyone can help me out on this, it will be very helpful.
Thanks.
October 17, 2008 at 11:53 am
I'm not sure what you are asking. What exactly do you want to be alphabetical? The case name which is 'test', the sequence number (5), or the case_cred 'are'?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 12:18 pm
psangeetha (10/17/2008)
Hi all,I have a table called case and there is an insert script to insert a record into this table.
select * from case_form
test1arc
test2
test3hct
test 4ber
tech1chr
tech2ikr
file1wri
file2sbn
insert into case_form (case_name,case_seq,case_cred) select 'test',max(case_sequence)+1, 'are' from case_form where case_name='test';
This script will add the record with case_seq as '5'.
But we would like the script to add the record with correct seq number to be alphabetical(case_name).
If anyone can help me out on this, it will be very helpful.
Thanks.
I'm not sure I quite understand what your asking for either 🙂 It sounds like you are looking for an order by case_name asc in your select 🙂
Tommy
Follow @sqlscribeOctober 17, 2008 at 12:49 pm
Sorry everyone for not explaining clearly in my first message. I request to please ignore my previous message.
Here is what I need. Below are two tables:
select * from form_items
case_credform_typedescription
arc F A/E CC Auth
wri F Corr Inv Rel
ner M Back Order Rel
cct F Auto Order Rel
ikr F Inv Rel
klc F Aud In Table
sbn F Serv Inv Rel
chr M Cred Auth Rel
Select * From case_form
case_namecase_seqcase_cred
test 1 arc
test 2 cct
test 3 klc
test 4 ner
tech 1 chr
tech 2 ikr
file 1 wri
file 2 sbn
First inserting record into form_items table.
insert into form_items (case_cred,form_type,description) values ('are','F','A/R Auth Table')
1 row inserted.
Next, I need to insert a record into case_form table. The case_seq should be assigned based on the form_items.description in the alphabetical order. I can get the next case_seq from this below select query.
select a.description,a.case_cred,b.case_seq from form_items a,case_form b where
b.case_name='test' and a.case_cred=b.case_cred order by a.description
A/E CC Auth arc 1
Auto Order Relcct 2
Aud In Table klc 3
Back Order Relner 4
For example, inserting below record in case_form,
insert into case_form (case_name,case_seq,case_cred) select 'test',?, 'are' from case_form where case_name='test';
The form_items.description is 'A/R Auth Table' for case_cred='are'. So case_form.case_seq should be assigned '2'. But since cct has been assigned to 2 already, it has to be moved down to 3 and similarly other records in case_form. It should be like below after inserting this record in case_form.
Select * From case_form
test 1 arc
test 2 are
test 3 cct
test 4 klc
test 5 ner
tech 1 chr
tech 2 ikr
file 1 wri
file 2 sbn
Basically, we want to assign the case_seq based on the form_items description alphabetical order. We need a query to insert record into case_form table, that can assign the case_seq based on the description of the form_items table alphabetically for that corresponding case_cred. We have to script the query so we can give our clients to run it. Sorry this is very confusing, but please help me if you have any suggestions.
Thanks a lot.
October 17, 2008 at 1:05 pm
Where do "test", "Tech" and "File" come into play and how do I know which one the new form_items row is going to fit into?
Why do you need the sequence number if it is based on the alphabetical order of the description? Couldn't you just order by description asc?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 1:06 pm
Clarifying:
If you insert them out of alphabetical order, you want the sequence number to also change?
~Craig
Craig Outcalt
October 17, 2008 at 1:15 pm
Jack,
When I will first do an insert ('are','F','A/R Auth Table') into form_items table.
After this, I have to do an insert into case_form table with case_name='test' and case_cred='are' But to insert this record I need to get the case_seq for this record based on the form_items.description. Both tables have the same case_cred as 'are'. With this we need to fit find the case_seq according to the alphabetical order of form_items.description. Like below:
select a.description,a.case_cred,b.case_seq from form_items a,case_form b where
b.case_name='test' and a.case_cred=b.case_cred order by a.description
A/E CC Auth arc 1
Auto Order Rel cct 2
Aud In Table klc 3
Back Order Rel ner 4
For the case_form.case_cred='are', case_name='test', the description is 'A/R Auth Table' which fits in case_seq 2 from the above select query. Manually I can just run this query and find what the case_seq will be. But I need to script it in an insert query for case_form so we can give our clients to run it.
Hope this explains.
Thanks for your help.
October 17, 2008 at 1:27 pm
Craig, In the above example, the inserting record in case_form table will be the 2nd record for case_name='test'. So, yes the existing record with case_seq=2 for case_name='test' should be updated with case_seq='3' and the existing record with case_seq='3' for case_name='test should be updated with case_seq='4' and so on...
The table should look like this
Select * From case_form
test 1 arc
test 2 are ---> Record after the insert.
test 3 cct
test 4 klc
test 5 ner
tech 1 chr
tech 2 ikr
file 1 wri
file 2 sbn
October 17, 2008 at 1:28 pm
What you need is a trigger that reorders the case_seq after an insert or update of case_cred.
This will solve your problem.
Good luck!
Craig Outcalt
October 17, 2008 at 1:32 pm
Oh?? WIll you be able to help me with that??? Not sure how I can create for this.
Thanks much.
October 17, 2008 at 1:41 pm
Are you sure you really need to store that information? If not, you can return that information using row_number() in the query (or create a view) that would generate the correct value when you select the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 17, 2008 at 1:48 pm
That's a tall order, but I can help you.
read up on Create Trigger
http://msdn.microsoft.com/en-us/library/ms189799(SQL.90).aspx
and using the inserted and deleted tables
http://msdn.microsoft.com/en-us/library/ms191300.aspx
so once you know how to create a trigger, you need to create a trigger for insert, delete and update on your table
inside your trigger use a cursor to select everything in the case name that was inserted or deleted
ordered by case_cred
Then use a counter variable to keep track of the next number and assign it to cse_seq with an update statement.
for the first record it will be 1
next record it will be 2
n=n+1...
because you are updating the same table that the trigger is fired on, make sure to specify that recursive triggers are set to 'off'... or perhaps use an 'instead of' trigger to insert the values in the right spot and reorder the other columns.... either way will work.
Craig Outcalt
October 17, 2008 at 1:49 pm
Jeff,
To create a view to generate the case_seq from the form_items table like below:
create view caseseq (case_seq)
as
select b.case_seq from form_items a,case_form b where
b.case_name='test' and a.case_cred=b.case_cred order by a.description;
But I am not sure, how I can write the view to fit the corresponding 'A/R Auth Table' alphabetically in this query.
Also, if I create a view, how can I get this case_seq number when I insert a record into case_form table???
Any help is appreciated.Thank you.
October 17, 2008 at 2:03 pm
psangeetha (10/17/2008)
Jeff,To create a view to generate the case_seq from the form_items table like below:
create view caseseq (case_seq)
as
select b.case_seq from form_items a,case_form b where
b.case_name='test' and a.case_cred=b.case_cred order by a.description;
But I am not sure, how I can write the view to fit the corresponding 'A/R Auth Table' alphabetically in this query.
Also, if I create a view, how can I get this case_seq number when I insert a record into case_form table???
Any help is appreciated.Thank you.
SELECT a.description
,a.case_cred
,row_number() over(partition by b.case_name order by a.description)
FROM form_items a
JOIN case_form b ON b.case_cred = a.case_cred
WHERE b.case_name = 'test';
Not sure if this is exactly what you are looking for, but should give you an idea of how to set it up.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 17, 2008 at 2:12 pm
Jeff, Thanks. The view gives me the output of the select query.
create view test (case_seq,description,case_cred,number)
as SELECT b.case_seq,a.description
,a.case_cred
,row_number() over(partition by b.case_name order by a.description)
FROM form_items a
JOIN case_form b ON b.case_cred = a.case_cred
WHERE b.case_name = 'test';
But I need to get the case_seq when I insert record into case_form table based on where the form_items_description will fit in alphabetically.
insert into case_form (case_name,case_seq,case_cred) select 'test',????????, 'are' from case_form where case_name='test';
Thanks.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply