Solved

How do I write With As in Oracle?

Posted on 2011-09-12
7
227 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 73

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 73

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 73

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now