Question { TCS, 6899 }

cust id,cust quty like 1,101;1,102;1,103 i want output like
cust id,cust quty 1 101,102,103 in oracle please write a
query in oracle

Answer

SELECT
CUST_ID,
DECODE(CUST_ID,'101',CUST_QUTY) AS CUST_QUTY1,
DECODE(CUST_ID,'102',CUST_QUTY) AS CUST_QUTY2,
DECODE(CUST_ID,'103',CUST_QUTY) AS CUST_QUTY3
FROM TABLE_NAME
GROUP BY CUST_ID;

Question { TCS, 6899 }

cust id,cust quty like 1,101;1,102;1,103 i want output like
cust id,cust quty 1 101,102,103 in oracle please write a
query in oracle

Answer

PIVOT finction available from ORCLE 11g Onwards:

SELECT *
FROM (SELECT customer_id, customer_quantity
FROM pivot_test)
PIVOT (customer_quantity FOR (customer_quantity
) IN (101, 102, 103))
ORDER BY customer_id;


Output:
1 101 102 103

Question { 8799 }

how to get the unique records on multiple columns by using
sequential file stage only

Answer

SORT -u file_name.txt
or
SORT|UNIQ file_name.txt

Question { Wipro, 7067 }

iam new to datastage...now i want to know what are fact
tables, dimension tables in bank domain...if any body knows
plz tell me asap..

Answer

FACT Table:(Daily transaction details)
Transaction_Details

Dimension Tables:(Which have static data)

Customer_details
Account_Details

DATE & TIME

Question { Infotech, 8330 }

If seg file having 10 records
ex:eid
1
2
"
"
10
if oracle database having 100 records
ex:eid
1
2
"
"
100
how to delete matched records permenently from oracle
database using datastage ?

Answer

we can delete in 2 ways:
1) in TGT Oracle Stage,
select 'Write Mode' as 'DELETE'
and write the delete Query as
"DELETE FROM EMP_TGT
WHERE EID IN (SELECT EID FROM EMP_SRC)"

2)Select 'Write Mode' as 'UPDATE' and write some dummy update query(This statement will not execute as WHERE condition 1=2) as
"UPDATE EMP SER EID='1111' WHERE 1=2"
then write delete query in 'Run before SQL statemets' as
"DELETE FROM EMP_TGT
WHERE EID IN (SELECT EID FROM EMP_SRC)"

Question { Infotech, 7821 }

tab1 tab2
1,a 1,d
2,b 3,c
perfoms outerjoin what is the o/p?
write sql query for outerjoin?

Answer

OUTPUT:
1, a, d
2, b, NULL
3, NULL, c

SQL Query:
SELECT * FROM TAB1 FULL OUTER JOIN TAB2 ON
TAB1.key_column=TAB2.key_column;

or

SELECT * FROM TAB1,TAB2 WHERE
TAB1.key_column=TAB2.key_column(+)
UNION
SELECT * FROM TAB1,TAB2 WHERE
TAB1.key_column(+)=TAB2.key_column;

Question { Infotech, 7821 }

tab1 tab2
1,a 1,d
2,b 3,c
perfoms outerjoin what is the o/p?
write sql query for outerjoin?

Answer

OUTPUT:

1, a, d
2, b, NULL
3, NULL, c

Question { TCS, 6244 }

Hi All, I have a file. i need to fetch the records between
first and last records by using transform stage.

EX:-

Source:
EMPNO EMPNAME
4567 shree
6999 Ram
3265 Venkat
2655 Abhi
3665 Vamsi
5852 Amit
3256 Sagar
3265 Vishnu

Target:

EMPNO EMPNAME
6999 Ram
3265 Venkat
2655 Abhi
3665 Vamsi
5852 Amit
3256 Sagar

I dont wan't to Shree and vishnu records.we can fetch
another way also but How can I write the function in
transform stage?

Answer

In the transformer stage's link constraints:
write below constraint
@INROWNUM <> 1 And @INROWNUM <> LastRow()
then you will get the desired out put.

Question { 3208 }

Hi any one has datasatge 8.5 certication dump could you
please provide your details i will contact you.
send reply to my mail rajaadivi@gmail.com
Thanks in Advance

Answer

hi raja,
I have datasatge 8.5 certication dump, I f u want U can
contact me on mail Id 'subbuchamala@gmail.com'
or
try googling for 'Datastage 8.5 certification dumps 000-421'

Question { 4193 }

Tell me the syntax of Configuration file?

Answer

