tableau Date Formating

Hi

In this entry i will show how to format date in tableau reporting screen.

At first I choose excel as my datasource  and  use Orders (Sample - EU Superstore) and I want to see people who have made successful orders and details about it.

In here we have  a column called  ShipDate  and now i want to create a variable  that only shows me YEAR.MONTH information for  aggregations.


Formula for calculated Field:

Result:

Oracle Finding first day of the month

Hi

Following sql helps you to find first day of previous months.


select  trunc(sysdate) from dual

22.01.2016

First Day of Current Month:
select    ADD_MONTHS (   LAST_DAY (  TRUNC (SYSDATE)  )  ,   -1  )+1   from dual

01.01.2016                                                                    

First Day of  eleven months before:                                      
select    ADD_MONTHS (   LAST_DAY (  TRUNC (SYSDATE)  )  ,   -11  )+1   from dual

01.03.2015

Oracle Finding Last_Day of Previous months

Hi

Following sql helps you to get previous months  last_day values:

Current Date:
select  trunc(sysdate) from dual

22.01.2016

Results:
-- One month before
select   ADD_MONTHS (LAST_DAY (TRUNC (SYSDATE)),-1)   from dual
31.12.2015

-- Eleven months before
select   ADD_MONTHS (LAST_DAY (TRUNC (SYSDATE)),-11)   from dual
28.02.2015

Oracle - CREATE NLS_CALENDAR Table

Hi

In this entry i will explain how can we create a NLS_CALENDAR table  , which can be used in ETL procedures as a lookup table.

At first lets create our table :

(
Table contains DAY , MONTH,YEAR and FULL_DATE values
Partition by YEAR and  Subpartitioned by MONTH
)

CREATE  TABLE   NLS_CALENDAR_TABLE
(
DAY  NUMBER,
MONTH  NUMBER,
YEAR NUMBER,
FULL_DATE  DATE
)
PARTITION BY RANGE ( YEAR )
        SUBPARTITION BY LIST (MONTH)
                SUBPARTITION TEMPLATE
                (
                        SUBPARTITION  MONTH1  VALUES(1),
                        SUBPARTITION  MONTH2  VALUES(2),
                        SUBPARTITION  MONTH3  VALUES(3),
                        SUBPARTITION  MONTH4  VALUES(4),
                        SUBPARTITION  MONTH5  VALUES(5),
                        SUBPARTITION  MONTH6  VALUES(6),
                        SUBPARTITION  MONTH7  VALUES(7),
                        SUBPARTITION  MONTH8  VALUES(8),
                        SUBPARTITION  MONTH9  VALUES(9),
                        SUBPARTITION  MONTH10  VALUES(10),
                        SUBPARTITION  MONTH11  VALUES(11),
                        SUBPARTITION  MONTH12  VALUES(12)
                )
        (
        PARTITION YEARXXX VALUES LESS THAN (2012)
)

Second step PL/SQL code for filling the table :

Note that  NLS_DATE_FORMAT for following code is 'DD.MM.YYYY'


DECLARE
BEGIN_DATE DATE;
END_DATE  DATE;
V_DYNTASK LONG;
VAL1  DATE;
DIFF_VAL  NUMBER;
DAY_VALUE  NUMBER;
MONTH_VALUE  NUMBER;
YEAR_VALUE  NUMBER;
YEAR_PART_VAL  NUMBER;
YEAR_VALUE_TMP NUMBER ;
BEGIN


BEGIN_DATE:= '01.01.2012';
END_DATE:='01.01.2050';  --- NOT INCLUDED
YEAR_VALUE_TMP:=0;

