Thread: error with mor than 1 sub-select
Hi, I am fairly new to PostgreSQL but not to SQL. Is this a bug or a limitation... The first query works fine, but when a second sub-query is added I get a syntax error... psql protocal2 -U p2user << EOF1 select * from serv_res SR where serv_key = 10 and not exists (select 1 from reservation R ) -- and not exits -- (select 1 -- from interval R1 -- ) order by 1 ; select * from serv_res SR where serv_key = 10 and not exists (select 1 fromreservation R ) and not exits (select 1 from interval R1 ) order by 1 ; EOF1 The output is as follows: Password for user p2user:serv_key | res_key | start_off_min | duration_min ----------+---------+---------------+-------------- (0 rows) ERROR: syntax error at or near "select" at character 192 LINE 9: (select 1 ^ Note: This is not the actual query I want to run but just kept removing complexity until the problem (multiple sub-selects) seemed obvious. -- View this message in context: http://www.nabble.com/error-with-mor-than-1-sub-select-tf2149934.html#a5936948 Sent from the PostgreSQL - sql forum at Nabble.com.
On Tue, Aug 22, 2006 at 06:47:51PM -0700, chrisj wrote: > The first query works fine, but when a second sub-query is added I get a > syntax error... > > psql protocal2 -U p2user << EOF1 > select * > from serv_res SR > where serv_key = 10 > and not exists > (select 1 > from reservation R > ) > -- and not exits Does the real query have "exits" instead of "exists"? I created some test tables and fixed that typo and then both queries worked. -- Michael Fuhr
<br /><br /><b><i>chrisj <chrisj.wood@sympatico.ca></i></b> escreveu:<blockquote class="replbq" style="border-left:2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br />Hi,<br /><br />I am fairly newto PostgreSQL but not to SQL. Is this a bug or a<br />limitation...<br /><br />The first query works fine, but when asecond sub-query is added I get a<br />syntax error...<br /><br />psql protocal2 -U p2user << EOF1<br /> select *<br/> from serv_res SR<br /> where serv_key = 10<br /> and not exists <br /> (select 1<br /> from reservation R<br /> )<br/>-- and not exits <br />-- (select 1 <br />-- from interval R1<br />-- )<br />order by 1<br />;<br /> select *<br />from serv_res SR<br /> where serv_key = 10<br /> and not exists <br /> (select 1<br /> from reservation R<br /> )<br />and not exits<br /></blockquote><br />exi*S*ts<br /><br /><blockquote class="replbq" style="border-left: 2px solid rgb(16,16, 255); margin-left: 5px; padding-left: 5px;"> (select 1<br /> from interval R1<br /> )<br />order by 1<br />;<br/><br />EOF1<br /><br />The output is as follows:<br /><br />Password for user p2user:<br /> serv_key | res_key | start_off_min| duration_min<br />----------+---------+---------------+--------------<br />(0 rows)<br /><br />ERROR: syntaxerror at or near "select" at character 192<br />LINE 9: (select 1<br /> ^<br /><br />Note: This is not the actual queryI want to run but just kept removing<br />complexity until the problem (multiple sub-selects) seemed obvious.<br />--<br />View this message in context: http://www.nabble.com/error-with-mor-than-1-sub-select-tf2149934.html#a5936948<br/>Sent from the PostgreSQL - sql forum atNabble.com.<br /><br /></blockquote><br /><br /><br />[]s<br />Osvaldo<br /><br /><p><hr size="1" /> Yahoo! Search<br />Música para ver e ouvir: <a href="http://us.rd.yahoo.com/mail/br/tagline/search/video/*http://br.search.yahoo.com/search/video?p=james+blunt&ei=UTF-8&cv=g&x=wrt&vm=r&fr=intl-mail-br-b">You're Beautiful,do James Blunt</a>
thanks, stupid user error. i guess the pointer on the error message led me astray Michael Fuhr wrote: > > On Tue, Aug 22, 2006 at 06:47:51PM -0700, chrisj wrote: >> The first query works fine, but when a second sub-query is added I get a >> syntax error... >> >> psql protocal2 -U p2user << EOF1 >> select * >> from serv_res SR >> where serv_key = 10 >> and not exists >> (select 1 >> from reservation R >> ) >> -- and not exits > > Does the real query have "exits" instead of "exists"? I created > some test tables and fixed that typo and then both queries worked. > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > > -- View this message in context: http://www.nabble.com/error-with-more-than-1-sub-select-tf2149934.html#a5937248 Sent from the PostgreSQL - sql forum at Nabble.com.
Actually, it pointed you right to it. Notice that exits is just before where the pointer was. chrisj wrote: > thanks, stupid user error. > > i guess the pointer on the error message led me astray > > > Michael Fuhr wrote: > >> On Tue, Aug 22, 2006 at 06:47:51PM -0700, chrisj wrote: >> >>> The first query works fine, but when a second sub-query is added I get a >>> syntax error... >>> >>> psql protocal2 -U p2user << EOF1 >>> select * >>> from serv_res SR >>> where serv_key = 10 >>> and not exists >>> (select 1 >>> from reservation R >>> ) >>> -- and not exits >>> >> Does the real query have "exits" instead of "exists"? I created >> some test tables and fixed that typo and then both queries worked. >> >> -- >> Michael Fuhr >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> >> >> > > -- erik jones <erik@myemma.com> software development emma(r)