Home » SQL & PL/SQL » SQL & PL/SQL » Check Departmet Code
Check Departmet Code [message #220389] Tue, 20 February 2007 09:00 Go to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi Guys,

following is my query
SELECT   BOS.OPERATION_SEQ_NUM OPERATION_SEQUENCE,
         DEPARTMENT_CLASS_CODE,
         MSI.SEGMENT1
FROM     	 MTL_SYSTEM_ITEMS MSI,
	         BOM_OPERATIONAL_ROUTINGS BORT,
	         BOM_OPERATION_SEQUENCES BOS,
	         BOM_DEPARTMENTS BD,
	         BOM_STANDARD_OPERATIONS BSD
	WHERE    MSI.INVENTORY_ITEM_ID = BORT.ASSEMBLY_ITEM_ID
	         AND MSI.ORGANIZATION_ID   = BORT.ORGANIZATION_ID
	         AND BORT.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
	         AND BOS.DEPARTMENT_ID = BD.DEPARTMENT_ID
	         AND BD.DEPARTMENT_ID = BSD.DEPARTMENT_ID
	         AND BOS.STANDARD_OPERATION_ID = BSD.STANDARD_OPERATION_ID
--	         AND BORT.ALTERNATE_ROUTING_DESIGNATOR ='MODEL'
	         AND MSI.INVENTORY_ITEM_ID =91202 
	         AND MSI.ORGANIZATION_ID = 1332 
	ORDER BY 1;

[B]
The Output......
[/B]
OPSEQ	DEPT	SEGMENT1
10	PROC	124717
20	VIM	124717
30	TRP	124717
40	ESR	124717
50	TRP	124717
125	EFP	124717
130	EFP	124717
140	EFP	124717
9999	PROC	124717

9 rows selected.


the above is one set for a perticular item. my question is
how to get the whole 9 rows value if there is any department_code from VIM,VIM1,VIM2.

meaning a result set of above query, which is 9 rows out of that 9 rows if any row has department_code VIM,VIM1 or VIM2 select all 9 rows if not than dont.

i can check via PL/SQL block putting if condition but how to do this with only SQL.

I am on 8i.

Thanks in advance
Re: Check Departmet Code [message #220398 is a reply to message #220389] Tue, 20 February 2007 09:42 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
I have a thought but it could be quite expansive.
Add an EXISTS condition that contains:
- the same query
- link it to the parent one with the item id
- add: AND BD2.DEPARTMENT_CLASS_CODE in(VIM,VIM1,VIM2)
Re: Check Departmet Code [message #220400 is a reply to message #220398] Tue, 20 February 2007 09:51 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi

thanks for reply...but i already tried this



SELECT   BOS.OPERATION_SEQ_NUM OPERATION_SEQUENCE, 
BD.DEPARTMENT_CODE DEPARTMENT_CODE, 
MSI.SEGMENT1 ROUTING_ITEM 
FROM    	 MTL_SYSTEM_ITEMS MSI, 
BOM_OPERATIONAL_ROUTINGS BORT, 
BOM_OPERATION_SEQUENCES BOS, 
BOM_DEPARTMENTS BD, 
BOM_STANDARD_OPERATIONS BSD 
WHERE    MSI.INVENTORY_ITEM_ID = BORT.ASSEMBLY_ITEM_ID 
AND MSI.ORGANIZATION_ID   = BORT.ORGANIZATION_ID 
AND BORT.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID 
AND BOS.DEPARTMENT_ID = BD.DEPARTMENT_ID 
AND BD.DEPARTMENT_ID = BSD.DEPARTMENT_ID 
AND BOS.STANDARD_OPERATION_ID = BSD.STANDARD_OPERATION_ID 
AND MSI.INVENTORY_ITEM_ID =91202 
AND exists (Select 'x' From BOM_DEPARTMENTS BD2
                 Where BD2.DEPARTMENT_CODE = BD.DEPARTMENT_CODE
                   And BD2.DEPARTMENT_CODE in ('VIM','VIM1','VIM2'))
AND MSI.ORGANIZATION_ID = 1332 
ORDER BY 1 


but this one gives only rows which has VIM,VIM1,VIM2

i need some thing like

if department_code IN (VIM,VIM1,VIM2) then

all 9 rows

else

none

end if


i am on 8i

[Updated on: Tue, 20 February 2007 09:51]

Report message to a moderator

Re: Check Departmet Code [message #220401 is a reply to message #220400] Tue, 20 February 2007 09:54 Go to previous message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
you do not link them with the right ID. You used the department id instead of the item id.
Previous Topic: Find results NOT in subquery
Next Topic: Basic update question
Goto Forum:
  


Current Time: Fri Dec 09 13:59:12 CST 2016

Total time taken to generate the page: 0.11236 seconds