0

I created a simple application for getting some data from Oracle. The application is written in Lazarus and uses ZeosLib and Oracle client (oci.dll) for connecting to the server. Oracle admin prepared me a View for getting my data. i don't knot how is it build. When I get run the script using field IDWYDZ then about 200 rows is returned (not all), when I comment out the field IDWYDZ or when I replace it with empty field ie. '' as IDWYDZ then I get all 500 rows. Why including or excluding column affects the number of rows returned?

SELECT
    SKROT as IDPROD
    , substr( REPLACE( LTRIM(RTRIM(CAST(NAZWA AS NVARCHAR2(100)))), '''', '`'), 1, 49) as NAZPROD
    , substr( REPLACE( LTRIM(RTRIM(CAST(OPIS1 AS NVARCHAR2(255)))), '''', '`'), 1, 49) as OPIS1
    , substr( REPLACE( LTRIM(RTRIM(CAST(OPIS2 AS NVARCHAR2(255)))), '''', '`'), 1, 49) as OPIS2
    , substr( REPLACE( LTRIM(RTRIM(CAST(PREFIX AS NVARCHAR2(255)))), '''', '`'), 1, 49) as OPIS3
    , substr( LTRIM(RTRIM(CAST(OPIS1 AS NVARCHAR2(100)))), 1, 30) as IDGRUPY
    , SUBSTR(JM,1, 10) AS JM
    , CENA_SPRZ AS CENA
    , EAN AS EAN13
    , 'R' as TYPBOM
    , case when SKROT<'140000' then 'M' else 'K' end as TYPPROD
    --, FD_DZIAL AS IDWYDZ
FROM
    ACME.EXPORT_VIEW_KAS
WHERE
    DATA_MODYF>=(CURRENT_DATE-7) AND
    LENGTH(SKROT)>0
order by 
    IDPROD
Stefan Batory
  • 1
  • 2
    No idea; doesn't make much sense. FD_DZIAL, as part of the SELECT column list, shouldn't affect number of rows returned. It might have impact if it was part of the WHERE clause, but - it is not ... – Littlefoot Dec 11 '18 at 09:59
  • where is the column IDWYDZ and why yor query has datatype in it??? i didnt understand you query!!!!!!!!! – Nikhil S Dec 11 '18 at 10:22
  • Yes this is weird, which usually means there's some peculiarity in your environment which we can't diagnose. One random idea: FD_DZIAL is a big column such as a CLOB and you're hitting a size (packet?) limit in your libraries. I suggest you enlist your DBA to help you understand what's going on. – APC Dec 11 '18 at 10:23
  • Maybe you have some exception during the fetching of records? For example there is a value in column FD_DZIAL, which can not be converted to NUMBER. – RGruca Dec 11 '18 at 10:24
  • what happens if you remove SQL lines between IDPROD and IDWYDZ to display only IDPORD and IDWYDZ columns ? If you have same problem, can you remove all these lines from your post ? – schlebe Dec 11 '18 at 10:24
  • @nikhilsugandh - it's fairly common to use `cast()` to change the projected datatype of a column in a view. Although I agree it is odd to see the casting nested inside of so many other string manipulation functions. I would expect the `cast()` to be the outermost call so the projection is accurate: why cast a column to NVARCHAR2(255) if we're only going to project a substring of 49 characters? – APC Dec 11 '18 at 10:30
  • @APC i have seen ltrim replace rtrim but cast didnt use it so had a problem anyways thanks for expalantion!! – Nikhil S Dec 11 '18 at 10:33
  • It doesn't really make too much sense, but if it's happening for real, I would suggest to retrieve the execution plan of both queries and compare them. Can you post both of them (with and without the extra column)? – The Impaler Dec 11 '18 at 11:42
  • I reduced the query to one col only. The number of rows returned is now the same. The only think that comes to mind is that in data must be any suspicious character. The main program which uses Oracle db is over 20 year old console application. Probably it uses or used some time ago any non standard code page like Mazovia and those characters are still in database disturbing the output. DB admin ran my original query today using Oracle tools and he said that in both cases the number of returned rows was the same. I will try with collation. Maybe the problem is in ZeosLib during translation. – Stefan Batory Dec 11 '18 at 17:44
  • @APC. I share the view that casting the column to 255 chars and than reducing it to 49 looks strange but when I try to execute substr without cast insight of it I get an error for some values that value in column was truncated and the result is empty. – Stefan Batory Dec 11 '18 at 18:07

0 Answers0