Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Posts
    18

    Unanswered: Simple SQL Query Question

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jan 2005
    Posts
    18
    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!!

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jan 2005
    Posts
    191
    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

  6. #6
    Join Date
    Jan 2005
    Posts
    18
    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??

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  8. #8
    Join Date
    Jan 2005
    Posts
    18
    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.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •