Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How do I write With As in Oracle?

Posted on 2011-09-12
7
229 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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