Forum Stats

  • 3,874,172 Users
  • 2,266,677 Discussions
  • 7,911,753 Comments

Discussions

SQL Developer and TOAD not returning Records

User_DBLDT
User_DBLDT Member Posts: 4 Green Ribbon
edited Oct 30, 2022 5:19PM in SQL & PL/SQL

When i run select * from per_all_people_f;

it is returning records from single Table but not when we run SQL Script which is executing from others PC(my Team members) with same network same version of Software's we are using.


SELECT

  papf.full_name,  

  papf.employee_number,   

  to_char(aba.creation_date, 'DD-MON-RRRR')    absence_creation_date,

  abt.name                     absence_type,

  aba.date_start,

  aba.date_end,

  aba.absence_days

FROM

  per_absence_attendances   aba,

  per_abs_attendance_types_vl abt,

  per_abs_attendance_reasons  abr,

  pay_input_values_f_tl    ivtl,

  pay_input_values_f      iv,

  per_all_people_f       pep,

  per_all_people_f       pep2,

  ssp_maternities       hm,

  hr_lookups          hrl,

  hr_lookups          l,

  per_all_people_f       papf

WHERE

    aba.absence_attendance_type_id = abt.absence_attendance_type_id

  AND abt.input_value_id = iv.input_value_id (+)

  AND aba.authorising_person_id = pep.person_id (+)

  AND aba.replacement_person_id = pep2.person_id (+)

  AND aba.abs_attendance_reason_id = abr.abs_attendance_reason_id (+)

  AND abt.absence_category = hrl.lookup_code (+)

  AND hrl.lookup_type (+) = 'ABSENCE_CATEGORY'

  AND abr.name = l.lookup_code (+)

  AND l.lookup_type (+) = 'ABSENCE_REASON'

  AND aba.maternity_id = hm.maternity_id (+)

  AND nvl(aba.date_notification, sysdate) BETWEEN nvl(iv.effective_start_date, nvl(aba.date_notification, sysdate)) AND nvl(iv.

  effective_end_date, nvl(aba.date_notification, sysdate))

  AND nvl(aba.date_start, nvl(date_projected_start, sysdate)) BETWEEN nvl(pep.effective_start_date, nvl(aba.date_start, nvl(date_projected_start,

  sysdate))) AND nvl(pep.effective_end_date, nvl(aba.date_start, nvl(date_projected_start, sysdate)))

  AND nvl(aba.date_start, nvl(date_projected_start, sysdate)) BETWEEN nvl(pep2.effective_start_date, nvl(aba.date_start, nvl(date_projected_start,

  sysdate))) AND nvl(pep2.effective_end_date, nvl(aba.date_start, nvl(date_projected_start, sysdate)))

  AND iv.input_value_id = ivtl.input_value_id (+)

  AND decode(ivtl.input_value_id, NULL, '1', ivtl.language) = decode(ivtl.input_value_id, NULL, '1', userenv('LANG'))

  AND aba.person_id = papf.person_id

  AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

  AND to_date(aba.date_start) between '01-jul-2022' and '31-jul-2022'   ;

«1

