Home » SQL & PL/SQL » SQL & PL/SQL » How to Display as well as Insert in a single sql statement
How to Display as well as Insert in a single sql statement [message #219744] Thu, 15 February 2007 17:00 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
I am currently generating a report using group by clause and left outer joins and it works. The report has a username and a date column among others.

However the requirement is once the report should display the same data twice.

The Constraints are
a) The reporting table cannot be modified to include a timestamp column to track the status as it is created by the application.
b) To use CTAS to insert data into another table and report from that table would not be feasible as main table is very huge.

What I would like to do is use this date and username to track into a separate table. Is it possible to SELECT as well as INSERT in a single statement, whereby I need not repeat the GrOUP BY AND OUTER JOINS again..

Regards,
Re: How to Display as well as Insert in a single sql statement [message #219746 is a reply to message #219744] Thu, 15 February 2007 17:11 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I know, it is not possible to select and insert data in a single statement.

But, if you have to display the same records twice, you could use union:
SQL> select user, sysdate from dual
  2  union all
  3  select user, sysdate from dual;

USER                           SYSDATE
------------------------------ --------
SCOTT                          15.02.07
SCOTT                          15.02.07

SQL>
Re: How to Display as well as Insert in a single sql statement [message #219747 is a reply to message #219744] Thu, 15 February 2007 17:13 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Does the RETURNING clause help you any?
Re: How to Display as well as Insert in a single sql statement [message #219749 is a reply to message #219746] Thu, 15 February 2007 17:14 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
I am sorry, it should have read
" the requirement is report should not display the same data twice."

Regards,
Re: How to Display as well as Insert in a single sql statement [message #219751 is a reply to message #219747] Thu, 15 February 2007 17:19 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Maybe I am wrong, but returning clause is used with plsql or begin..end block.Since it is a report, i cannot use plsql. I need to use sql.



Re: How to Display as well as Insert in a single sql statement [message #219754 is a reply to message #219751] Thu, 15 February 2007 17:38 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Can you provide some simple table structures, data, and a query with outer joins and a GROUP BY to illustrate what you mean about not displaying data twice?
Re: How to Display as well as Insert in a single sql statement [message #219755 is a reply to message #219754] Thu, 15 February 2007 17:52 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
What I meant was Supposing If i run the query today, it will generate set of rows eg 100 rows
and If I run it tomorrow again, It will again display the same set of rows + new rows added. i.e 100 rows of previous day + 50 rows new rows.
I want to eliminate or not display those previous days rows when the query is run again.

Regards,
Re: How to Display as well as Insert in a single sql statement [message #219767 is a reply to message #219744] Thu, 15 February 2007 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
CREATE VIEW TODAY_ONLY_MASK ....

Then run report against this view.
Re: How to Display as well as Insert in a single sql statement [message #219887 is a reply to message #219744] Fri, 16 February 2007 09:51 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Eg, this is the script I run everyday.
SELECT
username,
upper(computername) computername,
trunc(timstamp) timstamp,
count(*) cnt
FROM
seosdata
GROUP BY username,
upper(computername),
trunc(timstamp)
HAVING count(*) >= 12
) a LEFT OUTER JOIN sec_allusers b
ON ( a.username = b.userid).

Now the base table SEOSDATA cannot be modified to add another column. Also it is possible that on a given day I may not find any rows that has count > 12.
So eg. if on 2/15, I find 10 rows that match the criteria.
on 2/16, I dont find any rows, but still it displays the previous days 10 rows (Which I Dont want).
on 2/17, I may find new rows + 10 old rows of 2/15..

Considering this what is a good plan to tackle this.

Regards,
Re: How to Display as well as Insert in a single sql statement [message #219890 is a reply to message #219887] Fri, 16 February 2007 10:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, from this query, is there any way of telling whether a row would have been selected by running the query yesterday (eg does the value of timestamp tell you when the row was inserted?)

If there isn't a way of doing that, then you're going to be stuck with really cheesy solutions like storing the data that the query ran yesterday in a temporary table, and performing a minus on todays data set using that temporary table.
Re: How to Display as well as Insert in a single sql statement [message #219892 is a reply to message #219890] Fri, 16 February 2007 10:13 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Unfortunately I may have to use the storage solution. I need to use the combination of username and timestamp to get the rows. Any idea how to insert as well as select in this statement so that Ineed not run the same query twice.

Regards,
Re: How to Display as well as Insert in a single sql statement [message #219898 is a reply to message #219892] Fri, 16 February 2007 10:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you do an insert into a temporary table, then you don't have to run the same query again - you can just query the data that you inserted into the temporary table instead.
Re: How to Display as well as Insert in a single sql statement [message #219902 is a reply to message #219898] Fri, 16 February 2007 10:35 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Oh!! Ok.. I think I will go forward with that approach. Thanks everyone for the help.

Regards,
Previous Topic: Converting Dates
Next Topic: need help in Query
Goto Forum:
  


Current Time: Sat Dec 03 10:17:00 CST 2016

Total time taken to generate the page: 0.08070 seconds