|
View previous topic :: View next topic
|
| Author |
Message |
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
Hi,
My requirement is as below.
I need to select FST_NM column from NAME01 table for all the employees that are in EMPLOYEE table, and also for the 3 employees that are going to be derived dynamically. So I have repeated the column 4 times on total in the below dynamic sql.
Please suggest if any one of you done using some best approach other than this.
| Code: |
SELECT FST_NM FROM NAME01
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE)
OR EMP_ID = ?
OR EMP_ID = ?
OR EMP_ID = ? |
Other Approach I've tried:
I tried using IN clause as below.
| Code: |
SELECT FST_NM FROM NAME01
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE)
OR EMP_ID [b]IN[/b] ? |
For this case I've used STRING statement of COBOL to combine the 3 employees and passed to dynamic sql to replace the '?'. But I am getting -302 since the variable I am passing has PIC clause larger than the EMP_ID column. For example EMP_ID in the table is char(10). I have tried using X(38) to concatenate the 3 employees and building quotes and commas.
Please suggest me if there is any best method than the first method.
Regards
Amar |
|
| Back to top |
|
 |
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
The code in second part of the post has a typo. Here is the code.
| Quote: |
SELECT FST_NM FROM NAME01
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE)
OR EMP_ID IN ? |
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
there are values for emp_id of NAME01
that are not in EMPLOYEE? |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
Hi Amarendra,
From your first post, I assume you have a COBOL program that uses DYNAMIC SQL to get the desired output.
Can you please let us know why, this can't be achieved using good old EMBEDDED SQL cursor?
If you are concerned about the performance, embedded SQL is the way to go.
For example, the above query can be written-
| Code: |
SELECT FST_NM FROM NAME01
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE)
OR EMP_ID = :WS-EMP-ID1
OR EMP_ID = :WS-EMP-ID2
OR EMP_ID = :WS-EMP-ID3 |
|
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
Hi,
Yes there are for some employees in NAME01 that are not in EMPLOYEE.
Regards
Amar |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Why did you choose Dynamic SQL?
Whats the minimum number of value you pass and maximum? |
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
Hi agkshirsagar,
The table name needs to be dynamically determined. You see the table name I've given in the query was NAME01. It is actually NAMEXX where XX will be dynamically determined. Here XX ranges from 01-15. So if I need to do and embedded SQL then I need to have 15 DECLARE cursors/opens/Fetch/close. So our shop uses the dynamic SQL as the choice.
Regards
Amar |
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
Hi Pandora-Box,
| Quote: |
| Whats the minimum number of value you pass and maximum? |
Nice Question. For now it is 3 Employees, but some times they all might not present and sometimes they all are present. I am wondering what if there are going to be more than 3 in future. For now, can you please suggest for the 3 case. Since this is a new program as of now only 3 are considered.
Regards
Amar |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| So our shop uses the dynamic SQL as the choice. |
even though, apparently, no one knows how to code? (write the proper sql)
the idea of dynamic sql is to allow for multiple options for
select lists
where clauses - thus possibly taking advantage of run-time optimization.
when the only difference is the name of 1 table,
15 or 500 cursors, does not matter, should be imbedded,
thus reducing your run-time bind processing. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
So you are saying you wanted to process 01 - 15 tables and process one program Dynamically?
Also if you just wanted to select why not unload the needed data parallely and process ?
Note:Edited |
|
| Back to top |
|
|
|
|
