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.

DCookie · Accepted Answer · 2009-06-10 17:34:42Z

2

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.

answered Jun 10, 2009 at 17:34

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vincent Malgrat · Accepted Answer · 2009-06-10 18:05:18Z

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.

edited Jun 10, 2009 at 18:05

answered Jun 10, 2009 at 17:37

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

2 Comments

DCookie Over a year ago

+1, good suggestion as long as users have the ability to create objects in the database.

Jon Over a year ago

Unfortunately this would require DBA approval and regression testing.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related