Post reply

inserting rows

  • 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 )

  • October 18, 2010 at 7:46 pm

    #1238800

    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!

  • October 18, 2010 at 7:57 pm

    #1238803

    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

  • October 18, 2010 at 8:48 pm

    #1238807

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

  • October 19, 2010 at 6:15 am

    #1238979

    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.

  • October 19, 2010 at 9:09 am

    #1239107

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

  • October 19, 2010 at 9:22 am

    #1239119

    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

  • October 19, 2010 at 9:24 am

    #1239123

    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.

  • October 19, 2010 at 10:18 am

    #1239173

    Inserting is easy its the cndtion check thats getting me

  • December 1, 2010 at 9:21 am

    #1257461

    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.

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

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