Hi Friends,
I have a query that returns results like this:

col1**********col2********col3*******col4
xx---------------- -----------yy------------zz
xx---------------- -----------yy------------zz
xx---------------- -----------yy------------zz
...
....
Now to get column 2 values,I have to again run a query on the same table which should take in col1,col3 and col4 values as parameters.Can someone please tell me how to accomplish this....i wud really appreciate that.My first query is somewhat like this:

select col1,'' as col2,col3,col4 from table1 where col1 between 1 and 100 group by col1,col3,col4;

Thanking you in advance.

Could you please be more specific? Please list the DDL for the tables involved and what you expect to get back from your query.

Andy

Thanks A.R winner,
Unfortunately I dont have access to the DDL.What i am tryin to do is a simple self join.I get column1,column3 and column4 values and then I want to get the column 2 values depending upon the three values I have.Something like this:

select f.col1,sum(f.col2) as col2,col3,col4 from table1 as f ,table1 as f1 where f.col1 between 1 and 100 and f.col2 in ('USD','CAD') and f1.col3=f.col3 and f1.col1=f.col1 and f1.col4=f.col4 group by f.col1,f.col3,f.col4;

Help me if you can!!

YOu need to give me something to go on. Either you need to describe what the input is (table definition) and what you want to get out, or describe in detail what you are trying to accomplish. Giving me SQL, and expecting me to help you with it without knowing your intent, is fruitless.

Andy

Reading the tea leaves, I suspect that something like:

select col1, col2, col3, col4
from (select col1, substr(colx,1,10) as col2
, substr(colx,11,10) as col3
, substr(colx,21,10) as col4
from sometable
where col1 between 1 and 100) as tx

might give some clues. Of course the grouping seems rather strange

James Campbell

Thanks for your help A.R.Winner and JaCampbell,
I have the solution implemented in Java,but i know for long terms it's not feasible as it puts a considerable load on the network,so i was seeking for some sql help..so that instead of running a different query each time.I could work with one query.I guess some of you guys knows Java,so here's my method:


Connection co;
Statement firstStatement;
PreparedStatement secondStatement;
ResultSet firstResults, secondResults;

try{
co = getConnection();

firstStatement = co.createStatement();
secondStatement = co.prepareStatement("SELECT col2 FROM sometable WHERE xx=? AND yy=? AND zz=?");
firstResults = firstStatement.executeQuery("SELECT xx, yy, zz FROM sometable");


while( firstResults.next() )
{

int xx = firstResults.getInt("xx");
int yy = firstResults.getInt("yy");
int zz = firstResults.getInt("zz");


secondStatement.setInt( 1, xx );
secondStatement.setInt( 2, yy );
secondStatement.setInt( 3, zz );

secondResults = secondStatement.executeQuery();

if( secondResults.next() )
{

int col2Value = secondResults.getInt("col2");
doSomething( xx, col2Value, yy, zz);
}
else
{
// handle the fact that the data did not exist }
}
} catch( SQLException sqle ){
// do something appropriate
}
finally{
try {
if( secondResults!=null ) secondResults.close();
if( firstResults!=null ) firstResults.close();
if( secondStatement!=null ) secondStatement.close();
if( firstStatement!=null ) firstStatement.close();
if( co!=null ) co.close();
}
catch( SQLException sqle )
{
// can't clean up db connection, do something
}
}

Thanks..any suggestions??

From what I can gather, with just the SQL and still not knowing your intent,
you do not need 2 sql statements. You just need a UDF or expression to do what you want.

something like: select col2,xx,yy,zz,myUDF(xx,col2,yy,zz) from sometable

Andy

Thanks A.R.Winner,
After going thru some udf documentation,i think i can write a udf.Just needed to ask you that can i have a sql-query inside a udf like this:

CREATE FUNCTION myudf(parameters) RETURN somevalue
LANGUAGE SQL
BEGIN
select * from sometable where xx=parameter1;
END

Can i do this sort of thing,please help me.

Yes,
It will look like this:

CREATE FUNCTION MyUDF(parm1 INT, parm1 varchar(255))
RETURNS VARCHAR(255) LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
begin atomic
DECLARE outtext varcvhar(255);

set (outtext) = (select somevalue from sometable where somcolumn = parm1);
return outtext;
end @

Andy