OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Problem (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Problem [message #604353]

Sun, 29 December 2013 04:57

vgupta_2002
Messages: 3
Registered: December 2013

Junior Member

Hi,

My requirement is that while writing FIFO logic using a single query, if we are not able to fulfill the whole order quantity, the rows should not be inserted into a temporary table

Is there any way in SQL (only in SQL), where I should be able to insert all the records only based on some column in one of the row.

Basically, if my "last row" does not have "quantity remaining" as zero, i should not insert any of the records.

Regards

Report message to a moderator

Re: SQL Problem [message #604354 is a reply to message #604353]

Sun, 29 December 2013 05:05

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not clear.
Put an example.

Report message to a moderator

Re: SQL Problem [message #604362 is a reply to message #604353]

Sun, 29 December 2013 06:41

vgupta_2002
Messages: 3
Registered: December 2013

Junior Member

Hi,

Sorry that I was not clear.

Here is an example:

Adj Hrs. 100

Start Date End Date hrs
01-jan-13 31-jan-13 25
01-feb-13 28-feb-13 30
01-mar-13 31-mar-13 45

I have to write a query to adjust "adj hrs" against the rows in the table. In this example, i will be able to adjust 100 hrs against three rows as i have 100 hrs in the three rows. So, my query shd Insert all these records in a temp table.

But suppose, adj hrs are 120, then it will not be possible to adjust these against the three rows as i am 20 hrs short. So, my query shd not insert any of the rows in the temp table

I am trying to achieve this in single query although i have already written PL/SQL code for this.

Regards

Report message to a moderator

Re: SQL Problem [message #604363 is a reply to message #604362]

Sun, 29 December 2013 07:12

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

So you want to insert only if sum of hrs = 100? Then

SQL> select  *
  2    from  tbl
  3  /

START_DT  END_DT           HRS
--------- --------- ----------
01-JAN-13 31-JAN-13         25
01-FEB-13 28-FEB-13         30
01-MAR-13 31-MAR-13         45

SQL> select  *
  2    from  tbl_tmp
  3  /

no rows selected

SQL> insert
  2    into tbl_tmp
  3    with t as (
  4               select  tbl.*,
  5                       sum(hrs) over() total_hrs
  6                 from  tbl
  7              )
  8    select  start_dt,
  9            end_dt,
 10            hrs
 11      from  t
 12      where total_hrs = 100
 13  /

3 rows created.

SQL> select  *
  2    from  tbl_tmp
  3  /

START_DT  END_DT           HRS
--------- --------- ----------
01-JAN-13 31-JAN-13         25
01-FEB-13 28-FEB-13         30
01-MAR-13 31-MAR-13         45

SQL>

SY.

Report message to a moderator

Re: SQL Problem [message #604369 is a reply to message #604362]

Sun, 29 December 2013 08:36

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And if "adj hrs" is 90 what should return the query?
Same question for 55 and 70?

Report message to a moderator

Re: SQL Problem [message #604372 is a reply to message #604353]

Sun, 29 December 2013 10:21

vgupta_2002
Messages: 3
Registered: December 2013

Junior Member

Hi Michel,

In example, for anything other than 100, it should not return or insert any row.

There will be more rows in the table that I have given in the example and "Adj Hrs" can overlap the rows depicted, in which case I have to pro-rate the hrs.

Regards

Report message to a moderator

Re: SQL Problem [message #604373 is a reply to message #604372]

Sun, 29 December 2013 11:19

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I have to pro-rate the hrs.

What does this mean?
Can you post an example (and e test case) that cover ALL the cases and explain the result for different "adj hrs".

Report message to a moderator

Previous Topic:	move data from one table to another table
Next Topic:	"with" clause Vs. multiple views

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:53:32 CDT 2024