Q.STAFF | NAME | DEPT | JOB |
--------+-----------+--------+-------|
| P. | 84 | P. |
|
NAME JOB --------- ----- QUILL MGR DAVIS SALES EDWARDS SALES GAFNEY CLERK
Q.STAFF | ID | NAME | DEPT | YEARS | SALARY |
--------+--------+-----------+--------+--------+------------|
| P. | P. | 51 | P. | P. |
|
ID NAME YEARS SALARY ------ --------- ------ ---------- 140 FRAYE 6 21150.00 150 WILLIAMS 6 19456.50 220 SMITH 7 17654.50 230 LUNDQUIST 3 13369.80 250 WHEELER 6 14460.00
Q.STAFF | ID | NAME | DEPT | JOB | YEARS | SALARY | COMM | --------+----+------+------+-----+-------+--------+------| P. | | | 51 | | | | | |
ID NAME DEPT JOB YEARS SALARY COMM ------ --------- ------ ----- ------ ---------- ---------- 140 FRAYE 51 MGR 6 21150.00 - 150 WILLIAMS 51 SALES 6 19456.50 637.65 220 SMITH 51 SALES 7 17654.50 992.80 230 LUNDQUIST 51 CLERK 3 13369.80 189.65 250 WHEELER 51 CLERK 6 14460.00 513.30
Q.STAFF | ID | NAME | DEPT | YEARS |
--------+--------+-----------+--------+--------|
| P. | P. | P. | P.NULL |
|
ID NAME DEPT YEARS
------ --------- ------ ------
60 QUIGLEY 38 -
80 JAMES 20 -
120 NAUGHTON 38 -
200 SCOUTTEN 42 -
Q.STAFF | ID | NAME | JOB | YEARS |
--------+--------+-----------+-------+--------|
| P. | P. | P. | P.>=10 |
|
ID NAME JOB YEARS
------ --------- ----- ------
50 HANES MGR 10
210 LU MGR 10
260 JONES MGR 12
290 QUILL MGR 10
310 GRAHAM SALES 13
Q.STAFF | NAME | JOB | COMM |
--------+-----------+-------+------------|
| P. | MGR | P. ¬NULL |
|
NAME COMM --------- ----------
Q.STAFF | NAME | DEPT | JOB | YEARS |
--------+-----------+--------+-------+--------|
| P. AO. | P. | CLERK | P. |
|
NAME DEPT YEARS --------- ------ ------ ABRAHAMS 38 3 BURKE 66 1 GAFNEY 84 5 JAMES 20 - KERMISCH 15 4 LUNDQUIST 51 3 NAUGHTON 38 - NGAN 15 5 SCOUTTEN 42 - SNEIDER 20 8 WHEELER 51 6 YAMAGUCHI 42 6
Q.STAFF | NAME | DEPT | JOB | YEARS |
--------+-----------+----------------+-------+---------------|
| P. | P. AO(1). | CLERK | P. AO(2). |
|
NAME DEPT YEARS --------- ------ ------ KERMISCH 15 4 NGAN 15 5 SNEIDER 20 8 JAMES 20 - ABRAHAMS 38 3 NAUGHTON 38 - YAMAGUCHI 42 6 SCOUTTEN 42 - LUNDQUIST 51 3 WHEELER 51 6 BURKE 66 1 GAFNEY 84 5
Q.STAFF | ID | NAME | JOB | YEARS |
--------+--------+-----------+-------+--------|
| P. | P. | CLERK | P. DO. |
|
ID NAME YEARS
------ --------- ------
80 JAMES -
200 SCOUTTEN -
120 NAUGHTON -
190 SNEIDER 8
130 YAMAGUCHI 6
250 WHEELER 6
350 GAFNEY 5
110 NGAN 5
170 KERMISCH 4
230 LUNDQUIST 3
180 ABRAHAMS 3
330 BURKE 1
Notes:
Q.STAFF | ID | NAME | DEPT | JOB | YEARS |
--------+------+---------+---------------+-------+--------------|
| P. | P. | P. AO(2). | CLERK | P. DO(1). |
|
ID NAME DEPT YEARS
------ --------- ------ ------
80 JAMES 20 -
120 NAUGHTON 38 -
200 SCOUTTEN 42 -
190 SNEIDER 20 8
130 YAMAGUCHI 42 6
250 WHEELER 51 6
110 NGAN 15 5
350 GAFNEY 84 5
170 KERMISCH 15 4
180 ABRAHAMS 38 3
230 LUNDQUIST 51 3
330 BURKE 66 1
Q.STAFF | NAME |
--------+---------------------|
| P. LIKE '@(#)' |
|
NAME --------- KOONITZ PLOTZ GONZALES
Q.STAFF | NAME |
--------+---------------------|
| P. LIKE 'S%' |
|
NAME --------- SANDERS SNEIDER SCOUTTEN SMITH
Q.STAFF | NAME |
--------+---------------------|
| P. LIKE '_ _A%' |
|
NAME --------- NGAN FRAYE LEA GRAHAM
Q.STAFF | NAME | SALARY | COMM |
--------+-----------+------------+------------|
| P. | P. _S | P. _C |
| CONDITIONS |
|--------------------------|
| _S > 18000 OR _C > 1000 |
|
NAME SALARY COMM --------- ---------- ---------- SANDERS 18357.50 - PERNAL 18171.25 612.45 O'BRIEN 18006.00 846.55 HANES 20659.80 - ROTHMAN 16502.83 1152.00 KOONITZ 18001.75 1386.70 PLOTZ 18352.80 - FRAYE 21150.00 - WILLIAMS 19456.50 637.65 MOLINARE 22959.20 - LU 20010.00 - DANIELS 19260.25 - JONES 21234.00 - LEA 18555.50 - WILSON 18674.50 811.50 QUILL 19818.00 - GRAHAM 21000.00 200.30 EDWARDS 17844.00 1285.00
Q.STAFF | NAME | YEARS | COMM |
--------+-----------+--------+------------|
| P. | P. _Y | P. _C |
| CONDITIONS |
|--------------------------|
| _Y = NULL OR _C = NULL |
|
NAME YEARS COMM --------- ------ ---------- SANDERS 7 - MARENGHI 5 - HANES 10 - QUIGLEY - 650.25 JAMES - 128.20 PLOTZ 7 - NAUGHTON - 180.00 FRAYE 6 - MOLINARE 7 - SCOUTTEN - 84.20 LU 10 - DANIELS 5 - JONES 12 - LEA 9 - QUILL 10 -
Q.STAFF | ID | NAME | SALARY |
--------+--------+-----------+------------|
| P. | P. | P. _S |
Exclusive BETWEEN: Inclusive BETWEEN:
| CONDITIONS | | CONDITIONS |
|---------------------------| |-----------------------------|
| _S > 20000 AND _S < 21000 | | _S >= 20000 AND _S <= 21000 |
OR OR
| CONDITIONS | | CONDITIONS |
|---------------------------| |-----------------------------|
| _S BT 20001 AND 20999 | | _S BETWEEN 20000 AND 21000 |
|
ID NAME SALARY ID NAME SALARY ----- --------- ---------- ----- --------- ---------- 50 HANES 20659.80 50 HANES 20659.80 210 LU 20010.00 210 LU 20010.00
Q.STAFF | NAME | JOB | YEARS | SALARY |
--------+-----------+-----------------+--------+------------|
| P. | P. =MGR | P. <10 | P. >=20000 |
|
NAME JOB YEARS SALARY --------- ----- ------ ---------- FRAYE MGR 6 21150.00 MOLINARE MGR 7 22959.20
Q.STAFF | NAME | YEARS | SALARY | COMM |
--------+-----------+--------+------------+------------|
| P. | P. <10 | P. _S | P. _C |
| CONDITIONS |
|--------------------------|
| _S > 20000 OR _C > 1000 |
|
NAME YEARS SALARY COMM --------- ------ ---------- ---------- ROTHMAN 7 16502.83 1152.00 KOONITZ 6 18001.75 1386.70 FRAYE 6 21150.00 - MOLINARE 7 22959.20 - EDWARDS 7 17844.00 1285.00
Q.STAFF | NAME | ID | JOB | SALARY | COMM | |
--------+------+-----+-------+---------+-------+----------|
| P. | P. | SALES | P. _S | P. _C | P. _S+_C |
|
NAME ID SALARY COMM EXPRESSION 1 --------- ------ ---------- ---------- ------------ PERNAL 20 18171.25 612.45 18783.70 O'BRIEN 40 18006.00 846.55 18852.55 QUIGLEY 60 16808.30 650.25 17458.55 ROTHMAN 70 16502.83 1152.00 17654.83 KOONITZ 90 18001.75 1386.70 19388.45 WILLIAMS 150 19456.50 637.65 20094.15 SMITH 220 17654.50 992.80 18647.30 WILSON 280 18674.50 811.50 19486.00 DAVIS 300 15454.50 806.10 16260.60 GRAHAM 310 21000.00 200.30 21200.30 GONZALES 320 16858.20 844.00 17702.20 EDWARDS 340 17844.00 1285.00 19129.00
Q.STAFF | NAME | ID | JOB | SALARY | COMM | |
--------+--------+------+-------+----------+---------+-----------|
| P. | P. | SALES | P. _S | P. _C | P. _S+_C |
| CONDITIONS |
|--------------------------|
| (_S + _C) < 17500 |
|
NAME ID SALARY COMM EXPRESSION 1 --------- ------ ---------- ---------- ------------ QUIGLEY 60 16808.30 650.25 17458.55 DAVIS 300 15454.50 806.10 16260.60
Q.STAFF | NAME | JOB | SALARY | COMM | |
--------+-------+-------+---------+-------+---------------------|
| P. | SALES | _S | _C | P. DO. 100*(_C/_S) |
|
NAME EXPRESSION 1 --------- ------------------ KOONITZ 7.70313900 EDWARDS 7.20130000 ROTHMAN 6.98062000 SMITH 5.62349500 DAVIS 5.21595600 GONZALES 5.00646500 O'BRIEN 4.70148800 WILSON 4.34549700 QUIGLEY 3.86862400 PERNAL 3.37043400 WILLIAMS 3.27731000 GRAHAM 0.95380900
You might try using this instead:
Q.STAFF | NAME | JOB | SALARY | COMM | |
--------+------+-------+----------+--------+---------------------|
| P. | SALES | _S | _C | P. DO. (100*_C)/_S |
|
NAME EXPRESSION 1 --------- ------------------ EDWARDS 7 KOONITZ 7 ROTHMAN 6 GONZALES 5 DAVIS 5 SMITH 5 O'BRIEN 4 WILSON 4 PERNAL 3 QUIGLEY 3 WILLIAMS 3 GRAHAM 0
Note what happens to the precision of the ratio that you calculated. This truncation can also affect the order into which your rows are sorted.
Q.STAFF | NAME | JOB | SALARY | COMM | |
--------+------+-------+--------+------+-------------------------|
| P. | SALES | _S | _C | P. DO. 100*(_C/(_S+_C)) |
|
NAME EXPRESSION 1 --------- ------------------ KOONITZ 7.15219600 EDWARDS 6.71754900 ROTHMAN 6.52512600 SMITH 5.32409500 DAVIS 4.95738100 GONZALES 4.76776800 O'BRIEN 4.49037300 WILSON 4.16452800 QUIGLEY 3.72453600 PERNAL 3.26053900 WILLIAMS 3.17331100 GRAHAM 0.94479700
Q.STAFF | ID | NAME |
--------+--------+-----------|
| _MID | _MNM |
Q.ORG | DEPTNAME | MANAGER | LOCATION | |
------+--------------+---------+-------------+--------------|
| P. | _MID | P. | P. _MNM |
|
DEPTNAME LOCATION NAME -------------- ------------- --------- MID ATLANTIC WASHINGTON SANDERS SOUTH ATLANTIC ATLANTA MARENGHI NEW ENGLAND BOSTON HANES GREAT LAKES CHICAGO PLOTZ PLAINS DALLAS FRAYE HEAD OFFICE NEW YORK MOLINARE PACIFIC SAN FRANCISCO LEA MOUNTAIN DENVER QUILL
Q.STAFF | ID | NAME |
--------+--------+-----------|
| _MID | _MNM |
Q.ORG | DEPTNAME | MANAGER | DIVISION | LOCATION | |
------+------------+---------+----------+-----------+----------|
| P. | _MID | EASTERN | P. | P. _MNM |
|
DEPTNAME LOCATION NAME -------------- ------------- --------- MID ATLANTIC WASHINGTON SANDERS SOUTH ATLANTIC ATLANTA MARENGHI NEW ENGLAND BOSTON HANES
Q.STAFF | ID | NAME | YEARS |
--------+--------+-----------+--------|
| _MID | _MNM | >=10 |
Q.ORG | DEPTNAME | MANAGER | DIVISION | LOCATION | |
------+-----------+---------+----------+-----------+-----------|
| P. | _MID | EASTERN | P. | P. _MNM |
|
DEPTNAME LOCATION NAME -------------- ------------- --------- NEW ENGLAND BOSTON HANES
DISPLAY Q.STAFF SAVE DATA AS MYTABLE
MYTABLE | NAME | DEPT | JOB | YEARS | SALARY |
--------+-----------+--------+-------+--------+------------|
| U.RAMOTH | 66 | MGR | U. 7 | U.18238.50 |
|
After you have run the previous step:
MYTABLE | ID | NAME | DEPT | JOB | YEARS | SALARY | COMM | --------+-----+---------+------+-----+-------+--------+------| P. | | RAMOTH | | | | | | |
ID NAME DEPT JOB YEARS SALARY COMM ------ --------- ------ ----- ------ ---------- ---------- 270 RAMOTH 66 MGR 7 18238.50 -
MYTABLE | JOB | YEARS | SALARY | SALARY |
--------+-------+--------+------------+-----------------|
| CLERK | > 5 | _S | U. _S * 1.1 |
|
To retrieve rows to audit your result:
MYTABLE | ID | NAME | DEPT | JOB | YEARS | SALARY | COMM | --------+----+------+------+-------+-------+--------+------| P. | | | | CLERK | | | | |
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ ---------- ----------
80 JAMES 20 CLERK - 13504.60 128.20
110 NGAN 15 CLERK 5 12508.20 206.60
120 NAUGHTON 38 CLERK - 12954.75 180.00
130 YAMAGUCHI 42 CLERK 6 11556.49 75.60
170 KERMISCH 15 CLERK 4 12258.50 110.10
180 ABRAHAMS 38 CLERK 3 12009.75 236.50
190 SNEIDER 20 CLERK 8 15678.02 126.50
200 SCOUTTEN 42 CLERK - 11508.60 84.20
230 LUNDQUIST 51 CLERK 3 13369.80 189.65
250 WHEELER 51 CLERK 6 15906.00 513.30
330 BURKE 66 CLERK 1 10988.00 55.50
350 GAFNEY 84 CLERK 5 13030.50 188.00
MYTABLE | ID | NAME | DEPT | JOB | YEARS | SALARY | COMM | --------+-----+--------+------+-------+-------+--------+------| I. | 275 | ROGERS | 66 | SALES | NULL | 14000 | NULL | |
Instead of 14000 you can use 14000.00, but not 14,000 or 14,000.00 (commas are not valid numeric input characters).
You can retrieve the row with this query:
MYTABLE | ID | NAME | DEPT | JOB | YEARS | SALARY | COMM | --------+-----+------+------+------+-------+--------+------| P. | 275 | | | | | | | |
ID NAME DEPT JOB YEARS SALARY COMM ------ --------- ------ ----- ------ ---------- ---------- 275 ROGERS 66 SALES - 14000.00 -
MYTABLE | DEPT | JOB | --------+--------+-------| D. | 66 | SALES | |