3

I have to write an SQL command which queries a View in a database. I did not create them myself nor do I have access to the tables. Only the views are provided to us by our software manufacturer. I'm using Oracle SQL Developer 3.0 to query. The following command works flawlessly as expected:

SELECT * FROM EDBADM.VW_THEVIEWNAME

However, when I ask for a certain column (called filename) not to contain any null values:

SELECT * FROM EDBADM.VW_THEVIEWNAME WHERE filename IS NOT NULL;

.... I get the following error:

ORA-00979:
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error in line: 3 column: 75

This seems implausible to me as no aggregation is involved in my part of the query. Also, when I query for filename to match a certain pattern:

SELECT * FROM EDBADM.VW_THEVIEWNAME WHERE filename LIKE 'abc%'

... I still get result rows where filename is NULL.

Could it be possible that the underlying, inaccessible definition of the view is incorrect, leading to this strange behavior??

Mark Hurd
  • 10,665
  • 10
  • 68
  • 101
user748261
  • 151
  • 1
  • 2
  • 6

1 Answers1

0

Could you post the creation script of your view? Is the column called filename explicitally declared in the view called VW_THEVIEWNAME?

UltraCommit
  • 2,236
  • 7
  • 43
  • 61
  • 1
    As I initially wrote: the database was created by an external software manufacturer. It is the backend to a customer management system. The manufacturer does not provide us with any *direct* access to this backend, except through the views. This means that unfortunately I have no creation script available for you, sorry! – user748261 May 13 '11 at 13:32
  • 1
    While you use your software, you can use SESSION BROWSER by Toad (Quest Software) in order to see what queries have been launched on the server. It's in fact a "query sniffing" work. Have you already tried? In Oracle, useful system views are V$SQLTEXT, V$SQLAREA for example. – UltraCommit May 17 '11 at 14:55