Home » SQL & PL/SQL » SQL & PL/SQL » SQL questions from psycroptik (merged)
icon5.gif  SQL questions from psycroptik (merged) [message #287545] Wed, 12 December 2007 10:37 Go to next message
psycroptik
Messages: 4
Registered: December 2007
Junior Member
Hi hi,

Im looking to select a subscriber range from one table then
remove records from another table that the range falls inbetween.
I know a little sql so some of the code is there...

I want to make this into a .sql file, thats the part i know nothing of.


s int;
e int;

While (More lines in RANGE_MAP)

SELECT RANGE_START s, RANGE_END e, ID
FROM RANGE_MAP
WHERE ID !=1;

DELETE from SUBS
WHERE SUB >= s
AND SUB <= e;


Im also looking to be able to print out how many SUBS
were deleted.

Any help would be great!

Thanks.
Re: Help convert pseudo into a .sql file? [message #287548 is a reply to message #287545] Wed, 12 December 2007 11:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will give you the answer for the second one... It is sql%rowcount will give you. For the first part you have to do some search, like reading oracle manuals, google it. Sorry mate no pain, no gain. If you are stuck at some point come back somebody will help you out.

good luck and happy learning sql

Regards

Raj
Re: Help convert pseudo into a .sql file? [message #287550 is a reply to message #287545] Wed, 12 December 2007 11:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>While (More lines in RANGE_MAP)
Since when does the SQL language contain a WHILE construct?
Re: Help convert pseudo into a .sql file? [message #287557 is a reply to message #287545] Wed, 12 December 2007 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do it in a single SQL statement but as Raj said, you first have to read and learn SQL, especially join and correlated subquery.
First try to buikd a query that selects the rows you want to delete.
You need a database to work with.
You can't learn SQL without a database.

Regards
Michel
Re: Help convert pseudo into a .sql file? [message #287826 is a reply to message #287545] Thu, 13 December 2007 12:47 Go to previous messageGo to next message
psycroptik
Messages: 4
Registered: December 2007
Junior Member
Ok, I just needed ideas, looked up a few commands for SQL.
I tried two different ways but no luck..

delete from IM
WHERE IMS = (select start, end, ID from range_map where ID !=1 between start and end);
*
ORA-00907: missing right parenthesis

So I tried,

delete from IM
WHERE IMS = (select start, end, ID from range_map where ID !=1
and IMS >= start
and IMS <= end);

WHERE IMS = (select start, end, ID from range_map ...
*
ORA-00913: too many values

Any ideas on how to fix either one?
Re: Help convert pseudo into a .sql file? [message #287829 is a reply to message #287545] Thu, 13 December 2007 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>WHERE IMS = (select start, end, ID from range_map where ID !=1 between start and end);

Multiple conditions typically involve AND or OR
You have not specified WHAT is between start and end

>WHERE IMS = (select start, end, ID from range_map ...
How can one value (IMS) ever equal 3 (start, end, ID )?

I suggest you get extra tutoring from somebody who knows more about SQL than just how to spell it.
Re: Help convert pseudo into a .sql file? [message #287830 is a reply to message #287826] Thu, 13 December 2007 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You need to first learn the basic of SQL.
How could expect, in a logical point of view, one value can be compare to a list?
What is the meaning of 1 = (2,3,4), or worst 1=(2,3,4),(5,6,7),...?
This is what you wrote.

Regards
Michel
Re: Help convert pseudo into a .sql file? [message #287832 is a reply to message #287545] Thu, 13 December 2007 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
trying to learn SQL by trial and error is NOT an efficient use of your time.

At least you should start with valid & functioning SQL statements that you then customize to your local environment.

If you can NOT read, follow & understand the syntax diagrams in the SQL Reference manual, I suggest you stick to point & click GUIs.
Can you pass a variable through a UNION? [message #287846 is a reply to message #287545] Thu, 13 December 2007 15:05 Go to previous messageGo to next message
psycroptik
Messages: 4
Registered: December 2007
Junior Member
RS and RE are my variables, I want to use them after the UNION.
It seems to only want a column.

SELECT RANGE_START RS, RANGE_END RE, SDP_ID
FROM RANGE_MAP
WHERE SDP_ID !=1
UNION select count(1) from IMSI_CORRELATION
WHERE IMSI >= RS
AND IMSI <= RE;
Re: Can you pass a variable through a UNION? [message #287851 is a reply to message #287846] Thu, 13 December 2007 16:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your syntax is completly wrong.
Once again, as for your other post, you will not get the correct syntax without first learning SQL and also try to think about what you write.

Regards
Michel
Re: SQL questions from psycroptik (merged) [message #288055 is a reply to message #287545] Fri, 14 December 2007 09:19 Go to previous messageGo to next message
psycroptik
Messages: 4
Registered: December 2007
Junior Member
How is my syntax totally wrong?
This is a sql help forum right?
If your going to waste your time to complain why don't you answer the question.
Re: SQL questions from psycroptik (merged) [message #288058 is a reply to message #287545] Fri, 14 December 2007 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You are free to follow or ignore posting guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
We are fee to respond or ignore your request for assistance.

Fair is fair.
Re: SQL questions from psycroptik (merged) [message #288059 is a reply to message #287545] Fri, 14 December 2007 09:49 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi psycroptik,

You will get help here with problems but most people don't expect to (or will not) spoonfeed basic syntax help.

Have a look at techonthenet to see how the Oracle syntax for UNION (and others) works. This gives you the basic stuff and you can dip into the Oracle manuals as well (plenty of links to those on this site).

Once you've been through the syntax and had a go at your code again, if you still have problems that you can't resolve just post again, and as anacedent says, make sure you follow the posting guidelines as that helps everyone.

As for
Quote:

I want to make this into a .sql file, thats the part i know nothing of.
you'll need to give some more info for this, such as the operating system, and what tool you are working in (SQLPlus, TOAD etc).





Re: SQL questions from psycroptik (merged) [message #288199 is a reply to message #287545] Sat, 15 December 2007 15:04 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member

Frank, hobbes, MarcL, joy_division, Mahesh Rajendran, Barbara Boehmer, rleishman, rajavu1

Which of you are going hand hold this Newbie?
Previous Topic: how can i do this procedure?
Next Topic: Different Types of reports
Goto Forum:
  


Current Time: Thu Dec 08 18:29:13 CST 2016

Total time taken to generate the page: 0.10801 seconds