How to eliminate OR clause when joining tables?


 
Thread Tools Search this Thread
Top Forums Programming How to eliminate OR clause when joining tables?
# 1  
Old 12-09-2015
How to eliminate OR clause when joining tables?

HI Team

I have q tricky question when joining 3 tables in Teradata DB.In the last line of my query , I am comparing one condition with C an B tables and its creating performance issue in returning the data . Basically I want to eliminate OR clause and try other approach.Is there any logic to satisfy this condition . Thanks in advance

Code:
[SEL * FROM 
( SEL ORD_ID,NUM ,DATE 
from TABLE1 ) A
ON ORD_ID is NOT NULL
LEFT JOIN 
(
SELECT ORD_ID,NUM,DATE ,Contract_ID
FROM TABLE2) B
ON ORD_ID is NOT NULL
LEFT JOIN 
( SELECT ORD_ID,NUM,DATE ,Contract_ID
FROM TABLE3 ) C
ON (A.ORD_ID=C.ORD_ID
AND A.NUM=C.NUM)
OR ( C.Contract_ID=B.Contract_ID)
]


Last edited by Scrutinizer; 12-09-2015 at 02:25 PM.. Reason: code tags
# 2  
Old 12-10-2015
I don't know Teradata syntax, but maybe you could split up that query into two parts: (1) join "a" and "c" and (2) join "b" and "c". Then combine the results.
# 3  
Old 12-12-2015
It looks like you're doing a correlated subquery. Those are nasty performance killers.

Could you post the columns available in each table, the relationships between the tables, and the results you're trying to get?
This User Gave Thanks to achenle For This Post:
# 4  
Old 12-13-2015
Hmm that is nasty - here is a generalized discussion of the problem:

https://en.wikipedia.org/wiki/Correl...ted_subqueries
This User Gave Thanks to jim mcnamara For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

If clause query

Hi, i need to add a condition in my IF clause where i need to check if the file exists in a folder and return true out of it. but in my directory i have multiple files with same name but datestamp append on it for e.g. export f1 = filename export f2=filename1 if ] then echo "No... (9 Replies)
Discussion started by: rohit_shinez
9 Replies

2. Shell Programming and Scripting

How to search for a directory with if clause?

Hello All, I want to do a conditional search for a directory, i.e pathname=/abc/def foldername=xyz if ( $pathname/$foldername/aaa ) then .................. fi Here i am searching for aaa directory inside the path and if it exist then it should go inside the loop. Can... (1 Reply)
Discussion started by: Pramod_009
1 Replies

3. Shell Programming and Scripting

../ in perl and if clause

Hi can anyone please explain what the below code does? i mean $fide_stopfile = ? when $FIDE_SCR = '/fs/dir1/dir2/common/scr' and also little confused with if clause too. what it check? $fide_stopfile = "$ENV{FIDE_SCR}/../tmp/STOP"; if ( -e $fide_stopfile > 0 ) { ... (3 Replies)
Discussion started by: ptappeta
3 Replies

4. Shell Programming and Scripting

If clause in perl

HI friends , I am very new to perl .please dont mind if i ask silly questions. I seee below code in one sript if ( exists $ENV{FMTWRP_TMP_DIR} and $ENV{FMTWRP_TMP_DIR} ) { $tdir = $ENV{FMTWRP_TMP_DIR}; } whats does this mean . I am very confused about the if clauses in... (1 Reply)
Discussion started by: ptappeta
1 Replies

5. Shell Programming and Scripting

Use a shell variable in where clause

Hi all, I want to use a variable inside my sql query and below is my script: #!/bin/ksh export b="abcd" a=`sqlplus -s abc/def@ghi <<++ set heading off; set feedback off; select xxx from mytable where clmn_nm='$b'; exit; ++` echo $a But the output i get is below: $>... (4 Replies)
Discussion started by: Jayaraman
4 Replies

6. Shell Programming and Scripting

How to eliminate ^L

Hi, I am trying to create a text file from data retrieved from a query.The data retrieved is having this character '^L' at regular intervals of the data. How can i eliminate this, Please find below the sample data. I tried sed -e "s/\^L//g" to convert it, but with no luck ^LCODE*SERIAL... (11 Replies)
Discussion started by: ramkiran77
11 Replies

7. Shell Programming and Scripting

multiple conditions in 'if clause'

Hi, When i use the below code snippet in my shell script OFC_10.sh: if then echo "Success" exit 2 elif then echo "Failure" exit 6 I get the error message: ./OFC_10.sh: line 41: ' ./OFC_10.sh: line 45: ' Line 41 is the line where If loop starts and line 45 is... (2 Replies)
Discussion started by: shrutihardas
2 Replies

8. Shell Programming and Scripting

Dynamic SQL for where clause

Hi, I have an app which user can query the database based on 4 criteria, that is Field1, Field2, Field3 and Field4 Mya I know how to write a dynamic SQL where I can choose to retrieve data based on their selected value. eg. where Field1=AAA eg. where Field1=AAA and Field2=BBB eg.... (1 Reply)
Discussion started by: TeSP
1 Replies

9. UNIX for Dummies Questions & Answers

if clause

hi, pls could you help me with one program in KSH ( i have sunOS). I need to create an If clause, that prints an error message and filenames, when in a directory are found some files of null size (find . -type f -size 0 ). thanks (3 Replies)
Discussion started by: palmer18
3 Replies

10. Shell Programming and Scripting

Converting tables of row data into columns of tables

I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated. Input: test_data_1 1 2 90% 4 3 91% 5 4 90% 6 5 90% 9 6 90% test_data_2 3 5 92% 5 4 92% 7 3 93% 9 2 92% 1 1 92% ... Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies
Login or Register to Ask a Question