inserting rows

Question

Post reply

inserting rows

SQLTestUser

Hall of Fame

Points: 3997
More actions
October 18, 2010 at 7:38 pm

#230633

This is what i am trying to do
a store procedure that will insert rows to the user deduction table. Salary grade is determined by checking the salary grade table to derive the employee's salary grade. Raisae for users with no deduction and verify that a user is eligible for a deduction based upon their salary grade.Once you know an employee's grade, then verify if the employee is eligible for the deduction via comparing it to the minimum and maximum salary grade that is stored in the deduction table
this is what i am trying to accomplish
USer table has sal,userno,deptno
deductions name nvarchar(3) grd_min decimal (9,2) grd_maX decimal (9,2)
user_deduc (deduc Nvarchar(3),emp NUMBER(4),bfr_or_af CHAR, deduc amount int )

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

well here's the tables i constructed based on what you posted....but it's incomplete i think.

is a user the same as an employee id? none of the tables share a common column name.

i guess we need the actual table definitions as well as a couple of rows of sample data...you know INSERT INTO USERS... 'bob'

so we have code we can use to build working solutions.

CREATE TABLE [USERS](

userno int identity(1,1) not null primary key,

deptno int,

sal decimal( 9,2) )

CREATE TABLE deductions (

name nvarchar(3) ,

grd_min decimal (9,2),

grd_maX decimal (9,2) )

CREATE TABLE user_deduc (

deduc Nvarchar(3),

emp int,

bfr_or_af CHAR,

deduc_amount int )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 2

sorry about the incomplete post here are rest of the tables and an insert statement

CREATE TABLE deductions (

name nvarchar(3) ,

grd_min decimal (9,2),

grd_maX decimal (9,2) )

CREATE TABLE user_deduc (

deduc Nvarchar(3),

userno int,

bfr_or_af CHAR,

deduc_amount int )

user table

userno int NOT NULL,

username nVARCHAR(30) NOT NULL,

job nVARCHAR(30),

mgr decimal(5,2),

hiredate DATETIME,

sal decimal(7,2),

comm decimal(7,2),

deptno decimal(3) NOT NULL

);

CREATE TABLE SalGrade

(

gradeName nvarchar(3),

sal_grd_hgh decimal(7,2) ,

sal_grd_Lw Decimal(7,2)

);

Insert into SalGrade Values ( 'A',1000, 10000)

Insert into deductions values ( 'IRA',1, 2);

Insert into deductions values ( 'baseball',3, 5);

ALTER TABLE user add CONSTRAINT user_pk PRIMARY KEY (userno);

Insert into employee Values (1, 'A', 'Design' , '', 9000, 2, 1)

gradename in salgrade and deduc in user_deduc tables are foreign key to name in deduc table

userno in userdeduc is foregin key ko userno in usertable

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 3

can a simple block do it as well rather than a procedure ?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

SQLTestUser (10/18/2010)
can a simple block do it as well rather than a procedure ?

Yes.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 5

can you please direct me to where i can find an example

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 6

A Cursor could do it as well but i just dont know how we can loop thru the salgrade table and connect it to thtree tables, after that the insert should be easy

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

SQLTestUser (10/19/2010)
can you please direct me to where i can find an example

Bing: PL/SQL BLOCK INSERT 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 8

Inserting is easy its the cndtion check thats getting me

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 9

No answers please. Spam. Reported.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.