Solved

How do I write With As in Oracle?

Posted on 2011-09-12
7
230 Views
Last Modified: 2012-06-27
Here is a sample syntax of trying to write a simple With As in Oracle:
WITH OFFERS AS

(SELECT FECHAINI1 FROM TB_OFERTAS
UNION
SELECT FECHAINI2 FROM TB_OFERTAS)
AS OFFERS

SELECT
OFFERS
,COMPANY
FROM TB_OFERTAS

I am receiving an error [Oracle][ODBC][Ora]ORA-00928: missing SELECT keyword.  How do I resolve this?
0
Comment
Question by:garyjgs
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 36525660
Shouldn't it be something like:
WITH OFFERS AS

(SELECT FECHAINI1 c FROM TB_OFERTAS
 UNION
 SELECT FECHAINI2 c FROM TB_OFERTAS
)

SELECT
OFFERS.c, TB_OFERTAS.COMPANY
FROM TB_OFERTAS,
OFFERS

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36525845
what are you really trying to do?  You reference the  TB_OFERTAS  table 3 times which means it's unlikely to scale well as data volume increases.

If you simply trying to show all fechain1/fechain2 combinations for every company  then try this...

with x as (select 1 n from dual union all select 2 from dual)
select decode(n,1,fechaini1,2,fechaini2) offers, company from tb_ofertas,x


if this doesn't produce the results you are looking for,  please post some sample data and expected results.
It's hard to deduce what you "do" want based on a query that does "not" do what you want.
0
 

Author Closing Comment

by:garyjgs
ID: 36525855
Thank you,
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36525912
glad you got your answer, but do check if your io if the query is really doing what you want it to.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 36526754
but do check if your io if the query is really doing what you want it to.
Certainly do that. I made a suggestion based on what I found in the documentation, but I think sdstuber's middle name is "Oracle". It's pretty much always a good idea to follow up on his suggestions  = )
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36526973
kaufmed - thanks for the kind words.


our queries return slightly different results, so I can't say that mine is "correct", only the asker can determine that.
since mine wasn't accepted, I assume I must have misinterpreted the expected results.


my concern is getting the results expected (which you evidently did), but as efficiently as possible.

but, if the asker is content, that's fine.
0
 

Author Comment

by:garyjgs
ID: 36527029
Ok
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question