V_DYNTASK:='SELECT TO_DATE('''||END_DATE ||''') -  TO_DATE('''||BEGIN_DATE||''') FROM  DUAL ';
EXECUTE IMMEDIATE V_DYNTASK INTO DIFF_VAL;


FOR  COUNTER1 IN  0..DIFF_VAL-1
LOOP

        V_DYNTASK:= 'SELECT  TO_DATE('''||BEGIN_DATE||''')+'||COUNTER1||'  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO VAL1;



        V_DYNTASK:=  'SELECT  EXTRACT(DAY  FROM  TO_DATE('''||VAL1||''',''DD.MM.YYYY'') )  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO DAY_VALUE;

        V_DYNTASK:=  'SELECT  EXTRACT(MONTH  FROM  TO_DATE('''||VAL1||''',''DD.MM.YYYY'') )  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO MONTH_VALUE;

        V_DYNTASK:=  'SELECT  EXTRACT(YEAR  FROM  TO_DATE('''||VAL1||''',''DD.MM.YYYY'') )  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO YEAR_VALUE;

     
   
         IF   YEAR_VALUE_TMP!=YEAR_VALUE
         THEN
           
            YEAR_PART_VAL:=YEAR_VALUE+1;
            V_DYNTASK:= ' ALTER TABLE NLS_CALENDAR_TABLE   ADD PARTITION YEAR'||YEAR_VALUE||'  VALUES LESS THAN  ( '||YEAR_PART_VAL||' ) ' ;
            DBMS_OUTPUT.PUT_LINE(V_DYNTASK);
            EXECUTE IMMEDIATE  V_DYNTASK;
            COMMIT;
       
         END IF;
     
     
     
       V_DYNTASK:=
       '
       INSERT INTO  NLS_CALENDAR_TABLE  ( DAY ,  MONTH, YEAR ,  FULL_DATE )
       SELECT  '
       ||DAY_VALUE||'  ,  '||MONTH_VALUE||'  ,  '||YEAR_VALUE||' , '''||VAL1||'''  FROM  DUAL';
     
     
       EXECUTE IMMEDIATE V_DYNTASK;
       COMMIT;

       YEAR_VALUE_TMP:=YEAR_VALUE;  --  CHECK FOR YEAR IS CHANGED OR NOT  , IF YES THEN ADD PARITION
     
END LOOP;


END;


Result will be look like this:

Also Final Table Structure:

Oracle - Big Table Joins with Nested Loop

Hi

In this entry  i am gonna show faster execution sample for big data queries.

Environment: Oracle 11g


First we have the following SQL sample ( try to join 50M vs 88M data  which takes  7 minutes to return with parallel hint  )

select  /*+parallel( m1 , 12)  parallel( s1 12) */  *
from
test_table1  m1 ,    --50 million rows
test_table2 s1         --88 million rows
where
m1.col1=s1.col2   --Return Time : 7minutes

By Default optimizer choose HASH_JOIN which causes long execution time

Plan
SELECT STATEMENT ALL_ROWS Cost: 11 M Bytes: 268092 G Cardinality: 90 G
	3 HASH JOIN Cost: 11 M Bytes: 268092 G Cardinality: 90 G
		1 TABLE ACCESS FULL TABLE TEST_TABLE2 Cost: 474 K Bytes: 109 G Cardinality: 97 M
		2 TABLE ACCESS FULL TABLE TEST_TABLE1 Cost: 446 K Bytes: 114 G Cardinality: 61 M

To perform better first we will create an index  over join column (COL1)  in our smaller table.( TABLE1)

Then over same statement  , we add USE_NL hint  and we got the result as follows:

select   /*+ USE_NL( m1, s1)*/  *
from
test_table1  m1 ,
test_table2 s1
where
m1.col1=s1.col2   --   Return Time: 421 MSEC  (Much faster !! )

Plan
SELECT STATEMENT ALL_ROWS Cost: 72 G Bytes: 268092 G Cardinality: 90 G
	4 TABLE ACCESS BY INDEX ROWID TABLE TEST_TABLE1 Cost: 1 K Bytes: 2 M Cardinality: 923
		3 NESTED LOOPS Cost: 72 G Bytes: 268092 G Cardinality: 90 G
			1 TABLE ACCESS FULL TABLE TEST_TABLE2 Cost: 474 K Bytes: 109 G Cardinality: 97 M
			2 INDEX RANGE SCAN INDEX X_INDEX Cost: 2 Cardinality: 1 K

Informatica Numeric - AlphaNumeric check

Hi

In this  entry i will explain how to deal with AlphaNumeric  characters.

Assume that we have  a target column  called  TRG1 which is a number field.

From source table  lets say we have to a map column COL1 which contain Alphanumeric characters.

By only doing TO_DECIMAL operation only cause informatica set Alphanumeric data to  0  such as :

Lets say we have following source data

T1245
X58924
23783
1245

By just using TO_DECIMAL transformation  we get following in our target table:


0
0
23783
1245


So  in order to fix this issue following code can helps.


IIF
(
      REG_MATCH(COL1 , '\d+')=1  ,
      TO_DECIMAL(COL1 ),
      TO_DECIMAL( SUBSTR(COL1 ,2) )
)

Result:
1245
58924
23783
1245

Informatica how to run post or pre SQL statements in workflow sessions

Hi

Following script  is a sample struncture for how to run scripts in Informatica Workflow session's
pre-post sql statement.




Instead  of  standart  ;  change with \;

BEGIN

PROCEDURE1\;

PROCEDURE2\; 

PROCEDURE3\;

END\;