SQL & PL/SQL

Hi,

This looks almost identical to reply #12 in your earlier thread: Fetching Contract Employee information by Join the JOB_DTL,JOB_DTL1,IQN_BU Tables.

Please don't post the same question in multiple threads.  It's confusing for people trying to answer questions, and it's confusing for you, too, since you have to look in multiple places for answers.  Mark one of these threads as "Answered" before we go any farther.

The query you posted meets the requirements you posted.

1. It fetches contract employee information with HR_Status = 'A'. ( per_org = 'CWR' in PS_CTS_JOB_DTL1 table ).
2. It only returns rows where active contract employee Business_Unit value of 'PS_CTS_JOB_DTL' table is present in PS_CTS_IQN_BU table.
3. It handles the case were an employee has multiple consecutive rows with hr_status='A'.   (It may not handle them the way you want, but you haven't said what that way is.)

You apparently have other requirements you haven't posted yet.

Hi @"Frank Kulash",

Employee 733930 is active as CWR from 27-May-2019 to 03-Jun-2019.  and he is having one extra row with HR_STATUS = 'A' and effdt 02-Jun-2019. (there is a chances employee may have more than one row with HR_STATUS = 'A' between active and inactive.). And for each effdt row employee Business_unit also available in IQN BU setup table.

When i run the query with start date 01-JUN-2019 and end date 30-JUN-2019 Query is fetching two rows. 01-JUN-2019 to 03-JUN-2019 one row and 02-JUN-2019 to 03-JUN-2019 one row.

instead of fetching two rows i want to fetch only one row from 01-JUN-2019 to 03-JUN-2019.  Because he is active CWR from 01-JUN-2019 to 03-JUN-2019 .

Note :

1. when ever there is more than one row with HR_STATUS = 'A' between start and end dates, we need to check for each row business unit is available or not in the IQN BU table. If business unit is available then we need to skip that row.

in this case 27-MAY-2019 effdt row business unit valu is present in IQN BU table so we are fetching greatest value 01-JUN-2019 as start date.

For 02-JUN-2019 effdt row business unit also available in IQN BU table so we need to skip this row. ( but query is fetching this row )

2. If Business unit value is not available in IQN BU table then we need to treat row as HR_STATUS is inactive. ( This one is working fine in the Query)

similarly for Employee 733931 also it is fetching two rows for below data.

Query is fetching 26-JUN-2019 to 30-JUN-2019 one row and 28-JUN-2019 to 30-JUN-2019 second row.    For 28-JUN-2019 effdt row business unit value is available in IQN BU table so  i want to fetch only one from 26-JUN-2019 to 30-JUN-2019.

Hi @"Frank Kulash" , Could you please help me out on this.

Hi,

Sorry, I still don't understand why you want the results you posted.  The specific examples are good, but I need a general rule, such as "When there are two (or more) consecutive rows (in order by effdt) for the same emplid that all have hr_status='A', then only the first one counts.  The second (and later) consecutive 'A' rows can be ignored."

Hi @"Frank Kulash", Yes correct When there are two (or more) consecutive rows (in order by effdt) for the same emplid that all have hr_status='A',  and business unit also should be present in IQN BU table for all consecutive rows then only the first one counts.  The second (and later) consecutive 'A' rows can be ignored."   { If business unit is not available in IQN BU table for consecutive rows then we need to treat that row as inactive. Currently we are checking this thing we are validating in query }

Hi @"Frank Kulash", Thanks for your support, when i execute the above SQL i am getting below error. Please help me on this.

HI,

Vemula Muni wrote:

Hi [Frank Kulash](/people/Frank Kulash?customTheme=otn), Thanks for your support, when i execute the above SQL i am getting below error. Please help me on this.

That doesn't look like an Oracle error message.

IN and OUT parameters occur in PL/SQL procedures. Nothing in this thread before this has even mentioned PL/SQL. Are you doing something other than what is posted here?

I don't get any error when I run the query above in SQL*Plus (version 12.2.0.1.0). Do you?

What front end are you using when you get the error you posted? If it's detecting an error on line 1, then maybe that front end doesn't recognize WITH clauses (which have been part of Oracle since version 9.1, released in 2001).

The query you posted in your original message also started with WITH. Did it raise an error?

Hi @"Frank Kulash", I am using the below version SQL developer . till i executed all your quires in this and it is accepted WITH clauses. And i am not changing the query ( Executing as it is).

Hi,

Vemula Muni wrote:

Hi [Frank Kulash](/people/Frank Kulash?customTheme=otn), I am using the below version SQL developer . till i executed all your quires in this and it is accepted WITH clauses. And i am not changing the query ( Executing as it is).

So you're using SQL Developer version 1.5.5? That sounds kind of old.

The query in reply #6 runs (and returns the results you requested) when I try it in SQL Developer 4.1.4.21. It looks like you have a separate problem that involves SQL Developer, not SQL or PL/SQL. Start a new thread in the SQL Developer forum. When posting there, simplify the query as much as possible, so that it still causes the same error. That will also help isolate exactly what couases the problem, which will help if you can't solve the SQL Developer issue and need to find a different way to run the query

Or how about downloading a latest version from the internet and installing the same that is compatible with 12c ?

Regards,

Ranagal

Your screen shot of SQL Developer 1.5.5 running on Java JDK 1.8 is pretty stunning.  However you got it to work, you should at least have had to click through some dialog saying 1.5.5 does not support that version of Java.

And even getting that far would be pretty surprising.  Ditto for achieving a connection to Oracle 12c.  For me, this is a definite +1 on upgrading.

Hi @"Frank Kulash", Thanks for your kind support. Now this is working fine in SQL Developer 4.1.4.21. Once again Thanks a lot.

Just an additional comment, when using hard-coded dates, try to avoid using month names, use numbers instead.

This piece of code from your query would work in my environment:

TO_DATE ('01-JUN-2019', 'DD-MON-YYYY')

But if the month was August, you would have used AUG, and that's not a valid abbreviation for months in Spanish :-).

Since the conversions depends on the NLS settings of the sessions, you may find issues if any user uses another language... That may not be possible on some systems, but better prevent the issue than complain later!

You should use:

TO_DATE ('01-06-2019', 'DD-MM-YYYY')

And completely OUT OF TOPIC, but regarding the error you had on the older SQL Developer, I found a similar error, this time running the 19.2 version, with the following query:

CREATE TABLE Producto (

  ID NUMBER(10) NOT NULL,

  Marca VARCHAR2(100) NOT NULL,

  Nombre VARCHAR2(100) NOT NULL,

  Precio NUMBER(10,4) NOT NULL,

  CONSTRAINT PK\_Producto PRIMARY KEY (ID)

);

INSERT INTO Producto VALUES (1, 'Sony', 'TV LED 40"', 20000);

INSERT INTO Producto VALUES (2, 'Philips', 'TV LED 32"', 15000 );

INSERT INTO Producto VALUES (3, 'Motorola', 'Moto Z4', 25000);

SELECT JSON_OBJECT('ID':id, 'Producto':nombre) AS json_data_new

FROM Producto p;

This works perfectly on Live SQL

But fails on SQL Developer 19.2!

Ir's the same error message (in Spanish since I had the UI configured in Spanish for a lecture). I will follow this issue up on the SQL Developer forum :-)

Hi @"Frank Kulash", Could you please Explain how this query will work.

Hi,

Vemula Muni wrote:
Hi Frank Kulash, Could you please Explain how this query will work.

Can you be more specific?  What, in particular, do you have trouble understanding?  I could waste a lot of time (both mine and yours) by explaining things you already know.

To understand a query that uses WITH sub-queries, start by looking at what each sub-query does.  For example, in the query from reply #6, you might run just the first sub-query, like this:

WITH    parameters    AS
(
    SELECT  TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') AS pstartdt  -- Always use 4 digits for years
    ,       TO_DATE ('30-JUN-2019', 'DD-MON-YYYY') AS penddt
    ,       'CWR'                                  AS per_org
    FROM    dual
)
SELECT  *
FROM    parameters;

In this case, the first sub-query is so simple you probably won't need to do it, but this gives you the idea.

When you understand what this does, then try running the next sub-query, got_business_unit, the same way.  (Since it references the first sub-query, you'll need to include that, too.)  If you can't understand what  a sub-query does, then ask a specific question about it.  For example:

"When I run … I get the results ….  Why don't I get … instead?  The SQL manual, under … says …; doesn't that mean …?"

If there are unfamiliar features (maybe OUTER APPLY or MATCH_RECOGNIZE) or functions, search the documentation and sites like this for simple examples of how they work.  If you see examples of specific features that you can't understand, then you might want to start a separate thread asking about that example works.

In this problem, we want to know if the period given in the parameters overlaps with another period (the period between an 'A' row and an 'I' row).  How can you tell if two periods overlap?  They DO NOT overlap if (and only if) one period ends before the other begins, so they DO overlap if (and only if) each period begins before the other ends, so you need to test two conditions.  In reply #6, this is tricky, because one of those tests

d1.effdt       <= p.enddt

is done in the got_business_unit sub-query, but the other test

NVL (edt - 1, penddt)  > pstartdt

is done much later, at the very end of the main query.