From: Mario Dankoor <m.p.dankoor@gmail.com>
To: Rehan Saleem <pk_rehan@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL View to PostgreSQL View
Date: Tue, 28 Feb 2012 12:29:57 +0100
Message-ID: <4F4CBAB5.9080103@gmail.com> (raw)
In-Reply-To: <1330282216.75125.YahooMailNeo@web121605.mail.ne1.yahoo.com>
References: <1330282216.75125.YahooMailNeo@web121605.mail.ne1.yahoo.com>
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 amgettingthe
> following error idontknow how to handledbo. 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: m.p.dankoor@gmail.com, pk_rehan@yahoo.com
Subject: Re: SQL View to PostgreSQL View
In-Reply-To: <4F4CBAB5.9080103@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox