Reading Multiple Values for a SQL Query

[bapajju]

Hello,

I have a Query (In OraOCI8) that takes a single value to provide the result. For Example:
Select * from EMP where EMP_ID=1001.

But in EMP Table I have 100000 employees.

How to get the result set from the same query for all the employees. Please note that I can not take out the WHERE clause as I a using some Oracle function in the query that needs single value output from the query.

After pulling the results for each employee we need to perform some transformation in Transformer and put it in another Oracle table.

It will be of great help if someone can help in resolving this.

Thanks in advance
Amitav

[Sunshine2323]

Hi,


If you specify the where clause it willl only return those rows that satisfy the condition specified in the where clause.
It will not return all the rows.

You can try the UNION Operation

Select * from EMP where EMP_ID=1001
UNION
Select * from EMP

Can you kindly elaborate more on your requirement and what is it that you are trying to achieve?

[rasi]

Hi

Your question is not clear enough to respond. Could you explain it futher with some example

[bapajju]

I have a SQL statement that has a Where Clause. Which means it will return the result for the matching value in the where clause.

Ex
select * from Sale
Where
SALE_TSP >= nvl((SELECT max(begin_effective_dt)
FROM sale_history
WHERE sale_type_code in ('5','6','7','8','A')
AND invoice_id = (
SELECT invoice_id
FROM sale_detail
WHERE batch_id = 1001)
Now I have 100000 batch_id in sale_detail tables.

How do I pass the results to th target for all the batch_ids.

Hope now I am making it more clear.

Hi,


If you specify the where clause it willl only return those rows that satisfy the condition specified in the where clause.
It will not return all the rows.

You can try the UNION Operation

Select * from EMP where EMP_ID=1001
UNION
Select * from EMP

Can you kindly elaborate more on your requirement and what is it that you are trying to achieve?

[sanjay]

Hi

Question is not clear ????


Sanjay

I have a SQL statement that has a Where Clause. Which means it will return the result for the matching value in the where clause.

Ex
select * from Sale
Where
SALE_TSP >= nvl((SELECT max(begin_effective_dt)
FROM sale_history
WHERE sale_type_code in ('5','6','7','8','A')
AND invoice_id = (
SELECT invoice_id
FROM sale_detail
WHERE batch_id = 1001)
Now I have 100000 batch_id in sale_detail tables.

How do I pass the results to th target for all the batch_ids.

Hope now I am making it more clear.

Hi,


If you specify the where clause it willl only return those rows that satisfy the condition specified in the where clause.
It will not return all the rows.

You can try the UNION Operation

Select * from EMP where EMP_ID=1001
UNION
Select * from EMP

Can you kindly elaborate more on your requirement and what is it that you are trying to achieve?

[balajisr]

Hi,


If you specify the where clause it willl only return those rows that satisfy the condition specified in the where clause.
It will not return all the rows.

You can try the UNION Operation

Select * from EMP where EMP_ID=1001
UNION
Select * from EMP

Can you kindly elaborate more on your requirement and what is it that you are trying to achieve?

How about the below query

Select * from EMP where 1=1

I am not very clear about your requirement

Can you tell me in detail as to why you need where clause to be present when you need to get all rows?

--Balaji S.R

[kumar_s]

Code: Select all

select * from Sale 
Where 
SALE_TSP >= nvl((SELECT max(begin_effective_dt) 
FROM sale_history 
WHERE sale_type_code in ('5','6','7','8','A') 
AND invoice_id = ( 
SELECT invoice_id 
FROM sale_detail 
WHERE batch_id in (select batch_id from sale_detail ) )

[kumar_s]

But i still dont understand why you need the where condition if you want all the rows to be selected.

[bapajju]

I need the where clause because somewhere down the line I have to use "connect by prior" function to travese a hierarchy. I can not traverse the hierachy if I do not specify a single value in the WHERE clause.

But i still dont understand why you need the where condition if you want all the rows to be selected.

[ray.wurlod]

If you're doing this in DataStage surely the form you require is

Code: Select all

Select * from EMP where EMP_ID = :1

However, this does not traverse a hierarchy; it merely performs a lookup based on the current value for EMP_ID as supplied from the job's stream of data or some expression based on that. DataStage itself does not provide a mechanism for traversing a hierarchy; you may need to create a PL/SQL script or even a DataStage routine to do that.