Home » SQL & PL/SQL » SQL & PL/SQL » how can i unit 3 sql query to one query? (unix 9i)
how can i unit 3 sql query to one query? [message #323453] Wed, 28 May 2008 09:00 Go to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
this is the querys:

INSERT INTO SAPR3.ZMOBLOG (id,CardName, InDateTime, CLI, SqlId, UserId)
VALUES ((SELECT max(ID)+1 FROM SAPR3.ZMOBLOG ) ,'Tech',
(select to_char (sysdate, 'DD-MM-YYYY HH24:MI:SS') as "Current Time" from dual), #SubscriberID ,146, 0)


SELECT VORNA AS Name, PERNR AS ID, TO_NUMBER(PERNR) AS sID, LGORT AS Whs FROM SAPR3.ZTECH WHERE SUBSTR(TELNR,-7,7) = SUBSTR('#SubscriberID',-7,7) AND MANDT = '$vClient'


UPDATE SAPR3.ZMOBLOG SET SAPR3.ZMOBLOG.OutDateTime = (select to_char (sysdate, 'DD-MM-YYYY HH24:MI:SS') as "Current Time" from dual)
WHERE (ID = (SELECT max(ID) FROM SAPR3.ZMOBLOG WHERE (cli ='#SubscriberID' AND cardname = 'Tech' )))


instead of runing them one after one
i want to run all of them in once.

thanks
Re: how can i unit 3 sql query to one query? [message #323454 is a reply to message #323453] Wed, 28 May 2008 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>i want to run all of them in once.
We are not stopping you.
Please proceed to do as you desire.
Re: how can i unit 3 sql query to one query? [message #323457 is a reply to message #323454] Wed, 28 May 2008 09:06 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
be serious?
i mean that i want one query like PORCEDURE
in PL SQL

thanks
Re: how can i unit 3 sql query to one query? [message #323460 is a reply to message #323457] Wed, 28 May 2008 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
lioracle wrote on Wed, 28 May 2008 07:06
be serious?
i mean that i want one query like PORCEDURE
in PL SQL

thanks


We are not stopping you.
Please proceed to do as you desire.
Re: how can i unit 3 sql query to one query? [message #323461 is a reply to message #323453] Wed, 28 May 2008 09:08 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
lioracle wrote on Wed, 28 May 2008 10:00

INSERT INTO SAPR3.ZMOBLOG (id,CardName, InDateTime, CLI, SqlId, UserId)
VALUES ((SELECT max(ID)+1 FROM SAPR3.ZMOBLOG ) ,'Tech',
(select to_char (sysdate, 'DD-MM-YYYY HH24:MI:SS') as "Current Time" from dual), #SubscriberID ,146, 0)


Are you really storing DATEs as a VARCHAR2?

Quote:

SELECT VORNA AS Name, PERNR AS ID, TO_NUMBER(PERNR) AS sID, LGORT AS Whs FROM SAPR3.ZTECH WHERE SUBSTR(TELNR,-7,7) = SUBSTR('#SubscriberID',-7,7) AND MANDT = '$vClient'



SUBSTR('#SubscriberID',-7,7) cann be replaced by hardcoded 'riberID'

Re: how can i unit 3 sql query to one query? [message #323463 is a reply to message #323457] Wed, 28 May 2008 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BEGIN
statements;
END;
/

Regards
Michel
Re: how can i unit 3 sql query to one query? [message #323464 is a reply to message #323457] Wed, 28 May 2008 09:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can wrap the three statements in a DECLARE..BEGIN...END; but you will need to declare a set of variables to fetch the data from the SELECT into.

You need to explain what you are trying to achieve more clearly.

[Too slowwww!!!!]

[Updated on: Wed, 28 May 2008 09:21]

Report message to a moderator

Re: how can i unit 3 sql query to one query? [message #323465 is a reply to message #323463] Wed, 28 May 2008 09:12 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
hey michel

can you give me that.

i didnt understand .
i copy and paste my statements as is?

Re: how can i unit 3 sql query to one query? [message #323484 is a reply to message #323465] Wed, 28 May 2008 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lioracle wrote on Wed, 28 May 2008 16:12
hey michel

can you give me that.

i didnt understand .
i copy and paste my statements as is?



Yes but as JRowbottom said, you have to declare variables (either in declare section or bind variables) to get the data from SELECT statements and it depends if you get 1 or multiple rows, which tool you use, what do you want to do with this selected data...

Regards
Michel
Re: how can i unit 3 sql query to one query? [message #323488 is a reply to message #323484] Wed, 28 May 2008 10:24 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
help!!
i didnt know PLSQL

Re: how can i unit 3 sql query to one query? [message #323489 is a reply to message #323488] Wed, 28 May 2008 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So is it necessary you change your 3 statements to make them work with difficulties inside a PL/SQL block?

Regards
Michel

[Updated on: Wed, 28 May 2008 10:30]

Report message to a moderator

Re: how can i unit 3 sql query to one query? [message #323490 is a reply to message #323488] Wed, 28 May 2008 10:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What are you trying to achieve?

You want to be able to run these three SQL at once.
- Do you need to do this from SQL*Plus
- Do you need to do this from some sort of GUI front end (Forms, VB etc)
- Do you need to be able to do this from another process running in the database?

Tell us more about what you're trying to d, and we may be able to help you.
Re: how can i unit 3 sql query to one query? [message #323491 is a reply to message #323490] Wed, 28 May 2008 10:33 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
hey first all thanks

i need to run this from sql plus

thanks
Re: how can i unit 3 sql query to one query? [message #323493 is a reply to message #323491] Wed, 28 May 2008 10:35 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Create a file called (eg) my_code.sql.
Copy and paste all three queries into that file, making sure that there is a trailing ';' after each query.
Save the file.


for the sake of argument, we'll assume you've saved it in the directory c:\sql

From SQL*Plus, you can run the contents of this file by typeing the command:
@c:\sql\my_code.sql
Previous Topic: How to translate PSQL to Oracle PL/SQL
Next Topic: Error in procedure when called
Goto Forum:
  


Current Time: Fri Dec 09 07:45:06 CST 2016

Total time taken to generate the page: 0.06577 seconds