Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQLdeveloper question (possibly) with MAX(value)

Author  Topic 

Shego
Starting Member

7 Posts

Posted - 2013-05-22 : 19:43:21
So, I'm trying to find the lowest and highest salary amount possible for each employee in an organization. Salaries are coded by SalaryGroup_code (SGRP_code), grade, step, and amount, and are linkable to jobs by the sgrp_code. Jobs are defined on a different table, and linkable to persons (a third table) through identifying numbers (PIDM). I'm looking to get only one line of data for each person that is defined thusly:

person_name, person_pidm, job_description, job_sgrp_code, min(salary_amount), max(salary_amount)

The query I've written looks like this:

SELECT person_name, person_pidm, job_description, job_sgrp_code, min(salary_amount), max(salary_amount)
FROM person, job, salary
WHERE person_pidm=job_pidm
AND job_sgrp_code=salary_sgrp_code
GROUP BY person_name,person_pidm,job_description,job_sgrp_code

This appears to run properly, in that it selects a minimum and maximum value for each person for each row of data. Unfortunately it selects the same min/max salary values for each person for each row of data, and I know for a fact that this is not true. There is no way an intern's max salary is the same as the ceo's max salary..... it's just not logical.

So my question is "what have I done wrong" or maybe "what haven't I done that I should have"????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 00:25:28
so as per your explanation your query should work properly. You're linking on salary_sgrp_code for getting the salaray value and that should be the way to go as salary should be defined based on group. If you can post some sample data for us we might be able to validate if data is coming as per expectations and if not, suggest an alternative.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-23 : 04:38:32
your joins are probably incorrect. Maybe an implicit cross join.

Using ANSI syntax is both advised and more readable. Something like:

SELECT
p.person_name
, p.person_pidm
, j.job_description
, j.job_sgrp_code
, min(s.salary_amount)
, max(s.salary_amount)
FROM
person AS p
JOIN job AS j ON j.job_pidm = p.person_pidm
JOIN salary AS s ON
s.salary_sgrp_code = j.job_sgrp_code
AND <JOIN FROM PERSON to SALARY>
GROUP BY
p.person_name,
p.person_pidm,
j.job_description,
j.job_sgrp_code

Note the line in red...

So I think right now you are doing an implicit cross join between person and salary.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

Shego
Starting Member

7 Posts

Posted - 2013-05-23 : 13:28:07
Yes, I'm using an implicit connection between person and salary. This is because there are no explicit connections between these two tables, the only way (as far as I know) to connect them is through the commonality of the job. I feel almost as if I need to set up a temporary table or two, as each person can have more than one job and each job can belong to more than one person, just as each job can have more than one salary grade, which has more than one step, which determines the actual amount.

Normally I'd direct the query to the highest grade and step, but each sgrp_code has it's own set of grades and steps, and there is no consistency.

I expect the output to look something like this:
name............pidm......job..........sgrp......minSal....maxSal
JohnSmith.....11111.....cook.......88888....12000.....038000
JaneSmith.....11112.....shepherd 44444....10000.....020000
JoeySmith.....11113.....pirate.....77777.....45000....120000
JackSmith.....11114.....driver.....66666.....17000.....027000


but what I'm getting is this:
name............pidm......job..........sgrp......minSal....maxSal
JohnSmith.....11111.....cook.......88888....10000.....030000
JaneSmith.....11112.....shepherd.44444....10000.....030000
JoeySmith.....11113.....pirate.....77777....10000.....030000
JackSmith.....11114.....driver.....66666.....10000.....030000

Edit: Further, if I ask for the grade and step, they also reflect as the same, no matter what the sgrp_code is.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-23 : 14:14:01
Can you provide your table definitions and some data in consumable format?
The information you provided is good but not enough to solve the problem you are trying to address.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 00:29:44
you explanantion doesnt make sense to anyone seeing unless they either see your tables or have access to your data. Thats why we're repeatedly asking you to post some sample data from tables in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Shego
Starting Member

7 Posts

Posted - 2013-05-24 : 14:11:45
This is the Person table. I have limited access to the others, but they are stored on my desktop in a different format.

--------------------------------------------------------
-- DDL for Table SPRIDEN
--------------------------------------------------------

