Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    23

    Unanswered: Incomplete resultset

    The following SQL should return 18 records

    select * from luxproject.v1_read_acct_positions
    where trunc(calen_dt) = '08 feb 2005'
    and trim(ssb_fund_num) = 'SDEC'

    When I run it on my PC - either using DB Artisan or through an application which uses BDE to connect to the database I get 18, but when I run the SQL on the application server I only get 1 record.

    This was happening on my PC and I amended my BDE settings and the problem was resolved. However the server BDE settings match mine now and still the SQL does not work. In addition even when the SQL is run through SQL PLUS (which does not use BDE) only 1 record is returned.

    Has anyone seen this behaviour before... is there anything that causes an incomplete record set to be returned without any error message. The recordset is consistently wrong when the SQL is run on the server where as it is always right on my PC

    many thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I suspect that the NLS_Date format is different on the server and your workstation. Change the query to the following and try running it.

    select * from luxproject.v1_read_acct_positions
    where trunc(calen_dt) = to_date('08 feb 2005','dd mon yyyy')
    and trim(ssb_fund_num) = 'SDEC'
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Mar 2004
    Posts
    23
    Thanks - I checked this but the problem is that some but not all of the data is being returned for the date I specify so I don't think that it is a problem with the formatting. Some of the data that meets the where conditions are returned but not all.

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Some query-tools format the date for you, some don't. So run the same query with date formatting in all your query tools to ensure you are indeed looking at the same thing.

    try this query, and see what the differences are in the rows returned

    Code:
    SELECT to_char(calen_dt,'mm/dd/yyyy hh24:mi:ss') 
    FROM    luxproject.v1_read_acct_positions
    WHERE  trunc(calen_dt) = to_date('02/08/2005','mm/dd/yyyy')
        AND  trim(ssb_fund_num) = 'SDEC'
    also, check that ssb_fund_num is UPPER SDEC in all cases.

    HIH

Posting Permissions

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