August 13, 2015 at 11:10 am
August 13, 2015 at 12:22 pm
jodevil99 (8/13/2015)
FROM keyword not found where expectedselect *
from quer a
cross join (select 'Text1' as MyText union all select 'Text2') x
missing keyword
select *
from quer a
cross apply (values('Text1'), ('Text2')) x (MyText)
SQL command not properly ended
select *
from quer
cross apply (values('Text1'), ('Text2')) x (MyText)
Well this being a sql server forum the code I posted works in sql server. It may or may not work in Oracle. I have no clue about that as I can barely spell it and only vaguely remember it from the couple of times I had to use it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2015 at 12:44 pm
Sean Lange (8/13/2015)
jodevil99 (8/13/2015)
FROM keyword not found where expectedselect *
from quer a
cross join (select 'Text1' as MyText union all select 'Text2') x
missing keyword
select *
from quer a
cross apply (values('Text1'), ('Text2')) x (MyText)
SQL command not properly ended
select *
from quer
cross apply (values('Text1'), ('Text2')) x (MyText)
Well this being a sql server forum the code I posted works in sql server. It may or may not work in Oracle. I have no clue about that as I can barely spell it and only vaguely remember it from the couple of times I had to use it.
For Oracle, the first query should be something like this:
select *
from quer a
cross join (select 'Text1' as MyText FROM Dual union all select 'Text2' FROM Dual) x;
Oracle needs a FROM clause on every SELECT, so they provide a single column-single row table called Dual for this kind of things.
The "SQL command not properly ended" error is mostly common to be shown when there isn't a semicolon as a statement terminator.
These were 2 common headaches I suffered when having to work with Oracle.
August 13, 2015 at 1:01 pm
Luis Cazares (8/13/2015)
Sean Lange (8/13/2015)
jodevil99 (8/13/2015)
FROM keyword not found where expectedselect *
from quer a
cross join (select 'Text1' as MyText union all select 'Text2') x
missing keyword
select *
from quer a
cross apply (values('Text1'), ('Text2')) x (MyText)
SQL command not properly ended
select *
from quer
cross apply (values('Text1'), ('Text2')) x (MyText)
Well this being a sql server forum the code I posted works in sql server. It may or may not work in Oracle. I have no clue about that as I can barely spell it and only vaguely remember it from the couple of times I had to use it.
For Oracle, the first query should be something like this:
select *
from quer a
cross join (select 'Text1' as MyText FROM Dual union all select 'Text2' FROM Dual) x;
Oracle needs a FROM clause on every SELECT, so they provide a single column-single row table called Dual for this kind of things.
The "SQL command not properly ended" error is mostly common to be shown when there isn't a semicolon as a statement terminator.
These were 2 common headaches I suffered when having to work with Oracle.
Thanks Luis. Good information. I have to say though that seems like the most ridiculous work around I have heard of in quite some time...and I wonder where the strange name "Dual" comes from??? FakeTable would make more sense to me. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2015 at 1:07 pm
Sean Lange (8/13/2015)
Luis Cazares (8/13/2015)
For Oracle, the first query should be something like this:
select *
from quer a
cross join (select 'Text1' as MyText FROM Dual union all select 'Text2' FROM Dual) x;
Oracle needs a FROM clause on every SELECT, so they provide a single column-single row table called Dual for this kind of things.
The "SQL command not properly ended" error is mostly common to be shown when there isn't a semicolon as a statement terminator.
These were 2 common headaches I suffered when having to work with Oracle.
Thanks Luis. Good information. I have to say though that seems like the most ridiculous work around I have heard of in quite some time...and I wonder where the strange name "Dual" comes from??? FakeTable would make more sense to me. 😛
I never asked myself that question before. Fortunately, Wikipedia has the answer:
I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, apt for the process of creating a pair of rows from just one.The original DUAL table had two rows in it (hence its name), but subsequently it only had one row.
August 13, 2015 at 1:41 pm
Luis Cazares (8/13/2015)
Sean Lange (8/13/2015)
Luis Cazares (8/13/2015)
For Oracle, the first query should be something like this:
select *
from quer a
cross join (select 'Text1' as MyText FROM Dual union all select 'Text2' FROM Dual) x;
Oracle needs a FROM clause on every SELECT, so they provide a single column-single row table called Dual for this kind of things.
The "SQL command not properly ended" error is mostly common to be shown when there isn't a semicolon as a statement terminator.
These were 2 common headaches I suffered when having to work with Oracle.
Thanks Luis. Good information. I have to say though that seems like the most ridiculous work around I have heard of in quite some time...and I wonder where the strange name "Dual" comes from??? FakeTable would make more sense to me. 😛
I never asked myself that question before. Fortunately, Wikipedia has the answer:
I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, apt for the process of creating a pair of rows from just one.The original DUAL table had two rows in it (hence its name), but subsequently it only had one row.
Interesting. Does Oracle do something to prevent people from adding or deleting from that table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2015 at 2:05 pm
I don't have an Oracle database to test against it, but it seems that you can insert and delete rows from Dual if you have Admin rights.
The fun part is that it wouldn't matter how many rows you insert or delete, the optimizer won't read from it and simply assume that it has one row. This "improvement" seems to have happened on a recent version of Oracle (I believe 10g) as some seem to be able to return different number of rows depending on inserts and deletes.
Another fun part is that it stores an X in a varchar2(1) column called dummy. I'm sure that you'd love the data type chosen.
August 13, 2015 at 2:19 pm
Luis Cazares (8/13/2015)
I don't have an Oracle database to test against it, but it seems that you can insert and delete rows from Dual if you have Admin rights.The fun part is that it wouldn't matter how many rows you insert or delete, the optimizer won't read from it and simply assume that it has one row. This "improvement" seems to have happened on a recent version of Oracle (I believe 10g) as some seem to be able to return different number of rows depending on inserts and deletes.
Another fun part is that it stores an X in a varchar2(1) column called dummy. I'm sure that you'd love the data type chosen.
LOL. Does seem to fit a discussion we have been having elsewhere recently. I don't know how Oracle handles storage for varchar2 but I wouldn't be surprised if it is much the same as sql server. In this case it isn't a huge deal as it is only wasting a couple bytes of storage. The ones that kill me are that datatype on tables with a few million rows.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply