September 10, 2008 at 12:27 pm
OK, to make a long story short, I was hired to do some BI stuff, but because of my knowledge of MS TSQL and DB2, I've ended up having to also write some Oracle Sql. Ok, on to the subject. I really did not expect Oracle's Sql to be so different.
I've read many comments here and other places about Oracle's PL/SQL vs MS TSQL. My experience seems to be the opposite of popular opinion out there. Things that seem to be simple and trivial in TSQL end up to be amazingly verbose and difficult in Oracle Sql.(OK, enough editorializing)
Question 1.
My first question is, is there any equivalent to TSQLs "select into" in Oracle SQL. In Oracle PL/SQL, this selects columns into variables, which is not helpful here. I need the TSQL functionality, where we want to select some rows and columns from one table into a new table having it create the table on the fly as happens in TSQL.
Question 2.
I need to do what in TSQL we would call an "update from", but with derived tables, joins, etc. Sort of like this ;
Update table1
set col1 = x.colx, col2 = y.coly, col3 = z.colz,
from table2
join (select from someTable
join someother table on
group by ) Y
join ( ) Z
where
Oracle does not seem to like this at all. To irritate me even further, the only workaround seems to be something like ;
Update table1
set col1 = (select x.colx from table2 x
join (select from someTable
join someother table on
group by
join
) ,
set col2 = (select y.coly from table2 x
join (select from someTable
join someother table on
group by
join
) Y ,
set col3 = (select z.colz from table2
join (select from someTable
join someother table on
group by
join
) Z
Hopefully , you get the idea.
I really really hope that this verbose mess of repeated identical selects with only the selected column being different cannot be the only way to do something like this in Oracle.
Anyone know how to do these ?
September 10, 2008 at 2:23 pm
Yikes ! In my previous post, some of my Sql shorthand notation ended up a smiley emoticons ! :w00t:
I did end up solving isue #1. FYI, in Oracle Sql the way to do the equivalent of a TSql select into is ;
create table table_name as select column list from src_table_name
That's actually fairly intuitive. (well, provided you aren't so TSQL minded :D).
September 10, 2008 at 2:38 pm
I used to have a great side-by-side comparison when I worked at another company where we supported both. Boy do I miss those days.... NOT!!! 🙂
After a bit of searching I couldn't find the one that I had but I found a basic view into some of them.
http://www.nocoug.org/download/2004-05/BegSQL.ppt
Hope this helps. If I find the other then I will post that as well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 11, 2008 at 2:35 am
Welcome to the Oracle world.
PL/SQL and Transact-SQL are totally different languages like French and Occitan are meaning, do not expect to be fuent in one of them just because you are fluent on the other one 😉
The good news is that Oracle provides excelent documentation for free, check here... http://www.oracle.com/pls/db102/portal.all_books
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 13, 2008 at 7:34 am
Question 2.
I need to do what in TSQL we would call an "update from", but with derived tables, joins, etc. Sort of like this ;
Update table1
set col1 = x.colx, col2 = y.coly, col3 = z.colz,
from table2
join (select from someTable
join someother table on
group by ) Y
join ( ) Z
Although your T-SQL code is not very clear, here is one way to do it in PL/SQL
DECLARE
CURSOR C1 IS SELECT colx, coly ..... [your query]
REC1 C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO REC1;
EXIT WHEN C1% NOTFOUND;
BEGIN
UPDATE table1 SET col1 = rec1.colx, col2 = rec1.coly, [and so on ....]
WHERE [your condition]
END;
END LOOP;
COMMIT;
CLOSE C1;
END;
Note that SQL is an ANSI and ISO standard and both the Oracle SQL-Server are based on it. The PL/SQL and T_SQL being the procedural extensions, they differ in syntax and the way they peocess data. One basic pre-requisite to excel in both of them, especially development is to be proficient with the principles of RDBMS.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
September 13, 2008 at 11:14 am
jmuldoon (9/10/2008)
My experience seems to be the opposite of popular opinion out there. Things that seem to be simple and trivial in TSQL end up to be amazingly verbose and difficult in Oracle Sql.(OK, enough editorializing)Question 1.
My first question is, is there any equivalent to TSQLs "select into" in Oracle SQL. In Oracle PL/SQL, this selects columns into variables, which is not helpful here. I need the TSQL functionality, where we want to select some rows and columns from one table into a new table having it create the table on the fly as happens in TSQL.
Question 2.
I need to do what in TSQL we would call an "update from", but with derived tables, joins, etc. Sort of like this ;
Update table1
set col1 = x.colx, col2 = y.coly, col3 = z.colz,
from table2
join (select from someTable
join someother table on
group by ) Y
join ( ) Z
where ...
No... you're on the right track... you need to use correlated subqueries like that on updates in Oracle because it has no FROM clause in the UPDATE statement.
You can, however, list more than one column name on either side of the "=" in the set statement. You should also "double" the code up in the WHERE clause so that Oracle doesn't try to update everything in the table.
The alternative is to use the MERGE statement... I believe it's been available since version 9I2 or so. Ironically, it brings you very much closer to the friendly FROM clause in an UPDATE that SQL Server does so very well.
Heh... imagine this... there are some folks that think the FROM clause in an UPDATE is a bad thing. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2008 at 5:25 am
Heh... imagine this... there are some folks that think the FROM clause in an UPDATE is a bad thing.
I would be interested in hearing any argument(s) as to why some folks might think that. Spare me the not ANSI argument and I'll not mention the natural and useful extension to the language done by one vendor is sneered at by the academia geeks.
It would seem to me that this would be a logical extension to the update statement given its usage in the select statement.
Secondly. the one thing that is certain is that it is a very useful extensiom. It enables one to express a complex join relationship in a useful way, less cumbersome than the correlated subquery.
Secondly, it is very helpful in query design as it allows one to easily convert a select statement into an update (or a delete, when that extension is also
used).
Third, when used as I suggest above, that is, building and testing a select statement that can be easily converted to an update or delete can be used to make database work much safer.
?
September 15, 2008 at 10:06 am
You have to ask this question to Angelo, good luck.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 15, 2008 at 5:49 pm
jmuldoon (9/15/2008)
Heh... imagine this... there are some folks that think the FROM clause in an UPDATE is a bad thing.
I would be interested in hearing any argument(s) as to why some folks might think that. Spare me the not ANSI argument and I'll not mention the natural and useful extension to the language done by one vendor is sneered at by the academia geeks.
It would seem to me that this would be a logical extension to the update statement given its usage in the select statement.
Secondly. the one thing that is certain is that it is a very useful extensiom. It enables one to express a complex join relationship in a useful way, less cumbersome than the correlated subquery.
Secondly, it is very helpful in query design as it allows one to easily convert a select statement into an update (or a delete, when that extension is also
used).
Third, when used as I suggest above, that is, building and testing a select statement that can be easily converted to an update or delete can be used to make database work much safer.
?
Heh... preaching to the choir, here. I absolutely agree.
I'll see if I can find the URL's that try to justifiy it's deprecation... note that I don't agree with them even on a single point. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2008 at 8:30 pm
I found the URL I was thinking about.
http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
I disagree with just about everything he says there. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply