Thread: Query (view) question

Query (view) question

From

Art Fore

Date:

12 February 2007, 09:40:23

I have been trying to get a query going in a postgres database, a simple
on at that, and have had no success expcept using Access.

Here is the query that works in Access.

SELECT remote_ALL_PARTS.PARTNUM, public_datasheet_address.Datasheet &
remote_ALL_PARTS.Datasheet AS Expr1, public_datasheet_address.Datasheet
& remote_ALL_PARTS.Outline AS Expr2, public_datasheet_address.Footprint
& remote_ALL_PARTS.Footprint AS Expr3
FROM remote_ALL_PARTS, public_datasheet_address
WHERE (((remote_ALL_PARTS.PARTNUM) Like "091-" & "*"));


I tried this as a view with pgadmin with slight mods, This one below
works

SELECT "ALL_PARTS"."PARTNUM", "ALL_PARTS"."Datasheet",
"ALL_PARTS"."Outline", "ALL_PARTS"."Footprint"  FROM "ALL_PARTS" "ALL_PARTS" WHERE "ALL_PARTS"."PARTNUM"::text ~~
'021-%'::text;

But, if I do the following

SELECT "ALL_PARTS"."PARTNUM", "datasheet_address","sheet" ||
"ALL_PARTS"."Datasheet", "ALL_PARTS"."Outline", "ALL_PARTS"."Footprint"  FROM "ALL_PARTS" "ALL_PARTS",
"datasheet_address"
"datasheet_address" WHERE "ALL_PARTS"."PARTNUM"::text ~~ '021-%'::text;

I get an error that it cannot modify "Datasheet"

If I change to 

SELECT "ALL_PARTS"."PARTNUM", "datasheet_address","sheet" ||
"ALL_PARTS"."Datasheet" AS "Exp1", "ALL_PARTS"."Outline",
"ALL_PARTS"."Footprint"  FROM "ALL_PARTS" "ALL_PARTS", "datasheet_address"
"datasheet_address" WHERE "ALL_PARTS"."PARTNUM"::text ~~ '021-%'::text;

I still get a similar error.


Have searched for concatenating to fields, but had no success. Can
anyone help? I hope so, otherwise, will have to go to a Access database
and I do not want to do that.

Art

Re: Query (view) question

From

"Joris Dobbelsteen"

Date:

12 February 2007, 09:59:47

>-----Original Message-----
>From: pgadmin-support-owner@postgresql.org
>[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Art Fore
>Sent: maandag 12 februari 2007 11:40
>To: Pgadmin-Support
>Subject: [pgadmin-support] Query (view) question
>
[snip]
>I tried this as a view with pgadmin with slight mods, This one
>below works
>
>SELECT "ALL_PARTS"."PARTNUM", "ALL_PARTS"."Datasheet",
>"ALL_PARTS"."Outline", "ALL_PARTS"."Footprint"
>   FROM "ALL_PARTS" "ALL_PARTS"
>  WHERE "ALL_PARTS"."PARTNUM"::text ~~ '021-%'::text;
>
>But, if I do the following
>
>SELECT "ALL_PARTS"."PARTNUM", "datasheet_address","sheet" ||
>"ALL_PARTS"."Datasheet", "ALL_PARTS"."Outline", "ALL_PARTS"."Footprint"
>   FROM "ALL_PARTS" "ALL_PARTS", "datasheet_address"
>"datasheet_address"
>  WHERE "ALL_PARTS"."PARTNUM"::text ~~ '021-%'::text;

Notice the comma between "datasheet_address","sheet". I doesn't seem
intentional.

Try that, otherwise look up the column types and try casting them.

With parsers (especially compilers) there seems to be a relation between
clariness of the error messages and how widespread the software is.
For Postgres there are three options:
1) Its not widespread, justifying the error messages
2) Its error messages indicate its not widespread
3) It has defied the odds.

