Re: SQL View to PostgreSQL View

Hi Rehan,

Whilst I'm not sure what you exactly mean with '...., while this is working perfectly fine in sql,..',
it's kind of odd that you get result when you execute the sql.
I'd suggest looking at your search_path (show search_path), which normally resolves to 'user', 'public';

If you do have a schema dbo, you could change the search_path as follows:
alter user <your user> set search_path = '$user','public','dbo';

Mario

pgsql-sql@postgresql.org
On 2012-02-26 7:50 PM, Rehan Saleem wrote:

Hi ,

I am trying to convert sql view to postgresql view but i am getting the following error i dont know how to handle dbo. in postgresql and when i remove dbo. from table name then view got created but it does not show any data, while this is working perfectly fine in sql, here is my code and error details 

CREATE OR REPLACE VIEW vwkbcomparesites as

select a.kbid kb_a, b.kbid kb_b, a.chr chr_a, a.start start_a, a."end" end_a, (a."end" - a.start)+1 tagsize_a, 

b.chr chr_b, b.start start_b, b."end" end_b, (b."end" - b.start)+1 tagsize_b,

    abs((a."end" + a.start)/2 - (b."end" + b.start)/2) centredistance,

case 

when a."end" <= b."end" and a.start >= b.start  

then (a."end" - a.start) 

when b."end" <= a."end" and b.start >= a.start   

then (b."end" - b.start) 

when a."end" <= b."end" and a.start <= b.start    

then (a."end" - b.start) 

when a."end" >= b."end" and a.start >= b.start

then (b."end" - a.start)  

end bpoverlap

from  dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr

inner join dbo.kbdetails kbd on a.kbid=kbd.kbid

where kbd.active='1' ;

i am getting this error , how can i fix this.

ERROR:  schema "dbo" does not exist

LINE 15:  from  dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr

                ^

********** Error **********

ERROR: schema "dbo" does not exist

SQL state: 3F000

Character: 761