pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL View to PostgreSQL View
4+ messages / 4 participants
[nested] [flat]
* SQL View to PostgreSQL View
@ 2012-02-26 18:50 Rehan Saleem <pk_rehan@yahoo.com>
0 siblings, 3 replies; 4+ messages in thread
From: Rehan Saleem @ 2012-02-26 18:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL View to PostgreSQL View
@ 2012-02-27 14:13 Adrian Klaver <adrian.klaver@gmail.com>
parent: Rehan Saleem <pk_rehan@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2012-02-27 14:13 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Rehan Saleem <pk_rehan@yahoo.com>
On Sunday, February 26, 2012 10:50:16 am 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
>
> 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
Do you in fact have a schema dbo?
If so can you access the schema and table using some other method, for instance
using psql?
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL View to PostgreSQL View
@ 2012-02-28 11:29 Mario Dankoor <m.p.dankoor@gmail.com>
parent: Rehan Saleem <pk_rehan@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Mario Dankoor @ 2012-02-28 11:29 UTC (permalink / raw)
To: Rehan Saleem <pk_rehan@yahoo.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL View to PostgreSQL View
@ 2012-02-28 14:27 Igor Neyman <ineyman@perceptron.com>
parent: Rehan Saleem <pk_rehan@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Igor Neyman @ 2012-02-28 14:27 UTC (permalink / raw)
To: Rehan Saleem <pk_rehan@yahoo.com>; pgsql-sql
From: Rehan Saleem [mailto:pk_rehan@yahoo.com]
Sent: Sunday, February 26, 2012 1:50 PM
To: pgsql-sql@postgresql.org
Subject: SQL View to PostgreSQL View
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
>>
First, I assume you are converting your view from SQL Server, not from SQL.
SQL Server is RDBMS, while SQL is a language being used by multiple RDBMSs including PostgreSQL.
Second, there is no "standard" dbo ("database owner") role in Postgres.
Before converting from one RDBMS to another you need to do some basic (at least) documentation reading on "target" RDBMS (in this case - PostgreSQL).
Otherwise, you will stumble on every step.
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-02-28 14:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-26 18:50 SQL View to PostgreSQL View Rehan Saleem <pk_rehan@yahoo.com>
2012-02-27 14:13 ` Adrian Klaver <adrian.klaver@gmail.com>
2012-02-28 11:29 ` Mario Dankoor <m.p.dankoor@gmail.com>
2012-02-28 14:27 ` Igor Neyman <ineyman@perceptron.com>
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