String Aggregation Techniques

http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

Query at ReportExtract

You have an employee table, and user will give department number and from which you need to fetch out the respective employees.

select *
from employees
where department_id in (
select distinct department_id
from departments
where department_id = '&DepID'
)

But, twist is, if department table do not have any records, query should return all employee records.

How will you do it? below is the solution

create table departmentsBKP
as
select * from departments

select *
from employees
where department_id in (
select distinct department_id
from departmentsBKP
where department_id = '&DepID'
)
union
select *
from employees
where 0 = (select count(*) from departmentsBKP)

delete from departmentsBKP

and now again if u fire select statement, you will get all records of employees table.

Changing data types of columns...

You have one table say ABC, and in that you have 4 cols which are having data type as varchar2(200).

Requirement is , you need to convert the data types of these columns to Number. This table has a data.

How will you change the data types of these columns ?

Note:- You can not create backup table in production...

----------------

CREATE TABLE ABC(COL1 VARCHAR2(200),COL2 VARCHAR2(200),COL3 VARCHAR2(200),COL4

SET DEFINE OFF;
Insert into HR.ABC
   (COL1, COL2, COL3, COL4)
 Values
   ('1', '1', '1', '1');
Insert into HR.ABC
   (COL1, COL2, COL3, COL4)
 Values
   ('2', '2', '2', '2');
Insert into HR.ABC
   (COL1, COL2, COL3, COL4)
 Values
   ('3', '3', '3', '3');
COMMIT;

---------------

TABLE ABCADD

UPDATE (COL11 NUMBER, COL22 NUMBER, COL33 NUMBER, COL44 NUMBER)
---------------

ABCSET COL44 = TO_NUMBER(COL4)
ALTER

UPDATE ABC SET COL33 = TO_NUMBER(COL3)

UPDATE ABC SET COL22 = TO_NUMBER(COL2)


UPDATE ABC SET COL11 = TO_NUMBER(COL1)
----------------

ALTER  TABLE ABC DROP(COL1, COL2, COL3, COL4)
----------------

ALTER TABLE ABC RENAME column COL44 TO COL4 

ALTER TABLE ABC RENAME column COL33 TO COL3 

ALTER TABLE ABC RENAME column COL22 TO COL2 

ALTER TABLE ABC RENAME column COL11 TO COL1


---------------

1. Here, you need to create dummy columns with number datatype at abc, because you can not create column with same name again. 
2. You converted data using to_number from varchar to number and updated the newly added columns
3. You dropped the old columns
4. You rename newly added columns 

Things to verify...

Lookup Over-ride as clause and port map
Un-connected Lookup Input
Session Variables use
Indirect File Read What if select has more columns that ports available at Lookup and Source Qualifier 
XML of mapping, session, workflow
Mapping, Session, Workflow parameter and variable working
What all things we can parameteriazed ?
Interview Question of Records count - use of aggregator and joiner, second answer to earlier question.
Normalizer, want to trasfer 2 colums as 2 rows. But rest all columns are same
Issue of performance related to birwarkar
in what order we need to perform order by at lookup over-ride
auto option of memory...
full outer join of joiner 

Unix:
 grep "NK2" RDBJANERMM_BRK2_20110916_L1_20110917013839_risk.csv |  grep "USD" | cut -d"," -f3 | uniq

1) Command for listing only the files with some data =>

 find . -size +0c -ls | grep BFE2

2) Command for listing all the files but sorted according to their Size =>

 ls -lrS | grep BFE2

3) To convert "," to ";" fron whole file
First open the file using => view temp.csv
 %s/,/;/g

4) To display 12th colum from a .csv file, with condition that file "risk.csv" contains "EQUIP:ATFV"
 grep "EQUIP:ATFV" risk.csv | cut -d"," -f12

5) To view only directories
  ls -lrt | grep ^d

6) To delete roes from 3 to 17 in vi editor
 3,17d

7) To delete a row in vi editor
 press d twice with the cursor in the row to be deleted.