Answers

  • User_DBLDT
    User_DBLDT Member Posts: 4 Green Ribbon

    Please help me if anybody come across with this issue

  • Mike Kutz
    Mike Kutz Member Posts: 6,253 Gold Crown

    Does it return rows while running as a script from YOUR pc?

    Are you using the same login credentials and SAME SERVER? (This is the most common mistake. Different set of rows are returned because you're actually connecting to different databases)

    If there are RLS Rules (or equivalent: VPD, RAS, Views with SYS_CONTEXT) then this could very well be the expected results. Most likely because your session isn't initialized correctly. This could be Missing .logon.sql or horribly written Logon Trigger

  • mathguy
    mathguy Member Posts: 10,895 Black Diamond

    The most common mistake, I believe, is not a "different server".

    Rather, it is not committing one's transaction. If you created the tables and inserted data, but you didn't commit, then only you can see that data - and only from the same session. If it's a different user, OR if it is yourself from a different session, the table will appear empty - until you go back to the session where you inserted the data and you commit the transaction.

    It happens to me sometimes - I want to test something, I create a table, I insert some data, then I test everything in SQL Developer and everything is fine. But - since there are some very annoying differences between SQL Developer and SQL*Plus ("running queries from my PC"), I test from SQL*Plus also - logged in as me, with the same password, to the same server and the same pluggable database as from SQL Developer. I still don't see any data in the table - because I forgot to COMMIT in SQL Developer, and that is a different session (even if for the same user). Make sure you aren't making the same mistake as I am!

  • User_3ABCE
    User_3ABCE Member Posts: 192 Silver Badge
     AND decode(ivtl.input_value_id, NULL, '1', ivtl.language) = decode(ivtl.input_value_id, NULL, '1', userenv('LANG'))
    
    Christian.Shay -Oracle
  • BluShadow
    BluShadow Member, Moderator Posts: 42,555 Red Diamond
    edited Oct 31, 2022 8:58AM

    As User_3ABCE points out, it could be the language that differs, so you need to be sure that that is correct for what you're trying to pick out of your data (if that check is even needed)

    When I ran your query (using ANSI syntax so I could see what joins were actually being made, and correcting your strings so that they are actually DATE datatypes being compared against)...

    select papf.full_name
          ,papf.employee_number
          ,to_char(aba.creation_date, 'DD-MON-YYYY') as absence_creation_date
          ,abt.name absence_type
          ,aba.date_start
          ,aba.date_end
          ,aba.absence_days
    from   per_absence_attendances aba
                      join per_abs_attendance_types_vl abt  on (aba.absence_attendance_type_id = abt.absence_attendance_type_id)
           left outer join per_abs_attendance_reasons  abr  on (aba.abs_attendance_reason_id = abr.abs_attendance_reason_id)
           left outer join pay_input_values_f          iv   on (abt.input_value_id = iv.input_value_id)
           left outer join pay_input_values_f_tl       ivtl on (iv.input_value_id = ivtl.input_value_id)
           left outer join per_all_people_f            pep  on (aba.authorising_person_id = pep.person_id)
           left outer join per_all_people_f            pep2 on (aba.replacement_person_id = pep2.person_id)
           left outer join ssp_maternities             hm   on (aba.maternity_id = hm.maternity_id)
           left outer join hr_lookups                  hrl  on (abt.absence_category = hrl.lookup_code and hrl.lookup_type = 'ABSENCE_CATEGORY')
           left outer join hr_lookups                  l    on (abr.name = l.lookup_code and l.lookup_type = 'ABSENCE_REASON')
                      join per_all_people_f            papf on (aba.person_id = papf.person_id)
    where  coalesce(aba.date_notification, sysdate) between coalesce(iv.effective_start_date, aba.date_notification, sysdate)
                                                        and coalesce(iv.effective_end_date, aba.date_notification, sysdate)
    and    coalesce(aba.date_start, aba.date_projected_start, sysdate) between coalesce(pep.effective_start_date, aba.date_start, aba.date_projected_start, sysdate)
                                                                           and coalesce(pep.effective_end_date, aba.date_start, aba.date_projected_start, sysdate)
    and    coalesce(aba.date_start, aba.date_projected_start, sysdate) between coalesce(pep2.effective_start_date, aba.date_start, aba.date_projected_start, sysdate)
                                                                           and coalesce(pep2.effective_end_date, aba.date_start, aba.date_projected_start, sysdate)
    and    decode(ivtl.input_value_id, null, '1', ivtl.language) = decode(ivtl.input_value_id, null, '1', userenv('LANG'))
    and    sysdate between papf.effective_start_date and papf.effective_end_date
    and    to_date(aba.date_start) between date '2022-07-01' and date '2022-07-31';
    

    I likewise got no data from our HR system.

    The thing stopping it on our HR system was the JOIN to per_abs_attendance_types_vl (abt). If I turn that to a LEFT OUTER JOIN instead, then I got data back.

    I'm not overly familiar with that data though, so whether that is what is needed or not, I don't know

  • BluShadow
    BluShadow Member, Moderator Posts: 42,555 Red Diamond
    edited Oct 31, 2022 11:52AM

    Or... it works if I change that per_abs_attendance_types_vl table to actually use the per_absence_attendance_types table instead...

    join per_absence_attendance_types abt  on (aba.absence_attendance_type_id = abt.absence_attendance_type_id)
    

    Working query for me...

    select papf.full_name
          ,papf.employee_number
          ,to_char(aba.creation_date, 'DD-MON-YYYY') as absence_creation_date
          ,abt.name absence_type
          ,aba.date_start
          ,aba.date_end
          ,aba.absence_days
    from   per_absence_attendances aba
                      join per_absence_attendance_types abt  on (aba.absence_attendance_type_id = abt.absence_attendance_type_id)
           left outer join per_abs_attendance_reasons   abr  on (aba.abs_attendance_reason_id = abr.abs_attendance_reason_id)
           left outer join pay_input_values_f           iv   on (abt.input_value_id = iv.input_value_id)
           left outer join pay_input_values_f_tl        ivtl on (iv.input_value_id = ivtl.input_value_id)
           left outer join per_all_people_f             pep  on (aba.authorising_person_id = pep.person_id)
           left outer join per_all_people_f             pep2 on (aba.replacement_person_id = pep2.person_id)
           left outer join ssp_maternities              hm   on (aba.maternity_id = hm.maternity_id)
           left outer join hr_lookups                   hrl  on (abt.absence_category = hrl.lookup_code and hrl.lookup_type = 'ABSENCE_CATEGORY')
           left outer join hr_lookups                   l    on (abr.name = l.lookup_code and l.lookup_type = 'ABSENCE_REASON')
                      join per_all_people_f             papf on (aba.person_id = papf.person_id)
    where  coalesce(aba.date_notification, sysdate) between coalesce(iv.effective_start_date, aba.date_notification, sysdate)
                                                        and coalesce(iv.effective_end_date, aba.date_notification, sysdate)
    and    coalesce(aba.date_start, aba.date_projected_start, sysdate) between coalesce(pep.effective_start_date, aba.date_start, aba.date_projected_start, sysdate)
                                                                           and coalesce(pep.effective_end_date, aba.date_start, aba.date_projected_start, sysdate)
    and    coalesce(aba.date_start, aba.date_projected_start, sysdate) between coalesce(pep2.effective_start_date, aba.date_start, aba.date_projected_start, sysdate)
                                                                           and coalesce(pep2.effective_end_date, aba.date_start, aba.date_projected_start, sysdate)
    and    decode(ivtl.input_value_id, null, '1', ivtl.language) = decode(ivtl.input_value_id, null, '1', userenv('LANG'))
    and    sysdate between papf.effective_start_date and papf.effective_end_date
    --and    to_date(aba.date_start) between date '2022-07-01' and date '2022-07-31';
    and    aba.date_start between date '2022-07-01' and date '2022-07-31';
    
    

    Edit: Corrected following Solomon's spot below.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,937 Red Diamond

    Blu, you spotted bad use of string as date but missed more important bad code:

    to_date(aba.date_start) 
    

    Look what can happen:

    SQL> show parameter nls_date_format
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    nls_date_format                      string      dd-mon-yy
    
    SQL> select  to_char(hiredate,'mm/dd/yyyy') correct_date,
      2          to_char(to_date(hiredate),'mm/dd/yyyy') incorrect_date
      3    from  emp
      4    where ename = 'KING'
      5  /
    
    CORRECT_DATE INCORRECT_DATE
    ------------ --------------
    11/17/1981   11/17/2081
    
    SQL>
    

    So OP should use:

    aba.date_start between date '2022-07-01' and date '2022-07-31'
    

    SY.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,555 Red Diamond

    Indeed Solomon.. I did see it but, without checking my actual HR schema, assumed it was one of those columns that's been stored as a string, but even then forgot to consider it would need a format string applied if that were the case.

    I've just checked the schema and indeed, date_start is already DATE, so you're correct.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,555 Red Diamond

    But Same Query and Toad and SQL Developers also same version using by my Colleagues.

    But does USERENV('LANG') return the same value for you and your colleagues?

    Are you and your colleagues actually connecting to the same database?

    There are many possible reasons why something works for one person and not for another, including the poor use of date strings, as local client sessions can cause dates to be used wrongly and all of a sudden the query may work by chance but be searching between two dates that are not an appropriate range for the data.

    If it doesn't work on your colleagues clients then you need to break it down step by step, start with the base table and start adding in each other table and condition separately, until you find where it breaks.