Gather stats of a table sytax

begin

DBMS_STATS.GATHER_TABLE_STATS('owner', 'table_name',
                                       estimate_percent=>50,
                                       block_sample=>TRUE,
                                       degree=>4) ;

end  ;

or

ANALYZE TABLE <table_name> ESTIMATE STATISTICS 1000 ROWS;

or

ANALYZE TABLE <table_name> ESTIMATE STATISTICS 50 PERCENT;

ERROR: "Internal error. The Source Qualifier [] contains an unbound field" while running a workflow in PowerCenter

Problem Description
While running a workflow in the PowerCenter, the following error message is displayed:

Internal error. The Source Qualifier [] contains an unbound field []. Contact Informatica Global Customer Support.

Cause
This issue occurs when there are one or more unconnected ports between the Source and Source Qualifier.

Solution
The error encountered is not a product bug. It can be overcome by good mapping design and usage.

SQL to find Monday and Friday of the week of a given date

select next_day (sysdate-7,'FRIDAY') Last_Friday, next_day (sysdate-7, 'MONDAY') Last_Monday from dual;

How to count # of occurrences of a character inside a string?

LEN(ColumnToCheck) - LEN(REPLACE(ColumnToCheck,"M",""))

Like Operation in expression transformation

To check if a sub string is present in a string like '%%' is used in SQL format. Equivalent regular expression informatica will be


REG_MATCH(Employee,'.*Be.*')


Employee Result

Beat                 TRUE

Bearo                 TRUE

Kartheek         FALSE

Deb                 FALSE

Meta data table to know DML operations on table

Use below table if you want to know recent updates, inserts or deletions into table.

ALL_TAB_MODIFICATIONS -describes tables accessible to the current user that have been
                                                     modified since the last time statistics were gathered on the tables

DBA_TAB_MODIFICATIONS - provides such information for all tables in the database.

USER_TAB_MODIFICATIONS- provides such information for tables owned by the current user

VI Command

To use vi: vi filename.      -->this is a command to open a file
To get insert mode of vi: i. -->enter i to change vi mode to insert for making changes to file
To enter vi command mode: [esc] Counts -->enter esc if you are done with changes in insert mode 
                                                                          and if u want to exit(you have to run below commands                                                                            for exiting after clicking esc)
To exit vi and save changes: ZZ or :wq. --> This is a command to close a file with saving
To exit vi without saving changes: :q!   -->This is a command to close a file with out saving

Query to delete duplicate records in oracle

delete from
   customer
where rowid in
 (select rowid from
   (select
     rowid,
     row_number()
    over
     (partition by custnbr order by custnbr) dup
    from customer)
  where dup > 1)

LISTAGG - Concatenate multiple rows into a single delimiter-separated string

It is a similar, though simpler, exercise to transpose data from rows to a comma-seperated list.

LISTAGG is a in-built function in Oracle that lets you concatenate multiple rows of data into a single delimiter-separated string. LISTAGG was introduced in Oracle 11G R2, before which one would use the circuitous MAX(SYS_CONNECT_BY_PATH) or STRAGG methods for the same result.

Here’s how LISTAGG works.

Using the standard departments and employees tables of HR schema: list the employees in a comma-separated list against each department they belong to.

The SQL:

SELECT deptno
     , LISTAGG(empno, ',')
         WITHIN GROUP (ORDER BY empno)
         AS emp
FROM   emp
GROUP BY deptno;
When executed:

SQL> SELECT deptno
  2       , LISTAGG(empno, ',')
  3           WITHIN GROUP (ORDER BY empno)
  4           AS emp
  5  FROM   emp
  6  GROUP BY deptno;

deptno emp
------------- ---------------------------------------
           10 200
           20 201,202
           30 114,115,116,117,118,119
           40 203
           50 120,121,122,123,124,125,126,127,128,129

Query to get DFF and Segment Values

SELECT ffv.descriptive_flexfield_name “DFF Name”,
ffv.application_table_name “Table Name”,
ffv.title “Title”,
ap.application_name “Application”,
ffc.descriptive_flex_context_code “Context Code”,
ffc.descriptive_flex_context_name “Context Name”,
ffc.description “Context Desc”,
ffc.enabled_flag “Context Enable Flag”,
att.column_seq_num “Segment Number”,
att.form_left_prompt “Segment Name”,
att.application_column_name “Column”,
fvs.flex_value_set_name “Value Set”,
att.display_flag “Displayed”,
att.enabled_flag “Enabled”,
att.required_flag “Required”

FROM apps.fnd_descriptive_flexs_vl ffv,
apps.fnd_descr_flex_contexts_vl ffc,
apps.fnd_descr_flex_col_usage_vl att,
apps.fnd_flex_value_sets fvs,
apps.fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id=ffv.application_id
AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id=att.flex_value_set_id
AND ffv.title like ‘Give Title Name’
AND ffc.descriptive_flex_context_code like ‘Give Context Code Value’

ORDER BY att.column_seq_num