Home » SQL & PL/SQL » SQL & PL/SQL » Nvl( ) Function (Urjent query help)
Nvl( ) Function (Urjent query help) [message #217603] Fri, 02 February 2007 23:50 Go to next message
annu-agi1
Messages: 17
Registered: August 2006
Location: Pakistan
Junior Member

Hi
Experts
first see the given data. All we have to do is to join tables on the bases of dt_from and dt_to with trans_month,trans_year and empl_Cd. You can see 08/01/2006 dt_to is empty. if we are generating query for the date after 08/01/2006 to till date then we assume that is our current rate. Now the requirment is all we have to this by the help of NVL function.
Please Help me out this situation or suggust me .

note: for your ease i m uploading txt file with the same data view in correct form

Table 1 

dt_from         dt_to           Empl_cd     basic     comm      h-rent
01/01/2004      10/31/2004      10207       3500      550       4000
11/01/2004      12/31/2005      10207       4500      980       7800
01/01/2006      07/31/2006      10207       5900      1520      9000
08/01/2006                      10207       6300      1950      11180
                              
1/1/2004        12/31/2004      70802       2400      440       3300
1/1/2005        12/31/2005      70802       3500      590       4500
1/1/2006        12/31/2006      70802       6500      620       4990
1/1/2007                        70802       9300      781       8000


Table 2

empl_cd  tran_month   tran_year   attn_days  sick_days  casual_days
10207             1      2004            29      1      1
10207             2      2004            27      0      1
10207            11      2004            30      0      0
10207            12      2004            30      1      0

10207             1      2005            31      0      0
10207             2      2005            28      0      0
10207             3      2005            29      1      1
10207             4      2005            27      2      1

10207             1      2006            30      0      1
10207             5      2006            28      0      0
10207             7      2006            21      9      1
10207             9      2006            30      0      0

10207             1      2007            30      0      1
10207             2      2007            28      0      0
10207             3      2007            21      9      1
10207             4      2007            30      0      0

70802             1      2004            29      1      1
70802             2      2004            27      0      1
70802             3      2004            31      0      0
70802             4      2004            30      1      0

70802             1      2005            31      0      0
70802             2      2005            28      0      0
70802             3      2005            29      1      1
70802             4      2005            27      2      1

70802             1      2006            30      0      1
70802             2      2006            28      0      0
70802             3      2006            21      9      1
70802             4      2006            30      0      0

70802             1      2007            30      0      1
70802             2      2007            28      0      0
70802             3      2007            21      9      1
70802             4      2007            30      0      0


[Mod-Edit: Used code tags and replaced tabs to display data properly. Please do this yourself next time, instead of attaching a file]
  • Attachment: ctm.txt
    (Size: 1.64KB, Downloaded 123 times)

[Updated on: Sat, 03 February 2007 02:43] by Moderator

Report message to a moderator

Re: Nvl( ) Function (Urjent query help) [message #217638 is a reply to message #217603] Sat, 03 February 2007 10:29 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try:

NVL(dt_to,SYSDATE)

HTH
Previous Topic: Seelct records with recent Update_Date
Next Topic: transfer data from deffirent versions
Goto Forum:
  


Current Time: Sat Dec 10 13:01:05 CST 2016

Total time taken to generate the page: 0.04609 seconds