changes not made for company 50

[grapes12]

Here is original request from senior:
1. Create SQL statement that joins oprfile, cpy_file and div_file tables for oracle user MAC on sun5.

For each record in oprfile (excluding all records where of_coy = '50' and of_dept = '999')

2.1. Extract cpy_snam1 column from cpy_file table, concatenated with "/", then concatenated with di_short_name column from div_file table, then
concatenated with SPACE, and finally concatenated with of_name column from oprfile table.

here is my code:

select o.of_opid, c.cpy_snam1|| '/' ||d.di_short_name||' '||o.of_name
from mac.cpy_file c, mac.div_file d, mac.oprfile o
where o.OF_COY = c.CPY_CODE
and o.OF_COY = d.di_coy
and o.OF_DIV = d.di_code
and o.of_coy != '50'
and o.of_dept not in '999'
group by o.of_opid,c.CPY_SNAM1,d.DI_SHORT_NAME,o.OF_NAME;

senior says i need to make changes for company 50 as well
what is wrong with this script?

 

[Dagon]

I'm assuming that the problem is the situation where, say, of_coy = 50 and of_dept = '998'. Because you have a blanket restriction that of_coy != 50, then that record will be excluded as well as the of_dept= '999' record.

You could write this as:

select o.of_opid, c.cpy_snam1|| '/' ||d.di_short_name||' '||o.of_name
from mac.cpy_file c, mac.div_file d, mac.oprfile o
where o.OF_COY = c.CPY_CODE
and o.OF_COY = d.di_coy
and o.OF_DIV = d.di_code
and NOT (o.of_coy = '50' and o.of_dept = '999')
group by o.of_opid,c.CPY_SNAM1,d.DI_SHORT_NAME,o.OF_NAME;

or

select o.of_opid, c.cpy_snam1|| '/' ||d.di_short_name||' '||o.of_name
from mac.cpy_file c, mac.div_file d, mac.oprfile o
where o.OF_COY = c.CPY_CODE
and o.OF_COY = d.di_coy
and o.OF_DIV = d.di_code
and (o.of_coy != '50' or o.of_dept != '999')
group by o.of_opid,c.CPY_SNAM1,d.DI_SHORT_NAME,o.OF_NAME;

For Oracle-related work, contact me through Linked-In.

 

[grapes12]

So busy, thanks for the reply.
We need company of_coy 50 , but not of_dept '999'

 

[carp]

If you need of_coy 50, then change the filter to include it; change "!= 50" to "= 50". Or, if you need all of_coy values (not just 50), eliminate of_coy "!= 50" altogether.

 

[grapes12]

so will the code look like this:

select o.of_opid, c.cpy_snam1|| '/' ||d.di_short_name||' '||o.of_name
from mac.cpy_file c, mac.div_file d, mac.oprfile o
where o.OF_COY = c.CPY_CODE
and o.OF_COY = d.di_coy
and o.OF_DIV = d.di_code
and o.of_coy = '50'
and not o.of_dept '999'
group by o.of_opid,c.CPY_SNAM1,d.DI_SHORT_NAME,o.OF_NAME;

we want company 50, but not users of_dept 999 belonging to company 50

 

[carp]

ggoliath -
It will look more like this:

select o.of_opid, c.cpy_snam1|| '/' ||d.di_short_name||' '||o.of_name
from mac.cpy_file c, mac.div_file d, mac.oprfile o
where o.OF_COY = c.CPY_CODE
and o.OF_COY = d.di_coy
and o.OF_DIV = d.di_code
and o.of_coy = '50'
and o.of_dept != '999'
group by o.of_opid,c.CPY_SNAM1,d.DI_SHORT_NAME,o.OF_NAME;

 

[grapes12]

sorry...that's not correct

select o.of_opid, c.cpy_snam1|| '/' ||d.di_short_name||' '||o.of_name
from mac.cpy_file c, mac.div_file d, mac.oprfile o
where o.OF_COY = c.CPY_CODE
and o.OF_COY = d.di_coy
and o.OF_DIV = d.di_code
and not o.of_dept = '999'
group by o.of_opid,c.CPY_SNAM1,d.DI_SHORT_NAME,o.OF_NAME;

is this what the code should look like...

 

[Dagon]

If all you want to do is exclude of_dept = '999', then it looks reasonable, although most people would use o.of_dept <> '999' or o.of_dept != '999'.

For Oracle-related work, contact me through Linked-In.

 

[Beilstwh]

I might be dense but the requirements are

excluding all records where of_coy = '50' and of_dept = '999')

Which implies that of_coy can be anything including 50, just not the combination. in which case, try.

select o.of_opid, c.cpy_snam1|| '/' ||d.di_short_name||' '||o.of_name
from mac.cpy_file c, mac.div_file d, mac.oprfile o
where o.OF_COY = c.CPY_CODE
and o.OF_COY = d.di_coy
and o.OF_DIV = d.di_code
and NOT (o.of_coy = '50' and o.of_dept = '999')
group by o.of_opid,c.CPY_SNAM1,d.DI_SHORT_NAME,o.OF_NAME;

which Dagon suggested.

Bill
Lead Application Developer
New York State, USA