Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I write With As in Oracle?

Posted on 2011-09-12
7
Medium Priority
?
233 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
[X]
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
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

721 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