CREATE TABLE "SATURN"."SPRIDEN"
( "SPRIDEN_PIDM" NUMBER(8,0),
"SPRIDEN_ID" VARCHAR2(9 CHAR),
"SPRIDEN_LAST_NAME" VARCHAR2(60 CHAR),
"SPRIDEN_FIRST_NAME" VARCHAR2(60 CHAR),
"SPRIDEN_MI" VARCHAR2(60 CHAR),
"SPRIDEN_CHANGE_IND" VARCHAR2(1 CHAR),
"SPRIDEN_ENTITY_IND" VARCHAR2(1 CHAR),
"SPRIDEN_ACTIVITY_DATE" DATE,
"SPRIDEN_USER" VARCHAR2(30 CHAR),
"SPRIDEN_ORIGIN" VARCHAR2(30 CHAR),
"SPRIDEN_SEARCH_LAST_NAME" VARCHAR2(60 CHAR),
"SPRIDEN_SEARCH_FIRST_NAME" VARCHAR2(60 CHAR),
"SPRIDEN_SEARCH_MI" VARCHAR2(60 CHAR),
"SPRIDEN_SOUNDEX_LAST_NAME" CHAR(4 CHAR),
"SPRIDEN_SOUNDEX_FIRST_NAME" CHAR(4 CHAR),
"SPRIDEN_NTYP_CODE" VARCHAR2(4 CHAR),
"SPRIDEN_CREATE_USER" VARCHAR2(30 CHAR),
"SPRIDEN_CREATE_DATE" DATE,
"SPRIDEN_DATA_ORIGIN" VARCHAR2(30 CHAR),
"SPRIDEN_CREATE_FDMN_CODE" VARCHAR2(30 CHAR),
"SPRIDEN_SURNAME_PREFIX" VARCHAR2(60 CHAR)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEVELOPMENT" ;

COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_PIDM" IS 'Internal identification number of the person.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_ID" IS 'This field defines the identification number used to access person on-line.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_LAST_NAME" IS 'This field defines the last name of person.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_FIRST_NAME" IS 'This field identifies the first name of person.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_MI" IS 'This field identifies the middle name of person.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_CHANGE_IND" IS 'This field identifies whether type of change made to the record was an ID number change or a name change. Valid values: I - ID change, N - name change.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_ENTITY_IND" IS 'This field identifies whether record is person or non-person record. It does not display on the form. Valid values: P - person, C - non-person.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_ACTIVITY_DATE" IS 'This field defines the most current date record is created or changed.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_USER" IS 'USER: The ID for the user that most recently updated the record.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_ORIGIN" IS 'ORIGIN: The name of the Banner Object that was used most recently to update the row in the spriden table.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SEARCH_LAST_NAME" IS 'The Last Name field with all spaces and punctuation removed and all letters capitalized.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SEARCH_FIRST_NAME" IS 'The First Name field with all spaces and punctuation removed and all letters capitalized.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SEARCH_MI" IS 'The MI (Middle Initial) field with all spaces and punctuation removed and all letters capitalized.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SOUNDEX_LAST_NAME" IS 'The Last Name field in SOUNDEX phonetic format.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SOUNDEX_FIRST_NAME" IS 'The First Name field in SOUNDEX phonetic format.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_NTYP_CODE" IS 'NAME TYPE CODE: The field is used to store the code that represents the name type associated with a person''s name.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_CREATE_USER" IS 'Record Create User: This field contains Banner User ID which created new record';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_CREATE_DATE" IS 'Record Create Date: This field contains date new record created';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_DATA_ORIGIN" IS 'DATA SOURCE: Source system that generated the data';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_CREATE_FDMN_CODE" IS 'PII DOMAIN: PII Domain of the user who created the spriden row.';
COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SURNAME_PREFIX" IS 'SURNAME PREFIX: Name tag preceding the last name or surname. (Van, Von, Mac, etc.)';
COMMENT ON TABLE "SATURN"."SPRIDEN" IS 'Person Identification/Name Repeating Table';
Go to Top of Page

Shego
Starting Member

7 Posts

Posted - 2013-05-24 : 14:13:14
This is the Job table.

                                                           TABLE: NBRJOBS

CREATOR: POSNCTL

Assignment Repeating Table

===================================================================================================================
Column Name Type Width Scale Nulls Column Comments
=============================== ========= ===== ===== ======= =====================================================
NBRJOBS_PIDM NUMBER 8 NO PIDM: Internal identification number of the employee.
NBRJOBS_POSN VARCHAR2 6 NO POSITION: Position Number.
NBRJOBS_SUFF VARCHAR2 2 NO SUFFIX: Suffix of the job.
NBRJOBS_EFFECTIVE_DATE DATE 7 NO EFFECTIVE DATE: Date that the Job Assignment record
becomes effective.
NBRJOBS_STATUS VARCHAR2 1 NO STATUS: Status of the job. Valid values are A =
Active, B = Leave without pay with benefits, L = Leave
without pay without benefits, F = Leave with full pay
and benefits, P = Leave with partial pay and
benefits, T = Terminated.
NBRJOBS_DESC VARCHAR2 30 YES DESCRIPTION: Description of the job. Defaults from
the Position Title if left blank.
NBRJOBS_ECLS_CODE VARCHAR2 2 NO EMPLOYEE CLASSIFICATION CODE: The employee class
attached to the job, based on the Employee Class
entered on the Position Form (NBAPOSN).
NBRJOBS_PICT_CODE VARCHAR2 2 NO PAY ID CODE: The Pay ID attached to the job.
NBRJOBS_COAS_CODE_TS VARCHAR2 1 YES TIME SHEET CHART OF ACCOUNTS CODE: Valued only if
Banner Finance is installed.
NBRJOBS_ORGN_CODE_TS VARCHAR2 6 NO TIME SHEET ORGANIZATION: Organization where time sheet
is sent for this job.
NBRJOBS_SAL_TABLE VARCHAR2 2 YES SALARY TABLE: The Salary Table attached to the job.
Defaults from the Salary/Rate Rule Form (NTRSALA) via
the Position Form (NBAPOSN).
NBRJOBS_SAL_GRADE VARCHAR2 5 YES SALARY GRADE: The Salary Grade attached to the job.
Defaults from the Salary/Rate Rule Form (NTRSALA) via
the Position Form (NBAPOSN).
NBRJOBS_SAL_STEP NUMBER 3 YES SALARY STEP: The Salary Step attached to the job.
Defaults from the Salary/Rate Rule Form (NTRSALA) or
can be entered online.
NBRJOBS_APPT_PCT NUMBER 5 2 NO APPOINTMENT PERCENTAGE: The Appointment percent
attached to the Job. Used to establish the percentage
of full time appointment for an employee in a
position. The value defaults from the position
record, but the user may change at the job level.
The appointment percent cannot exceed 100.
NBRJOBS_FTE NUMBER 6 3 YES FULL TIME EQUIVALENCY: The full time percentage
attached to the job. Defaults to 1.
NBRJOBS_HRS_DAY NUMBER 6 2 NO HOURS PER DAY: The hours attached to the job that are
normally worked in a day.
NBRJOBS_HRS_PAY NUMBER 6 2 NO HOURS PER PAY: The hours attached to the job that are
normally worked in a pay period.
NBRJOBS_SHIFT VARCHAR2 1 NO SHIFT: The shift attached to the job.
NBRJOBS_REG_RATE NUMBER 11 6 YES REGULAR RATE: The regular rate attached to the job.
If this field is entered or defaulted from the
Salary/Rate Rule Form, the Assign Salary is system
calculated and vice versa.





Date: 18-DEC-2008 Page: 45

===================================================================================================================
Column Name Type Width Scale Nulls Column Comments
=============================== ========= ===== ===== ======= =====================================================
NBRJOBS_ASSGN_SALARY NUMBER 11 2 NO ASSIGNMENT SALARY: The salary to be paid each pay
period. If the Assignment Salary is entered, the
Annual Salary is calculated based on the factor and
vice versa.
NBRJOBS_FACTOR NUMBER 3 1 NO FACTOR: The number of pays per calendar year.
NBRJOBS_ANN_SALARY NUMBER 11 2 NO ANNUAL SALARY: The annual salary attached to the job
based on the calendar year. If the Annual Salary is
entered, the Assignment Salary is system calculated
based the factor and vice versa.
NBRJOBS_PER_PAY_SALARY NUMBER 11 2 NO PER PAY SALARY: Used for deferred pay calculations.
NBRJOBS_PAYS NUMBER 3 1 NO PAYS: Used for deferred pay calculations.
NBRJOBS_PER_PAY_DEFER_AMT NUMBER 11 2 NO PER PAY DEFERRED AMOUNT: The amount deferred each pay
period for deferred pay calculation.
NBRJOBS_ACTIVITY_DATE DATE 7 NO Date of last activity (insert or update) on the
record.
NBRJOBS_JCRE_CODE VARCHAR2 5 YES CHANGE REASON CODE: Associates the reason for the
change with the job detail record.
NBRJOBS_SGRP_CODE VARCHAR2 6 NO SALARY GROUP CODE: The Salary Group Code attached to
the job.
NBRJOBS_EMPR_CODE VARCHAR2 4 YES EMPLOYER CODE: The Employer Identification code
attached to the job.
NBRJOBS_LGCD_CODE VARCHAR2 4 YES LONGEVITY CODE: Longevity code attached to the job.
NBRJOBS_LOCN_CODE VARCHAR2 6 YES LOCATION CODE: EEO location attached to the job.
NBRJOBS_SCHL_CODE VARCHAR2 6 YES SCHOOL CODE: EEO school attached to the job.
NBRJOBS_SUPERVISOR_PIDM NUMBER 8 YES SUPERVISOR PIDM: Internal identification number of the
supervisor attached to the job.
NBRJOBS_SUPERVISOR_POSN VARCHAR2 6 YES SUPERVISOR POSN: The position number of the supervisor
attached to the job.
NBRJOBS_SUPERVISOR_SUFF VARCHAR2 2 YES SUPERVISOR SUFFIX: Suffix attached to the supervisor's
position.
NBRJOBS_WKCP_CODE VARCHAR2 4 YES WORKER COMPENSATION CODE: Workers' Compensation
Classification Code attached to the job.
NBRJOBS_JBLN_CODE VARCHAR2 6 YES JOB LOCATION CODE: Job Location Code attached to the
job.
NBRJOBS_PERS_CHG_DATE DATE 7 NO PERSONNEL CHANGE DATE: Date the change was actually
made as opposed to the effective date which is for
payroll purposes.
NBRJOBS_PCAT_CODE VARCHAR2 4 YES PREMIUM PAY CODE: Generates premium pay attached to
the job.
NBRJOBS_DFPR_CODE VARCHAR2 4 YES DEFER PAY CODE: The defer pay schedule attached to the
job. Defaults from PTRDFPR.
NBRJOBS_ENCUMBRANCE_HRS NUMBER 6 2 YES ENCUMBRANCE HOURS: Hours used to calculate the
encumbrance amount for this job in the active fiscal
year.
NBRJOBS_CONTRACT_NO VARCHAR2 8 YES JOB CONTRACT NUMBER: Job contract number attached to
the job.
NBRJOBS_STRS_ASSN_CODE VARCHAR2 2 YES STRS ASSIGNMENT CODE: STRS Assignment Code attached to
the job. Valid values are STRS mandated.
NBRJOBS_STRS_PAY_CODE VARCHAR2 2 YES STRS PAY CODE: STRS Pay Code for the employee attached
to the job. The value selected determines how the
employee's salary will be reported to STRS
administration on the monthly F-496 report.
NBRJOBS_PERS_PAY_CODE VARCHAR2 2 YES PERS PAY CODE: PERS Pay Code for the employee attached
to the job. The value selected determines how the
employee's salary will be reported to PERS
administration on the PERS Monthly Contribution
Report.





Date: 18-DEC-2008 Page: 46

===================================================================================================================
Column Name Type Width Scale Nulls Column Comments
=============================== ========= ===== ===== ======= =====================================================
NBRJOBS_TIME_ENTRY_METHOD VARCHAR2 1 NO TIME ENTRY METHOD: Time Entry Method attached to the
job. Valid values are P = Payroll, W = Web, D =
Departmental, T = Third Party.
NBRJOBS_TIME_ENTRY_TYPE VARCHAR2 1 NO TIME ENTRY TYPE: Time Entry Type attached to the job.
It represents the structure of how time will be
entered over the Web or on the Electronic Approvals of
Time Entry Form (PHATIME). Valid values are T = Time
Sheet, E = Exception Time Only, N = None.
NBRJOBS_TIME_IN_OUT_IND VARCHAR2 1 NO TIME IN AND OUT INDICATOR: Time In/Out Indicator
attached to the job. Valiod values are Y = Yes and N
= No. If Y, this employee must enter time in and out
information on the Web or have it entered by a

department administrator on the Electronic Approvals
of Time Entry Form (PHATIME). Time In and out must be
entered for all earn codes that require hours entry.
Time in and out will not be entered for earn codes
that require units entry.
NBRJOBS_LCAT_CODE VARCHAR2 2 YES LEAVE CATEGORY CODE: A code which identifies a group
of leave types. It is used when the Leave by Job
Method has been selected for leave processing.
NBRJOBS_LEAV_REPT_METHOD VARCHAR2 1 NO LEAVE REPORT ENTRY METHOD: Leave Report Method
Attached to the job. Valid values are P = None, W =
Leave Report on the Web, D = Departmental Leave
Report, T = Third Party Leave Report.
NBRJOBS_PICT_CODE_LEAV_REPT VARCHAR2 2 NO LEAVE REPORT PAY ID: The Leave Report Pay ID attached
to the job and associated with a leave report or leave
request.
NBRJOBS_USER_ID VARCHAR2 30 YES USER ID: The Oracle ID of the user who changed the
record.
NBRJOBS_DATA_ORIGIN VARCHAR2 30 YES DATA ORIGIN: Source system that created or updated the
row.
Go to Top of Page

Shego
Starting Member

7 Posts

Posted - 2013-05-24 : 14:14:24
This is the Salary table.


TABLE: NTRSALA

CREATOR: POSNCTL

Salary/Rate Rule Table

===================================================================================================================
Column Name Type Width Scale Nulls Column Comments
=============================== ========= ===== ===== ======= =====================================================
NTRSALA_TABLE VARCHAR2 2 NO SALARY TABLE: The Salary Table being defined.
NTRSALA_GRADE VARCHAR2 5 NO SALARY GRADE: The Salary Grade associated with the
Salary Table being defined.
NTRSALA_STEP NUMBER 3 NO SALARY STEP: The Salary Step associated with the
Salary Table and Grade being defined.
NTRSALA_AMOUNT NUMBER 13 4 NO ANNUAL SALARY/HOURLY RATE AMOUNT: The annual salary or
hourly rate associated with this Table, Grade, and
Step.
NTRSALA_ACTIVITY_DATE DATE 7 NO Activity Date: Date of Last Modification of the
Record;
NTRSALA_SGRP_CODE VARCHAR2 6 NO SALARY GROUP CODE: The Group Code associated with a
set of Salary Tables and Grades.

Go to Top of Page

Shego
Starting Member

7 Posts

Posted - 2013-05-24 : 14:22:51
Then there are these two other tables I've discovered and have been referencing:
This one contains position information.

TABLE: NBBPOSN

CREATOR: POSNCTL

Position Base Table

===================================================================================================================
Column Name Type Width Scale Nulls Column Comments
=============================== ========= ===== ===== ======= =====================================================
NBBPOSN_POSN VARCHAR2 6 NO Position.
NBBPOSN_STATUS VARCHAR2 1 NO Status.
NBBPOSN_TITLE VARCHAR2 30 NO Title.
NBBPOSN_PLOC_CODE VARCHAR2 6 YES Position Location code.
NBBPOSN_BEGIN_DATE DATE 7 NO POSITION BEGIN DATE: First date the position is funded
to begin.
NBBPOSN_END_DATE DATE 7 YES POSITION END DATE: End date of the position. If null,
the position is presumed to be a permanent position.
NBBPOSN_TYPE VARCHAR2 1 NO Single or pooled position.
NBBPOSN_PCLS_CODE VARCHAR2 5 NO POSITION CLASS CODE: The position class the defined
position is assigned to.
NBBPOSN_ECLS_CODE VARCHAR2 2 NO POSITION EMPLOYEE CLASS CODE: Employee class code
defaults from the Position Class Rule Form but can be
overriden.
NBBPOSN_POSN_REPORTS VARCHAR2 6 YES Position that this position reports to. A superior.
NBBPOSN_AUTH_NUMBER VARCHAR2 9 YES POSITION AUTHORIZED NUMBER: The position number that
authorized the approval of the defined position.
NBBPOSN_TABLE VARCHAR2 2 NO Salary table.
NBBPOSN_GRADE VARCHAR2 5 NO SALARY GRADE: Position salary grade defaults from the
Position Class Rule Form but can be overriden.
NBBPOSN_STEP NUMBER 3 YES Salary step.
NBBPOSN_APPT_PCT NUMBER 5 2 NO POSITION APPOINTMENT PERCENT: Appointment percent
indicates the percentage of full time the position
uses. The salary, leave accrual and budget rolls are
modified by this percentage.
NBBPOSN_CIPC_CODE VARCHAR2 6 YES COLLEGE INSTRUCTIONAL PROGRAM CODE: Predefined codes
that are validated against the Student College
Instructional Validation Form.
NBBPOSN_ROLL_IND VARCHAR2 1 NO Budget roll indicator.
NBBPOSN_COAS_CODE VARCHAR2 1 YES Position Chart of Accounts Code: Valued only if Banner
Finance is installed
NBBPOSN_ACTIVITY_DATE DATE 7 NO Date of last activity on this record.
NBBPOSN_SGRP_CODE VARCHAR2 6 YES Salary Group: The Salary group that the table/grade on
position belongs to.
NBBPOSN_PGRP_CODE VARCHAR2 4 YES PGRP_CODE: The Position Group code.
NBBPOSN_WKSH_CODE VARCHAR2 4 YES WORK SCHEDULE CODE: Determines if a Work Schedule is
defaulted when a job is established.
NBBPOSN_PREMIUM_ROLL_IND VARCHAR2 1 NO PREMIUM EARNINGS ROLL INDICATOR: Indicator that
specifies how to roll budget for premium earnings.
Values are (C)urrent Budget Amount, (A)ctual
Expenditure for Current Year, (Z)ero, or (N)ot
Applicable.
NBBPOSN_PFOC_CODE VARCHAR2 3 YES FEDERAL OCCUPATIONAL CODE: Federal Occupational Code.
NBBPOSN_PNOC_CODE VARCHAR2 6 YES NATIONAL OCCUPATIONAL CODE: National Occupational
Code.





Date: 18-DEC-2008 Page: 17

===================================================================================================================
Column Name Type Width Scale Nulls Column Comments
=============================== ========= ===== ===== ======= =====================================================
NBBPOSN_DOTT_CODE VARCHAR2 11 YES OCCUPATIONAL TITLE CODE:field specifies the Dictionary
of Positions Occupational Titles Code.
NBBPOSN_CHANGE_DATE_TIME DATE 7 NO CHANGE DATE TIME: Date and Time this record was
changed.
NBBPOSN_CALIF_TYPE VARCHAR2 1 YES CALIFORNIA TYPE: California Pension Position Class
Type.
NBBPOSN_EXEMPT_IND VARCHAR2 1 NO EXEMPTION INDICATOR: Indicates if employees assigned
to positions in this Position Class will be exempt or
non-exempt.
NBBPOSN_JBLN_CODE VARCHAR2 6 YES JOB LOCATION CODE: Code associated with this Position.
NBBPOSN_BARG_CODE VARCHAR2 2 YES BARGAINING UNIT CODE: Code associated with this
Position.
NBBPOSN_PROBATION_UNITS NUMBER 3 YES PROBATION UNITS: Identifies the number of Probationary
Units.
NBBPOSN_COMMENT VARCHAR2 2000 YES COMMENT: This field stores user comments.
NBBPOSN_ACCRUE_SENIORITY_IND VARCHAR2 1 NO ACCRUE SENIORITY INDICATOR: This indicator will denote
whether seniority hours are accrued against the
position.
NBBPOSN_JOBP_CODE VARCHAR2 5 YES JOB PROGRESSION CODE: Job Progression code associated
with the position.
NBBPOSN_BPRO_CODE VARCHAR2 6 YES BPRO CODE: Budget Profile code.
NBBPOSN_BUDGET_TYPE VARCHAR2 1 NO BUDGET TYPE: Expected status of funding sources for
position.


And this one that defines Position Classes:

TABLE: NTRPCLS

CREATOR: POSNCTL

Position Classification Rule Table

===================================================================================================================
Column Name Type Width Scale Nulls Column Comments
=============================== ========= ===== ===== ======= =====================================================
NTRPCLS_CODE VARCHAR2 5 NO POSITION CLASS CODE: A code which identifies a
Position Class which can then be assigned to a
Position.
NTRPCLS_DESC VARCHAR2 30 NO POSITION CLASS DESCRIPTION: The description or title
for the Position Class.
NTRPCLS_ECLS_CODE VARCHAR2 2 NO EMPLOYEE CLASS CODE: A code which identifies the
Employee Class that is associated with this Position
Class, and will therefore default to the Position Form
(NBAPOSN) when the Position Class is entered.
NTRPCLS_ESKL_CODE VARCHAR2 2 YES EEO SKILL CODE: The EEO Skill Code as defined by the
U.S. Federal Goverment, for use by institutions of
higher education, for EEO reporting
NTRPCLS_EFUN_CODE VARCHAR2 2 YES EEO FUNCTION CODE: For goverment agencies only. The
EEO Function Code as defined by the U.S. Federal
Goverment, for use with EEO reporting.
NTRPCLS_EXEMPT_IND VARCHAR2 1 NO EXEMPTION INDICATOR: Indicates if employees assigned
to positions in this Position Class will be exempt or
non-exempt.
NTRPCLS_TABLE VARCHAR2 2 NO SALARY TABLE: The Salary Table as defined on the
Salary/Rate Rule Form (NTRSALA) that will be
associated with positions assigned to this Position
Class and will default to the Position Form (NBAPOSN).
NTRPCLS_GRADE VARCHAR2 5 YES SALARY GRADE: The Salary Grade, as defined on the
Salary/Rate Rule Form (NTRSALA), that is associated
with positions in this Position Class and will default
to the Position Form (NBAPOSN) when this Position
Class is entered
NTRPCLS_STEP NUMBER 3 YES SALARY STEP: The Salary Step associated with the
Salary Grade as defined on the Salary/Rate Rule Form
(NTRSALA) that will be associated with positions
assigned to this Position Class.
NTRPCLS_ACTIVITY_DATE DATE 7 NO ACTIVITY DATE: Date when the record was inserted or
last modified.
NTRPCLS_SGRP_CODE VARCHAR2 6 NO SGRP_CODE: The salary group code.
NTRPCLS_PGRP_CODE VARCHAR2 4 YES PGRP_CODE: The position group code.
NTRPCLS_CHANGE_DATE_TIME DATE 7 NO CHANGE DATE TIME: Date and Time this record was
changed.
NTRPCLS_CALIF_TYPE VARCHAR2 1 YES CALIFORNIA TYPE: California Pension position class
type.
NTRPCLS_STRS_FTE NUMBER 6 2 YES STRS FTE: California STRS FTE hours.
NTRPCLS_PERS_HOURS NUMBER 6 2 YES PERS HOURS: California PERS Work Schedule hours.
NTRPCLS_STRS_BASE_HRS NUMBER 7 2 YES STRS part-time employee base hours
NTRPCLS_BARG_CODE VARCHAR2 2 YES BARGAINING UNIT CODE: Code associated with this
Position Class.
NTRPCLS_PROBATION_UNITS NUMBER 3 YES PROBATION UNITS: Identifies the number of Probationary
Units.





Date: 18-DEC-2008 Page: 126

===================================================================================================================
Column Name Type Width Scale Nulls Column Comments
=============================== ========= ===== ===== ======= =====================================================
NTRPCLS_ACCRUE_SENIORITY_IND VARCHAR2 1 NO ACCRUE SENIORITY INDICATOR:This indicator will denote
whether seniority hours are accrued against the
position class code.
Go to Top of Page

Shego
Starting Member

7 Posts

Posted - 2013-05-24 : 14:25:26
And the script I'm running currently looks like this:
select spriden_id
, nbrjobs_desc
, min(nbbposn_grade),min(nbbposn_step)
, min(ntrsala_amount)
, max(nbbposn_grade),max(nbbposn_step)
, max(ntrsala_amount)
, nbbposn_grade, nbbposn_step
, ntrsala_amount
from spriden,nbrjobs,ntrsala,ntrpcls,nbbposn

where spriden_pidm=nbrjobs_pidm
and nbrjobs_sgrp_code=ntrsala_sgrp_code
and ntrpcls_code=nbbposn_pcls_code
and nbbposn_posn=nbrjobs_posn
and ntrpcls_table=nbbposn_table
and ntrpcls_grade=nbbposn_grade
and ntrpcls_step=nbbposn_step
and nbbposn_table=ntrsala_table
and nbbposn_grade=ntrsala_grade
and nbbposn_step=ntrsala_step

group by spriden_id,nbrjobs_desc,nbbposn_grade,nbbposn_step,ntrsala_amount
order by spriden_id


I've tried combining the tables through inner joins, but this 1:1 connection format actually works better.
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-24 : 14:57:43
Making things even more difficult to find out the desired information we're looking for. Let me make it easier for you ..

Provide sample data for the folowing table's fields (for each table seperately) in consumable format. By consumable we means it should be in the form of "insert statments" so that we can quickly create temporary tables and use the insert statments to add the data into it.

person: person_pidm, person_name
job: job_pidm, job_description, job_sgrp_code
salary: salary_sgrp_code, salary_amount, salary_amount

Provide a full set of sample data for an employee (representing the real data as that of what you have in tables) e.g.
Take the example of "JohnSmith", provide all records for this employee in Person table (logically it should be one record) and corresponding all records in Job table (for John Smith) and all records in Salary table (for John Smith).

You can get this sample data using the following query

SELECT person_pidm, person_name FROM Person WHERE person_pidm=?? and Person_Name='John Smith'
SELECT job_pidm, job_description, job_sgrp_code FROM Job WHERE job_pidm=?? -- Value for ?? should be the PIDM of the Person table which belongs to John Smith
SELECT salary_sgrp_code, salary_amount, salary_amount FROM Job WHERE salary_sgrp_code IN (??) --value for ?? should be the srgp_code that of the Job table. There could be one/multiple codes, if multiple pass in all the codes comma separated to the IN clause


Provide the result of the above three queries in consumable format. And then also suggest the desired ouput you think it should have.

Cheers
MIK
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-05-24 : 21:31:46
Looks like the joins might be off. As others have suggested, sample data and expected output would help us point you in the right direction.

My best guess would be that you're looking for something like this

select a.spriden_id
,b.nbrjobs_desc
,f.min_nbbposn_grade
,f.min_nbbposn_step
,f.min_ntrsala_amount
,f.max_nbbposn_grade
,f.max_nbbposn_step
,f.max_ntrsala_amount
,d.nbbposn_grade
,d.nbbposn_step
,c.ntrsala_amount
from spriden as a
inner join nbrjobs as b
on b.nbrjobs_pidm=a.spriden_pidm
inner join ntrsala as c
on c.ntrsala_table=b.nbrjobs_sal_table
and c.ntrsala_grade=b.nbrjobs_sal_grade
and c.ntrsala_step=b.nbrjobs_sal_step
and c.ntrsala_sgrp_code=b.nbrjobs_sgrp_code
inner join nbbposn as d
on d.nbbposn_posn=c.nbrjobs_posn
and d.nbbposn_table=c.ntrsala_table
and d.nbbposn_grade=c.ntrsala_grade
and d.nbbposn_step=c.ntrsala_step
and d.ntrsala_sgrp_code=c.nbrjobs_sgrp_code
inner join ntrpcls as e
on e.ntrpcls_code=d.nbbposn_pcls_code
and e.ntrpcls_table=d.nbbposn_table
and e.ntrpcls_grade=d.nbbposn_grade
and e.ntrpcls_step=d.nbbposn_step
and e.ntrpcls_sgrp_code=d.nbbposn_sgrp_code
and e.ntrpcls_pgrp_code=d.nbbposn_pgrp_code
inner join (select a.spriden_id
from spriden as a
,min(d.nbbposn_grade) as min_nbbposn_grade
,min(d.nbbposn_step) as min_nbbposn_step
,min(c.ntrsala_amount) as min_ntrsala_amount
,max(d.nbbposn_grade) as max_nbbposn_grade
,max(d.nbbposn_step) as max_nbbposn_step
,max(c.ntrsala_amount) as max_ntrsala_amount
inner join nbrjobs as b
on b.nbrjobs_pidm=a.spriden_pidm
inner join ntrsala as c
on c.ntrsala_table=b.nbrjobs_sal_table
and c.ntrsala_grade=b.nbrjobs_sal_grade
and c.ntrsala_step=b.nbrjobs_sal_step
and c.ntrsala_sgrp_code=b.nbrjobs_sgrp_code
inner join nbbposn as d
on d.nbbposn_posn=c.nbrjobs_posn
and d.nbbposn_table=c.ntrsala_table
and d.nbbposn_grade=c.ntrsala_grade
and d.nbbposn_step=c.ntrsala_step
and d.ntrsala_sgrp_code=c.nbrjobs_sgrp_code
group by a.spriden_id
) as f
on f.spriden_id=a.spriden_id


Looking at description of field NBRJOBS_USER_ID, it looks like you're using Oracle db. This forum is for MSSQL db, so my suggestion may not work at all for you.
Go to Top of Page
   

- Advertisement -