below is the Syntax for the 1 node configuration:
{
node "node1"
{
fastname ""
pools ""
resource disk " get saved>" {pools ""}
resource scratchdisk " SORT etc operations>" {pools ""}
}

}

Is This Answer Correct ?

7 Yes

1 No

Question { 19604 }

How you Remove the Dataset in Unix?

Answer

Syntax:
Orchadmin delete | del | rm [options] dataset_Name1 …
sataset_NameN
EX:
a. Orchadmin delete EMP1.ds EMP2.ds EMP3.ds
b. Orchadmin rm EMP*.ds

Question { IBM, 16943 }

i want send my all duplicate record one tar and all uniq
records one target how we will perfome explain
example:
input data
eid
251
251
456
456
951
985
out put/target1
251
251
456
456
out put/target2
951
985
how we will bring

Answer

According his logic: from aggregator stage: the output is
this: 251,2
456,2
951,1
985,1
the main data is:
251
251
456
456
951
985

If you join these two links then the output will be:
251,2
251,2
456,2
456,2
951,1
985,1

Then your are specifying that count=1 then you get the
unique records. means YOU get: 951,985


in another link count<>1 means YOU get: 251,
251,
456,
456

this is our desired out put, our out put.
the logic explained by Shiva is correct.

Question { TCS, 42943 }

what is the exact difference between dataset and fileset in
datastage?

Answer

DataSet:
1. The fundamental concept of the Orchestrate
framework is the Data Set. Data Sets are the inputs and
outputs of Orchestrate operators.
2. As a concept a Data Set is like a database table,
in so far as it is a collection of identically-defined
rows. It is the only structure on which Orchestrate
operators operate. Each operator( i.e., stage) accepts
input from one Data Set and sends its output to another
Data Set.
3. A Data Set exists on all the processing nodes
defined for the job that is currently processing it. That
subset of rows in a Data Set that are located on a single
processing node is referred to as a "partition" of the Data
Set. Technically, a partition is a subset of the rows in a
Data Set (or File Set) earmarked for processing on the same
processing node.
4. A control file is associated with each data set.
The control file contains the record schema that defines
the row structure (effectively its column definitions).
5. Within a Data Set data are stored in internal, or
machine-compatible format.

FileSet:
1. It allows you to read data from or write data to a
file set.
2. The stage can have a single input link, a single
output link and a single reject link.
3. It only executes in parallel mode.
4. The data files and the file that lists them are
called a file set. This capability is useful because some
operating systems impose a 2 GB limit on the size of a file
and you need to distribute files among nodes to prevent
overruns.
5. Only advantage of using fileset over a sequential
file is "it preserves partitioning scheme"

A dataset is a file/stage where the data can be read
directly by the DataStage, whereas a file set needs to be
converted into DataStage readable format (which happens
internally).

In simple words the data from the DataSet can be read
faster than from FileSet.

Question { 6090 }

What is the difference between lookup and sparse lookup?

Answer

Normal lookup will provide data for an in-memory look up
whereas Sparse lookup will access the database directly.
Normal might provide poor performance when the reference
data is huge as it has to load large data into memory.
In such scenarios a join stage may work better(It will put
the data onto an internal dataset file for each link,
and then do the join based on the data provided). If your
stream/source data is small then a sparse lookup is
suggested
as a row is retrieved from the database for each input row
instead of the full reference data being brought into
memory.
Sparse lookup sends individual SQL statement for every
incoming row (If stream/source data is huge you can imagine
the number
of times it has to hit DB and hence the down side on
performance). It can be used when you want to get the next
sequence
number from your database (Again expensive overhead on your
job as noted before). Also note that sparse lookup is only
available for DB2 and Oracle. Normal Lookup stage can have
multiple reference link but Sparse can only have one
reference
link. When normal is used it is a good practice to choose
less volume data as reference data.

Question { 8801 }

Source Like
department_no, employee_name
----------------------------
20, R
10, A
10, D
20, P
10, B
10, C
20, Q
20, S

and Output should be like this

department_no, employee_list
--------------------------------
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, A,B,C,D,P
20, A,B,C,D,P,Q
20, A,B,C,D,P,Q,R
20, A,B,C,D,P,Q,R,S

Answer

SRC---->Transformer--->TGT
we do the 'HASH' partitioning.

SetNull value to the Stage variable----->SV_List

In the transformer stage variables:
SV_List : Employee_Name ----->SV_List (Concatenating Employee
Names)

In the Column Derivation:
SV_list------->Employee_Name