Ravi raj

 Profile Answers by Ravi raj 

• Jan 31st, 2011

 

Select * from EMP where EMP_DOJ = 010110

Here

EMP is the Table name,EMP_DOJ is the date of joining of the Employee in EMP table.

If any record/records found with DOJ as 010110,it will retrive all the records in the EMP table.
Now you need to compare the front end values with back end values with respect to corresponding record.

Note :some times Back end fields are storing for Date filed like "DDMMCCYY",but front end shows only "DDMMYY".

DD =     Date
MM =    Month
CCYY = Current year(2010 - Full year)
YY =      Year(10 - Last 2 digits).

For above situation you need to identify the Date field format whether it is accepting "DDMMYY" or "DDMMCCYY"

nicel007

 Profile Answers by nicel007 

• Mar 4th, 2011

 

Suppoe there is table called SOURCE, conatins column NAME and AGE.  The reuirement is that to poulate records from SOURCE to the target tale TARGET. In TARGET table contain following columns NAME and CATEGORY.

Following is the rule for the category selection
  
Age Group     Category
---------------   ------------
0-17       ->           A
18-30     ->           B
31 - 50   ->           C
51-70     ->            D
71+        ->            E

Once the ETL jobs are done then we can validate the data with the combination CASE and MINUS we can validate it as following

/* Source - Target */
/* Source*/
SELECT
            NAME,
            (
              CASE
                       WHEN AGE <18 THEN 'A' 
                       WHEN AGE  BETWEEN 18 AND 30 THEN 'B' 
                       WHEN  AGE BETWEEN 31 AND 50 THEN 'C' 
                       WHEN  AGE BETWEEN 51 AND 70 THEN 'D'              
                ELSE
                          'E' 
                END
              ) AS CATEGORY
    FROM 
             SOURCE     
 MINUS 
 /* Target */
      SELECT
                NAME, 
                CATEGORY         
      FROM
               TARGET

Like this do a Target minus Source too. Idealy both the scripts (source -Target) & (Targe - Source) should return as values at all. If it returns any value means something wrong in the Code

Note:- Here i didn't include the source filtering part and Target filter part. Say if you want to take some filter condition that need to b applied in both Source part and target part
 
For Eg:- Time stamp.