Slight off-topic note:
Furthermore, you can get rid of all the ugly " if you name everything
with only lowercase letters. When not quoting the names Postgres seems
to do some name mangling (convert to lowercase and search for it in a
case-sensative way with the unconverted names).

- Joris

Re: Query (view) question

From

Art Fore

Date:

12 February 2007, 11:04:14

On Mon, 2007-02-12 at 12:00 +0100, Joris Dobbelsteen wrote:
> >-----Original Message-----
> >From: pgadmin-support-owner@postgresql.org 
> >[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Art Fore
> >Sent: maandag 12 februari 2007 11:40
> >To: Pgadmin-Support
> >Subject: [pgadmin-support] Query (view) question
> >
> [snip]
> >I tried this as a view with pgadmin with slight mods, This one 
> >below works
> >
> >SELECT "ALL_PARTS"."PARTNUM", "ALL_PARTS"."Datasheet", 
> >"ALL_PARTS"."Outline", "ALL_PARTS"."Footprint"
> >   FROM "ALL_PARTS" "ALL_PARTS"
> >  WHERE "ALL_PARTS"."PARTNUM"::text ~~ '021-%'::text;
> >
> >But, if I do the following
> >
> >SELECT "ALL_PARTS"."PARTNUM", "datasheet_address","sheet" || 
> >"ALL_PARTS"."Datasheet", "ALL_PARTS"."Outline", "ALL_PARTS"."Footprint"
> >   FROM "ALL_PARTS" "ALL_PARTS", "datasheet_address"
> >"datasheet_address"
> >  WHERE "ALL_PARTS"."PARTNUM"::text ~~ '021-%'::text;
> 
> Notice the comma between "datasheet_address","sheet". I doesn't seem
> intentional.
> 
> Try that, otherwise look up the column types and try casting them.
> 
> With parsers (especially compilers) there seems to be a relation between
> clariness of the error messages and how widespread the software is.
> For Postgres there are three options:
> 1) Its not widespread, justifying the error messages
> 2) Its error messages indicate its not widespread
> 3) It has defied the odds.
> 
> Slight off-topic note:
> Furthermore, you can get rid of all the ugly " if you name everything
> with only lowercase letters. When not quoting the names Postgres seems
> to do some name mangling (convert to lowercase and search for it in a
> case-sensative way with the unconverted names).
> 
> - Joris
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Comma was a type in the email, which I edited since the view did not
save my edits, it was not in the orginal that I tried from PGadmin. The
comma causes another error though.

What do you mean by "casting"? I am no database expert and have not
encountered that before. Could I have an example? I did have a problem
with data types, but changed the datasheets_address xolumns to vchar(64)
to mach the column types in All_Part. which seemed to fix that problem.

Also a note, I tried find/replace in 1.7 and that did not work for
modifying the All_Parts table. Tried Rekall, it opens the database and I
can see the tables and views, etc., but anything I try to view data or
even look at the properties, it crashes. Tried Knode, but its
find/replace also did not work. Ended up using a make table query in
Access from the linked postgres database, doing a search/replace on it,
then updating the table with an update query. That did not work at
first, but changed the ODBC driver from UTF-8 to ANSI, then it worked.
Don't understand why since the database is UTF-8. If I had discovered
this earlier, I might could have done the search/replace direcctly on
the linked table instead of the work around with Access. 

Sure would be nice and helpful for postgres if there was an Access like
application for postgres that worked as well as Access it does, although
I do not like MS or Access, for my purposes, it does work. On the other
hand, the application that uses the database only as read only,
DxExchange from Mentor Graphics, is about 10 to 100 times times faster
using postgres rather than Access, also, Access has a limit on
simultaneous users.

Art

Re: Query (view) question

From

Raymond O'Donnell

Date:

12 February 2007, 13:14:57

Art Fore wrote:

> Sure would be nice and helpful for postgres if there was an Access like
> application for postgres that worked as well as Access it does, although

There is - it's called pgAdmin! :)

Seriously, Access tries to be both a GUI and a database; PostgreSQL is a 
database only, and does that supremely well.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------