SQL Server Performance

If no record found then .. What's default output of ....

Discussion in 'General Developer Questions' started by nitingautam, Jan 4, 2009.

  1. nitingautam New Member

    I am using output of SQL Server subquery in DB2 query IN clause.
    I have a hurdle, if subquery found no record, then what it will return?

    Beacuse the output of this query will be input of IN clause in DB2 query, what if nothing is returned by the SQLServer query?

    Example:
    SQL Query
    select CLNT_ID from CLNT_ACS where INTN_ID=12
    Output:
    1234

    DB2 Query
    SELECT CLNT_ID, CLNT_NM from CLNT WHERE CLNT_ID IN (1234) ORDER BY CLNT_NM
    nitingautam, Jan 4, 2009
    #2

  2. Adriaan New Member

    If this is within a SQL Server script, you can use OPENQUERY or OPENDATASOURCE (both with an alias) in your FROM clause, and just JOIN with the local table.
    If you are doing this from a client application that makes separate connections to the DB2 and SQL Server databases, you will either see a NULL on the CLNT_ID column, or no current record for the recordset object.
    Adriaan, Jan 5, 2009
    #3

  3. FrankKalis Moderator

    In which context are you using the queries?
    If the SQL Server query can't find matching row(s), an empty resultset will be returned. You probably can rewrite the DB2 query to a JOIN or an EXISTS.
    FrankKalis, Jan 5, 2009
    #4

  4. satya Moderator

    Welcome to the forums.
    Are you executing this query in a linked server context?
    Refer to what Adriaan is suggesting and in this case you have to more clear about what kind of result you are expecting within this query execution.
    satya, Jan 7, 2009
    #5

  5. nitingautam New Member

    I added a dummy value in IN clause so that this query never fails if I don't have a any data through subquery
    SELECT CLNT_ID, CLNT_NM from CLNT WHERE CLNT_ID IN (1234,9999999) ORDER BY CLNT_NM
    If nothing is returned by SQL Server subquery then DB2 query will be
    SELECT CLNT_ID, CLNT_NM from CLNT WHERE CLNT_ID IN (9999999) ORDER BY CLNT_NM
    and this works for me.
    nitingautam, Jan 7, 2009
    #6

  6. Adriaan New Member

    Dummy values are not a very good option. The CLNT table may grow to the point where CLNT_ID actually reaches that stop value of 9,999,999. Even if that is unlikely, it is not inconceivable. (Check out the real-life stories on http://www.thedailywtf.com about "unfortunate coding".)
    Most database platforms and programming languages have standard solutions for detecting and handling NULL values or empty recordsets.
    Please show us some of your code so we can see how you connect to the databases, and then handle the output of the first query so it ends up in the second query.
    Adriaan, Jan 7, 2009
    #7

Share This Page