View Problem

[hot_ice]

I am using a inline SQL query in VB which i want to convert to a view in oracle. But the problem is the view also contains search criteria using the where clause and also the group by clause.

Since it's a pretty long query, I would like to implement it as a VIEW in oracle and just call the view in VB. How do i append the WHERE clause to the VIEW in VB with the group by also there?

eg IF the view says " Create or replace view EMP_SAL(employees,department,salary) as select employees, department, salary group by department, salary" In VB, I say, select * from SAL_EMP. Now How do I append the where clause in between the query and group by?

Please shed some light asap.

[brucevde]

How do I append the where clause in between the query and group by?

You wouldn't. The Where clause would be included in the VB sql statement.

select * from SAL_EMP Where salary > 10000

[jp140768]

I don't know if Oracle is the same as SQL Server, but in SQL Server, you could achieve this by creating a stored procedure, and passing the values of your Where statement as parameters.

HTH

[hot_ice]

ya bruce , agreed. But a where clause cannot be appended after a group by. It shd be placed before group by. And the group by is already formed in the view. So how to place the where clause between the query and group by?

[si_the_geek]

You don't need to.

The SQL that 'builds' the view it is irrelevant, as the view is treated like a table by any SQL statements that use it - so just write SQL statements in the usual way, and pretend that the view is a table.

[szlamany]

What columns do you want to do the WHERE on? Are they already in the VIEW - or did they get lost in the GROUP BY?

More information please!