DB2 Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesDB2 Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
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  
Old April 25th, 2007, 05:06 AM
annamaria annamaria is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Posts: 1 annamaria User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 14 m
Reputation Power: 0
Thumbs up New Column Mysterious Behaviour

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)

Reply With Quote
  #2  
Old April 25th, 2007, 11:30 AM
LKBrwn_DBA LKBrwn_DBA is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2006
Posts: 294 LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 2 Days 16 h 48 m 15 sec
Reputation Power: 9
Cool


Maybe because GG_MAT_C is NULL in the second row?
You may need to check for null values in a computation.

__________________

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDB2 Development > New Column Mysterious Behaviour


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

IBM developerWorks




© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway