How to make Oracle Query recognizable to ASP.NET table adapter?

Question

I have a query with a sub query in the select that needs to get a value from a certain table if it exists. I don't want to paste the query here for business reasons, but here's a simplified example of what I am trying to do:

select a, b, (select x from z) as c  from table where ...

The query runs fine in TOAD, but when I go through the ASP.NET table adapter wizard in my data object, it complains about the sql statement: "Error in SELECT clause NEAR ','". I'm not sure if it would still work at runtime even though it complains? I know the preview doesn't work and it didn't create the data table. Anyone experienced this?

The reason I want to do it this way is because the query that populates field c will not always have a value but I have to return a and b even if c does not have a value. Any one good with knowledge of sql may be able to suggest a work around. I want to stick with 1 table adapter query if possible.

Answer 1

Outer join to table Z?

SELECT t.a,t.b,z.x
  FROM table t LEFT OUTER JOIN Z ON (t.y = Z.y)
 WHERE...

This will give you the values from Z if they exist, NULL otherwise.

Answer 2

When a tool doesn't understand a query, one method that always works is to create a view that encapsulates your query.

CREATE VIEW v AS SELECT a, b, (select x from z) as c from table

You would then use a simple query in ASP.NET like :

SELECT a, b, c FROM v where...

The view now contains your business logic and would be kept in your source control repository alongside all your other code.

Answer 3

I have the same problem - the two answers are good, but does enable us to use dataset designer in visual studio.

So Please help - are there a third solution

M