Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id C28729552D4
	for <pgsql-sql@postgresql.org>; Tue, 28 Feb 2012 07:30:14 -0400 (AST)
Received: from mail-bk0-f46.google.com ([209.85.214.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <m.p.dankoor@gmail.com>) id 1S2LG0-00029g-RS
	for pgsql-sql@postgresql.org; Tue, 28 Feb 2012 11:30:14 +0000
Received: by bkcik5 with SMTP id ik5so1126056bkc.19
	for <pgsql-sql@postgresql.org>; Tue, 28 Feb 2012 03:29:59 -0800 (PST)
Received-SPF: pass (google.com: domain of m.p.dankoor@gmail.com designates
	10.205.132.71 as permitted sender) client-ip=10.205.132.71;
Authentication-Results: mr.google.com;
	spf=pass (google.com: domain of m.p.dankoor@gmail.com
	designates 10.205.132.71 as permitted sender)
	smtp.mail=m.p.dankoor@gmail.com;
	dkim=pass header.i=m.p.dankoor@gmail.com
Received: from mr.google.com ([10.205.132.71])
	by 10.205.132.71 with SMTP id ht7mr8969408bkc.19.1330428599542
	(num_hops = 1); Tue, 28 Feb 2012 03:29:59 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type;
	bh=i5Mt1lEJxAPmqt+bhAgZIWc15x2st1jk/U3ODjVOCY0=;
	b=pv1I7hxFu5t5jVfuv2xnWeFWqZbWKQ7mXILrMcREfYqKrIQKPjJVr/5s+Cs5cqf9vA
	7LOptS3+XI4VW8BVSoZjNzPebai1Wjhnmwt9Nk0FcjktcFSClphLIsVINtPgyXPXF1V9
	16iUT5LQeNO7vO0W08eWRJrwfcoNr4Z4zTIYg=
Received: by 10.205.132.71 with SMTP id ht7mr7217768bkc.19.1330428599436;
	Tue, 28 Feb 2012 03:29:59 -0800 (PST)
Received: from [192.168.178.16] (qtuning.xs4all.nl. [80.101.37.209])
	by mx.google.com with ESMTPS id d5sm30565335bkb.3.2012.02.28.03.29.57
	(version=SSLv3 cipher=OTHER); Tue, 28 Feb 2012 03:29:58 -0800 (PST)
Message-ID: <4F4CBAB5.9080103@gmail.com>
Date: Tue, 28 Feb 2012 12:29:57 +0100
From: Mario Dankoor <m.p.dankoor@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
To: Rehan Saleem <pk_rehan@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: SQL View  to PostgreSQL View
References: <1330282216.75125.YahooMailNeo@web121605.mail.ne1.yahoo.com>
In-Reply-To: <1330282216.75125.YahooMailNeo@web121605.mail.ne1.yahoo.com>
Content-Type: multipart/alternative;
	boundary="------------030909040002090407040702"
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201202/91
X-Sequence-Number: 36384

This is a multi-part message in MIME format.
--------------030909040002090407040702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>


--------------030909040002090407040702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hi Rehan,<br>
    <br>
    Whilst I'm not sure what you exactly mean with '....<span
      style="font-family: 'times new roman','new york',times,serif;
      background-color: white; font-size: 12pt;">, while this is working
      perfectly fine in sql,..',<br>
    </span>it's kind of odd that you get result when you execute the
    sql.<br>
    I'd suggest looking at your search_path (show search_path), which
    normally resolves to 'user', 'public';<br>
    <br>
    If you do have a schema dbo, you could change the search_path as
    follows:<br>
    alter user &lt;your user&gt; set search_path =
    '$user','public','dbo';<br>
    <br>
    Mario<br>
    <br>
    <a class="moz-txt-link-abbreviated" href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a><br>
    On 2012-02-26 7:50 PM, Rehan Saleem wrote:
    <blockquote
      cite="mid:1330282216.75125.YahooMailNeo@web121605.mail.ne1.yahoo.com"
      type="cite">
      <div style="color:#000; background-color:#fff; font-family:times
        new roman, new york, times, serif;font-size:12pt">
        <div style="font-family: 'times new roman', 'new york', times,
          serif; font-size: 12pt; ">Hi ,</div>
        <div><span style="font-size: 12pt;">I am trying to convert&nbsp;</span><span
            style="font-family: 'times new roman', 'new york', times,
            serif; background-color: white; font-size: 12pt; ">sql
            view to postgresql view but i am</span><span
            class="apple-converted-space" style="font-family: 'times new
            roman', 'new york', times, serif; background-color: white;
            font-size: 12pt; ">&nbsp;</span><span
            class="apple-converted-space" style="background-color:
            white; ">getting<span style="font-size: 12pt;">&nbsp;</span></span><span
            style="font-family: 'times new roman', 'new york', times,
            serif; background-color: white; font-size: 12pt; ">the
            following error i</span><span class="apple-converted-space"
            style="font-family: 'times new roman', 'new york', times,
            serif; background-color: white; font-size: 12pt; ">&nbsp;</span><span
            class="mark" style="font-family: 'times new roman', 'new
            york', times, serif; background-color: white; font-size:
            12pt; ">dont</span><span class="apple-converted-space"
            style="font-family: 'times new roman', 'new york', times,
            serif; background-color: white; font-size: 12pt; ">&nbsp;</span><span
            style="font-family: 'times new roman', 'new york', times,
            serif; background-color: white; font-size: 12pt; ">know how
            to handle</span><span class="apple-converted-space"
            style="font-family: 'times new roman', 'new york', times,
            serif; background-color: white; font-size: 12pt; ">&nbsp;</span><span
            class="mark" style="font-family: 'times new roman', 'new
            york', times, serif; background-color: white; font-size:
            12pt; ">dbo</span><span style="font-family: 'times new
            roman', 'new york', times, serif; background-color: white;
            font-size: 12pt; ">.
            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&nbsp;</span></div>
        <div><span style="font-family: 'times new roman', 'new york',
            times, serif; background-color: white; font-size: 12pt; "><br>
          </span></div>
        <div><span style="background-color: white; ">
            <div>CREATE OR REPLACE VIEW vwkbcomparesites as</div>
            <div>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,&nbsp;</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>b.chr
              chr_b, b.start start_b, b."end" end_b, (b."end" -
              b.start)+1 tagsize_b,</div>
            <div>&nbsp; &nbsp; abs((a."end" + a.start)/2 - (b."end" + b.start)/2)
              centredistance,</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>case&nbsp;</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>when
              a."end" &lt;= b."end" and a.start &gt;= b.start &nbsp;</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>then
              (a."end" - a.start)&nbsp;</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>when
              b."end" &lt;= a."end" and b.start &gt;= a.start &nbsp;&nbsp;</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>then
              (b."end" - b.start)&nbsp;</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>when
              a."end" &lt;= b."end" and a.start &lt;= b.start &nbsp; &nbsp;</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>then
              (a."end" - b.start)&nbsp;</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>when
              a."end" &gt;= b."end" and a.start &gt;= b.start</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>then
              (b."end" - a.start) &nbsp;</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>end

              bpoverlap</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>from
              &nbsp;dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>
              inner join dbo.kbdetails kbd on a.kbid=kbd.kbid</div>
            <div><span class="Apple-tab-span" style="white-space:pre"> </span>where
              kbd.active='1' ;</div>
            <div style="font-family: 'times new roman', 'new york',
              times, serif; font-size: 12pt; "><br>
            </div>
            <div style="font-family: 'times new roman', 'new york',
              times, serif; font-size: 12pt; ">i am getting this error ,
              how can i fix this.</div>
            <div style="font-family: 'times new roman', 'new york',
              times, serif; font-size: 12pt; "><br>
            </div>
            <div>
              <div>ERROR: &nbsp;schema "dbo" does not exist</div>
              <div>LINE 15: &nbsp;from &nbsp;dbo.kbsites a inner join dbo.kbsites
                b on a.chr=b.chr</div>
              <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ^</div>
              <div><br>
              </div>
              <div>********** Error **********</div>
              <div><br>
              </div>
              <div>ERROR: schema "dbo" does not exist</div>
              <div>SQL state: 3F000</div>
              <div>Character: 761</div>
              <div style="font-family: 'times new roman', 'new york',
                times, serif; font-size: 12pt; "><br>
              </div>
            </div>
          </span></div>
      </div>
    </blockquote>
    <br>
  </body>
</html>

--------------030909040002090407040702--