|
View previous topic :: View next topic
|
| Author |
Message |
Delip
New User
Joined: 13 Sep 2007
Posts: 46
Location: Bangalore
|
|
|
|
Hi,
I have a situation where the execution of query needs to be done based on an input. I can have a town or postcode as input. Only one would come as input. Column to be fetched is same but the tables are different. Currently I am using a union all like below
select c1 from t1 where town=:input post town and indicator = 1
union all
Select c1 from t2 where postcode= :input postcode and indicator=2
but the issue is the job is going to go to both the tables and causing performance issues.
Is it possible in a query itself as
if indicator = 1 then fetch from t1
else fetch from t2
(it is not possible to do in cobol as the input is not limited to only two inputs. there are nearly 10 inputs,so it is very difficult to do programatically).
Appreciate the help |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Hi Delip,
| Quote: |
| (it is not possible to do in cobol as the input is not limited to only two inputs. there are nearly 10 inputs,so it is very difficult to do programatically). |
What do you mean by 10 inputs?
and
| Quote: |
select c1 from t1 where town=:input post town and indicator = 1
union all
Select c1 from t2 where postcode= :input postcode and indicator=2 |
How are you executing the query the query currently and how do you differentiate in providing what is the input? |
|
| Back to top |
|
|
Delip
New User
Joined: 13 Sep 2007
Posts: 46
Location: Bangalore
|
|
|
|
Hi Pandora,
Apologies for the ambiguous statements. Currently I am executing the query through a cursor. Based on the input, I am setting a flag. If it is town, I am passing 1 into the indicator. If it post code, indicator is 2. Like this I am passing various values to the Indicator(this is a working storage variable but not a column of any table).
As the number of inputs are more, I do not want to handle those many cursors so I put every thing in one cursor . But this is causing performance issues.
Hope I am clear now.Let me know if you still need details. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
What are the values present in the input file?
Is it not possible to read the file record by record and process? |
|
| Back to top |
|
|
Delip
New User
Joined: 13 Sep 2007
Posts: 46
Location: Bangalore
|
|
|
|
| we are processing record by record. every record has one of the inputs (like town/post code/telephone etc).for every record we open the cursor and do the processing |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| ok and you feel that process to be not efficient? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
Why do you use union because if you get "town" then rest all the Select in that union is useless? Why don't you write each 10 separate queries based on what input you get from the file for that perticular record and route them accordingly? How many rows are present in t1 and t2 tables? Were there proper indexes? because your performance issue might have many reason but not just the approach as you think.
| Quote: |
| As the number of inputs are more, I do not want to handle those many cursors so I put every thing in one cursor |
Why so and how many possible cursors you are thinking of here ? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| sounds as if dynamic sql is needed here. |
|
| Back to top |
|
|
Delip
New User
Joined: 13 Sep 2007
Posts: 46
Location: Bangalore
|
|
|
|
| is there any other way of doing instead of a dynamic query? how do we build a dynamic query? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
| Back to top |
|
|
|
|
