Home » SQL & PL/SQL » SQL & PL/SQL » Problem while joining (oracle 9i)
Problem while joining [message #330011] Fri, 27 June 2008 05:42 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I am finding problem while joinging for the below tables.Need your help!

TABLE A
__________

 CAPTION_CDE            
 FIELD_SEQ              
 FIELD_CDE              
 FIELD_TXT              
 LINKED_FIELD_CDE  

TABLE B
_________

CODE_VALUE
DESCRIPTION

TABLE C:
__________

QUEUE
LOCATION_CD
INS_TMS

TABLE D
____
LOCATION_CD
LOC_NAME




SELECT C.LOCATION_CD,C.QUEUE,B.CODE_VALUE COUNT(C.INS_TMS) 
FROM TABLE A ,TABLE B , TABLE C, TABLE D				
-- table c contains null values in Location code for queue names
WHERE C.LOCATION_CD  = D.LOCATION_CD(+) 
AND C.QUEUE,1,4) = A.FIELD_CDE
AND (( p_sub_process_cd IS NULL AND A.LINKED_FIELD_CDE = p_process_cd) 
  OR ( p_sub_process_cd IS NOT NULL AND A.LINKED_FIELD_CDE = p_sub_process_cd))
GROUP BY C.LOCATION_ID,C.QUEUE,B.CODE_VALUE;


Finding difficulty in joining.Need your suggesion.I need to join Table A & Table B.Its obvious that the table is not in normalised form and I dont have priviledge to make any changes to the table structure.





FIELD_CDE column contains values for process code, sub process code and queue code

LINKED_FIELD_CDE: may be null /application /subapplication code

If Field_cde contains process code then LINKED_FIELD_CDE contains NULL
If Field_cde contains sub process code then LINKED_FIELD_CDE contains process code
If Field_cde contains queue code then LINKED_FIELD_CDE contains process/sub process code



And CODE_VALUE (TABLE B) contains queue code only
DESCRIPTION: gives description of queues

I am using this tables in a procedure where process name/subprocess name/queue name is passed as a parameter.

Pass process code if there is no sub process else pass sub process code


Another Table C is there which contains the Queue code, (Field name: Queue)


Requirement: Based on application/sub application return queue count with description.

[Updated on: Fri, 27 June 2008 06:00] by Moderator

Report message to a moderator

Re: Problem while joining [message #330019 is a reply to message #330011] Fri, 27 June 2008 06:10 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
YOu are using 4 tables and but the join conditions are only 2.
Why?
Re: Problem while joining [message #330020 is a reply to message #330019] Fri, 27 June 2008 06:16 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thts what my question i. How can I join them?

A.FIELD_TXT=B.CODE_VALUE

-----Here Field text contains three different values in a single column.App code/sub app code and queue code while CODE_VALUE contains only queue code


If I use then it will give wrong data.

Thanks,
Oli




[Updated on: Fri, 27 June 2008 06:16]

Report message to a moderator

Re: Problem while joining [message #330046 is a reply to message #330011] Fri, 27 June 2008 07:42 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Olivia wrote on Fri, 27 June 2008 06:42


[code]

SELECT C.LOCATION_CD,C.QUEUE,B.CODE_VALUE COUNT(C.INS_TMS)
FROM TABLE A ,TABLE B , TABLE C, TABLE D
-- table c contains null values in Location code for queue names
WHERE C.LOCATION_CD = D.LOCATION_CD(+)
AND C.QUEUE,1,4) = A.FIELD_CDE



This will produce a syntax error.

Do you actually sit there and retype your code in Orafaq? What is so difficult to cut and paste? I am wondering if many people are using their phones to post due to the inordinate amount of invalid syntax in what people say are code they execute. I would imagine highlighting, cutting and pasting is not easy to do on a phone.

[Updated on: Fri, 27 June 2008 07:44]

Report message to a moderator

Re: Problem while joining [message #330047 is a reply to message #330046] Fri, 27 June 2008 07:50 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Ooops! Thanks for pointing out the mistake

SELECT C.LOCATION_CD,C.QUEUE,B.CODE_VALUE COUNT(C.INS_TMS) 
FROM TABLE A ,TABLE B , TABLE C, TABLE D 
-- table c contains null values in Location code for queue names
WHERE C.LOCATION_CD = D.LOCATION_CD(+) 
AND SUBSTR(C.QUEUE,1,4) = A.FIELD_CDE



Still tables are not joined!
Re: Problem while joining [message #330116 is a reply to message #330047] Fri, 27 June 2008 10:52 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Does anyone have answer to this? Please kindly let me know if it can be made possible.Please make a note that I cant change table structure.I have no priviledge to do that.

Thanks,
Oli
Previous Topic: calling values from a parameter
Next Topic: append description
Goto Forum:
  


Current Time: Wed Dec 07 04:53:39 CST 2016

Total time taken to generate the page: 0.11430 seconds