[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

How do I write With As in Oracle?

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
garyjgs
Asked:
garyjgs
  • 3
  • 2
  • 2
1 Solution
 
käµfm³d 👽Commented:
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
 
sdstuberCommented:
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
 
garyjgsAuthor Commented:
Thank you,
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
sdstuberCommented:
glad you got your answer, but do check if your io if the query is really doing what you want it to.
0
 
käµfm³d 👽Commented:
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
 
sdstuberCommented:
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
 
garyjgsAuthor Commented:
Ok
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now