I am running this SQL statement in php 4.39 to Postgresql 7.3.4 and I
keep getting this error

Warning: pg_query(): Query failed: ERROR: syntax error at or near
"WITHOUT" at character 29 in /var/www/html/liberty/includes/test2.php
on line 12

I can't figure out why this is failing.

$buildtableQ = "CREATE TABLE $tempname WITHOUT OIDS AS
SELECT rt.resid, max(rt.transdate)
FROM li_roomtransfer as rt
JOIN li_resident AS r ON rt.resid = r.resid
WHERE r.nursehomeid = '1'AND r.distribid = '1'
GROUP BY rt.resid";
$buildtableR = pg_query($buildtableQ); This is Line 12. any
suggestions?

On Wed, 09 Aug 2006 16:53:37 -0700, rich wrote:

$buildtableQ = "CREATE TABLE $tempname WITHOUT OIDS AS
SELECT rt.resid, max(rt.transdate)
FROM li_roomtransfer as rt
JOIN li_resident AS r ON rt.resid = r.resid
WHERE r.nursehomeid = '1'AND r.distribid = '1'
GROUP BY rt.resid";
$buildtableR = pg_query($buildtableQ); This is Line 12. any
suggestions?

What is $tempname set to?

Adam

--
Adam Harvey

To e-mail: don't make an example out of me!

Adam Harvey wrote:

On Wed, 09 Aug 2006 16:53:37 -0700, rich wrote:

>
What is $tempname set to?
>
Adam
>
--
Adam Harvey
>
To e-mail: don't make an example out of me!

Adam I wrote a small function to get temporary table names.
Here it is. I know this peice works

function temptablename() {
$gettempnumQ = "SELECT * FROM li_temptables";
$gettempnumR = pg_query($gettempnumQ)or die("The query fail".
pg_error());
$tempnumA = pg_fetch_array($gettempnumR);
$tempnum = $tempnumA['tempnum'];
$newnum = $tempnum + 1;
$incrementQ="UPDATE li_temptables SET tempnum = '$newnum' where tempnum
= '$tempnum'";
$incrementR = pg_query($incrementQ) or die("The increment query
failed". pg_error());
$temptable = "li_temptable".$tempnum;
return $temptable;
}

$tempname = temptablename(); This is where $tempname comes from.

rich wrote:

I am running this SQL statement in php 4.39 to Postgresql 7.3.4 and I
keep getting this error
>
Warning: pg_query(): Query failed: ERROR: syntax error at or near
"WITHOUT" at character 29 in /var/www/html/liberty/includes/test2.php
on line 12
>
I can't figure out why this is failing.
>
$buildtableQ = "CREATE TABLE $tempname WITHOUT OIDS AS
SELECT rt.resid, max(rt.transdate)
FROM li_roomtransfer as rt
JOIN li_resident AS r ON rt.resid = r.resid
WHERE r.nursehomeid = '1'AND r.distribid = '1'
GROUP BY rt.resid";
$buildtableR = pg_query($buildtableQ); This is Line 12. any
suggestions?

Hi,

You misplaced the WITHOUT OIDS.
Read more here:
http://www.postgresql.org/docs/7.3/i...eatetable.html

Regards,
Erwin Moller

PS: What do you have against OIDs? :P

Erwin Moller wrote:

rich wrote:
>

>
>
Hi,
>
You misplaced the WITHOUT OIDS.
Read more here:
http://www.postgresql.org/docs/7.3/i...eatetable.html
>
Regards,
Erwin Moller
>
PS: What do you have against OIDs? :P

I have done some reading and I can't remember where that states if you
don't need to use OID's don't. There is a finite number of them within
the database and you can hit that limit. I know its a huge number, but
why use them? I usually have my primary keys on each of the tables I
design. Isn't that redundant?

"rich" <rhdyes@gmail.comwrote:

>
>I have done some reading and I can't remember where that states if you
>don't need to use OID's don't. There is a finite number of them within
>the database and you can hit that limit. I know its a huge number, but
>why use them?

4 billion. It takes one hell of a lot of INSERTs to get to 4 billion.

>I usually have my primary keys on each of the tables I design. Isn't
>that redundant?

Usually.

(That's a little joke. If you "usually have primary keys", then OIDs will
usually be redundant.)
--
- Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

Tim Roberts wrote:

"rich" <rhdyes@gmail.comwrote:

>
4 billion. It takes one hell of a lot of INSERTs to get to 4 billion.
>

>
Usually.
>
(That's a little joke. If you "usually have primary keys", then OIDs will
usually be redundant.)

LOL, indeed. :-)
To OP: I was also joking when I asked what your problem was with OIDs.
You don't need them if you design your databases good (=all tables have
PK/serials and all references are enforced via Foreign Keys).

Regards,
Erwin Moller