|
View previous topic :: View next topic
|
| Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi,
I have a query like this
| Code: |
SELECT EMPNO ,
( FIRSTNME
|| ' '
|| MIDINIT
|| ' '
|| LASTNAME ) AS FULLNAME ,
(
CASE
WHEN YEAR(CURRENT DATE - HIREDATE) < 1
THEN VARCHAR(MONTH( CURRENT DATE - HIREDATE ))
|| ' MONTHS'
ELSE VARCHAR(YEAR ( CURRENT DATE - HIREDATE ))
|| ' YEARS'
END ) AS EXP_IN_MONS_YRS ,
JOB ,
( SALARY + BONUS + COMM ) AS GROSS
FROM SHANDB.EMPLOYEE
WHERE SEX = 'M'
ORDER BY YEAR(CURRENT DATE - HIREDATE) ASC ; |
I want know what will be effcient & good for the application, doing conversions & calculations in COBOL program or using SQL functions.
Sushanth |
|
| Back to top |
|
 |
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
Usage of case is ok. But I think you should try to reduce the calculations within the query. Ex.
| Code: |
| CURRENT DATE - HIREDATE |
this can be easily and "efficiently" done using COBOL. All you need to do is store it in a host variable and mention in the SQL. This will reduce a burden from the Query. |
|
| Back to top |
|
|
trilokdewangan
New User
Joined: 19 Jun 2008
Posts: 39
Location: USA
|
|
|
|
Hi Sushanth,
I believe, Embedded SQL with COBOL program is efficient but is also depends on your requirement. Here you gave the query, which can also be written in COBOL program but, you need to install the program in production (if want production data) to get the output Instead you can run SPUFI in production since your query is not complicated. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
Forgot to mention,
| Quote: |
| The efficiency of the functions in SQL depend on the no. of rows that will be returned as a result of this query. So if the result table is too large, then it would be better to go for COBOL. |
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Guyz,
Bharath, YES. Year Calculations can be done in cobol with a few lines of code.
Trilok, Yes. This query can be executed in SPUFI and results can be easily got.
My question is entirely different. "What will be more efficient, cobol codings,calculations, conversion & decisions OR using SQL functions". B'cos i have seen many cobol codings using DB2 like selecting all the columns in table and performing calculations which could go upto tonnes & tonnes of coding. But, what i don't understand is those conversion & calculations can be easily be in SQL itself, which can reduce the amount of coding in cobol.
| Quote: |
| The efficiency of the functions in SQL depend on the no. of rows that will be returned as a result of this query. So if the result table is too large, then it would be better to go for COBOL. |
Because most of the select's have huge resultsets.
In this case, SQL ()'s can be only used in SPUFI.
As said above "efficiency of the ()'s in SQL depends on the no. of rows".
What kind of function()'s can be effcient like calculations()'s, text related()'s, conversion ()'s. And to what extent, will it be efficient.
Sushanth |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| Quote: |
But, what i don't understand is those conversion & calculations can be easily be in SQL itself, which can reduce the amount of coding in cobol.
|
This is to improve the performance of the query, which seems to be your objective. If you want these calculations to be done by SQL, then you are trading off the performance of the query for a few lines of COBOL code.
Such calcualations within the query will cause it to be non indexable.Indexable queries will give you optimal performances. So, Its not wise to make the query non indexable.
Coming to the functions part,
Use of functions for reducing the amount of COBOL code may be useful only if a handfull of rows are returned. If the number of rows is going to be large, then the amount of time taken by the SQL to execute the query will be more. In such cases, COBOL code will surely be efficient.
What I would say is, check the result set in realtime environment and then decide. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Yes bharath. I get it.
Thank You. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
You're welcome.
BTW, was it part of a requirement or just a doubt? |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Just a doubt, bharath.
I am very fond of SQL more than COBOL. That's WHY ?
Sushanth |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I am very fond of SQL more than COBOL. |
We tend to find a reason to use tools we like best. . . 
Suggest you put many tools in your "toolbox", know how to use all of them well, and then choose the proper tool for the task at hand. There is no "one tool that does everything best". |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Thank You dick,
| Quote: |
| There is no "one tool that does everything best". |
That's a very good quote
Sushanth |
|
| Back to top |
|
|
|
|
