|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
|
|||
|
|||
|
I've made this query in Qmf for Windows (using DB2)
I select these columns: TABLEONE: SEC TABLETWO: B_DATE and E_DATE (they are numbers – not dates); RATE (is a number) I first calculate the difference between E_DATE and B_DATE and between a chosen date and B_DATE creating the new columns GG_CED_C and GG_MAT_C. These new columns are calculated perfectly (they make the difference between the two dates according to the 30/360 date count convention) and don’t give me any problems. They are all filled with numbers. I’m able to use these new columns to make other calculations. For example I succeed in calculating RATE/GG_CED_C* GG_MAT_C but if I try to calculate RATE/360*GG_MAT_C AS RATEO the result is that in some records I have my new column RATEO correctly calculated, while in some other records there is no value in the column. For example: B_DATE E_DATE RATE GG_CED_C GG_MAT_C RATEO 20061229 20070630 3,995 180 90 0,99875 20061229 20070630 4,185 180 90 Dates are identical and so are GG_CED_C and GG_MAT_C: why don't I have the result in the second record???? SELECT SEC, B_DATE, E_DATE, RATE, GG_CED_C, GG_MAT_C, RATE/ 360 * GG_MAT_C AS RATEO FROM ( SEC, B_DATE, E_DATE, RATE, GG_CED_C, GG_MAT_C (YEAR2 - YEAR1)*360 + (MONTH2 - MONTH1)*30 + (DAY2 - DAY1) AS GG_CED_C, (YEARRIL - YEAR1)*360 + (MONTHRIL - MONTH1)*30 + (DAYRIL - DAY1) AS GG_MAT_C, FROM ( SELECT A.SEC, , B_DATE, E_DATE, RATE, TRUNC(B_DATE/10000,0) AS YEAR1 , TRUNC(MOD(B_DATE,10000)/100,0) AS MONTH1 , CASE WHEN MOD(B_DATE,1000)= 229 THEN 30 WHEN MOD(B_DATE,1000)= 228 AND MOD(TRUNC(B_DATE/10000,0),4) > 0 THEN 30 ELSE MIN(MOD(B_DATE,100),30) END AS DAY1, TRUNC(E_DATE/10000,0) AS YEAR2 , TRUNC(MOD(E_DATE,10000)/100,0) AS MONTH2 , CASE WHEN MOD(E_DATE,1000)= 229 THEN 30 WHEN MOD(E_DATE,1000)= 228 AND MOD(TRUNC(E_DATE/10000,0),4) > 0 THEN 30 ELSE MIN(MOD(E_DATE,100),30) END AS DAY2, TRUNC(&DRIL/10000,0) AS YEARRIL , TRUNC(MOD(&DRIL,10000)/100,0) AS MONTHRIL , CASE WHEN MOD(&DRIL,1000)= 229 THEN 30 WHEN MOD(&DRIL,1000)= 228 AND MOD(TRUNC(&DRIL/10000,0),4) > 0 THEN 30 ELSE MIN(MOD(&DRIL,100),30) END AS DAYRIL, FROM TABLEONE A, TABLETWO C What's happening? After what I've seen in this query I'm wondering if SQL is reliable when it comes to calculations (after all its main duty is to query data and not to make calculations among them)(?????. Please give an explanation to this mystery. Thank you. Anna - Verona (Italy) ![]() |
|
#2
|
|||
|
|||
|
Maybe because GG_MAT_C is NULL in the second row? You may need to check for null values in a computation.
__________________
|
![]() |
| Viewing: Dev Shed Forums > Databases > DB2 Development > New Column Mysterious Behaviour |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|