Tsql question

Question

Tsql question

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

psangeetha (10/17/2008)
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.

Why don't you replace 'case_seq' with the number being returned from the view and forget about storing the value in the database? Is there a specific reason why you need this stored in the table?

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

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

Jeff, that is a very good question. But thats how the application is been set up. Depending on the case_seq number, the description in the form_items appears on the application screen. So thats the reason we would want the case_seq number be assigned with the alphabetic description in the form_items table.

Is there a way to get the value of the case_seq from the view and generate it in the insert record statement for the case_form table?

Thank you

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

psangeetha (10/17/2008)
Jeff, that is a very good question. But thats how the application is been set up. Depending on the case_seq number, the description in the form_items appears on the application screen. So thats the reason we would want the case_seq number be assigned with the alphabetic description in the form_items table.
Is there a way to get the value of the case_seq from the view and generate it in the insert record statement for the case_form table?
Thank you

I don't think you can do that - because you can't just insert the new row. You are going to have to insert the new row, then update the associated rows with the correct sequence number.

That is why I suggested calculating it instead. By using a view instead of the table - the value will always be updated.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

And, if the field you have to adjust is part of a primary key or unique index you actually have to accomplish the update first, then insert the new record.

😎

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 5

Thanks for your replies. I'm still trying to get this work and I'm not sure how I can do this with a view.

I'm thinking of inserting a record into case_form with case_seq as '0' and then update the records with the corresponding case_seq based on the form_items.description alphabetical order.

update case_form set case_seq = (select 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 will update the correct case_seq based on the description(alphabetical order) in form_items. But currently, I'm getting incorrect syntax near 'test'.

I would appreciate any help from you all. Thank you.