Re: [SQL] An order by question
> I need a little help on a sorting problem. Imagine a table, call it > records, that has fields: > > lastName > firstName > term > > I want to sort the records by last name, then first name, and finally by > term. This almost does what I want: > > select * from records order by lastName, firstName, term; > > However, the possible values for term are: > > 2002F > 2003S > 2003X > 2003F > > Where F is for fall, S for spring, and X for summer session. Thus, a > straight alphabetical sort doesn't give me what I want. If the year is the > same, then I want a sort with S, then X, then F for identical year. Consider storing the session in a separate field and assigning each session a value that collates in the order you want. Here's a way to order the sessions using their current format: SELECT * FROM records ORDER BY lastName, firstName, SUBSTRING(term FROM 1 FOR 4), CASE SUBSTRING(term FROM 5) WHEN 'S' THEN 1 WHEN 'X' THEN 2 ELSE 3 END; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] An order by question
Dnia 2004-02-01 14:34, Użytkownik Michael Fuhr napisał: Consider storing the session in a separate field and assigning each session a value that collates in the order you want. Here's a way to order the sessions using their current format: SELECT * FROM records ORDER BY lastName, firstName, SUBSTRING(term FROM 1 FOR 4), CASE SUBSTRING(term FROM 5) WHEN 'S' THEN 1 WHEN 'X' THEN 2 ELSE 3 END; or the easier one: order by lastname,firstname, translate(term,'SXF','123'); Regards, Tomasz Myrta ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] REPLACE
LK I'm using Foxpro 8 and I'm trying to merge selective data from 4 different tables into a new table.The problem is that the tables are not alike, Table one may have 4 records with the name John in them, table 2 had only one John with one account # in that record, so I want to have a new table with the 4 Johns(Same Johns) and the account number each time after his name. So here is what I did. I selected data from each table and stored them in an array, 3 of the four table have a common field which is, 'Name' Create table data Name char (12),; Address char (20),; Phone Char (10),; Account Char (20),; Order N(15),; Date Date,; (etc.) Select Name, Address, Phone (etc.) From Table 1; where Table1.date= todays date; INTO ARRAY ABC (Then I count the number of records) Num1=Alen(ABC,1) Select Name, AccountN, Order, time (etc) from Table2; Where Table2.date= todays date; INTO ARRAY DFE (Then I count the number of records) Num2=Alen(DFE,1) Now since Table 1 has the most data I move that data into the new table. Use Data Append blank For XXX=1 to Num1(Record count from array) Replace Name with ABE(xxx,1) Replace Address with ABE(xxx,2) Replace Phone with ABE(xxx,3) (Etc) Skip Endfor|Next So far so good, now here is where the problem happens. Let's say Johnname appears 4 times in table 1, and had one account Num in table 2, lets say it's ASD234 and I have 36 records all together from Table 1. Now I try and replace the account number, there are only 9 different records in table 2, 9 account numers to match to the 36 records from Table 1. For SA = 1 to Num2(Second array count total of 9 records) Replace Account with DEF[SA,1] Where ABC[1,1]=DEF[SA,1] ENDFOR|Next Now what happens is that it replaces all 36 records with the same account number, ASD234, it seems to ignore the, ABC[1,1]=DEF SA[SA,1]. What I was looking for was this John ASD234 John ASD234 John ASD234 John ASD234 Joe (no number) Joe (no number) Mike (no number) Etc. What I get is this, John ASD234 John ASD234 John ASD234 John ASD234 Joe ASD234 Joe ASD234 Mike ASD234 Etc. I also tried to use the For command, for ABC[1,1]=DEF SA[SA,1]. I tried using the Locate command and the Insert command, but same thing, it doesn't stop replacing account wqith the same account number, all 36 records. For some reason it's not comparing the two expressions like it should and stopping when they no longer equal each other. Maybe you can't compare 2 arrays like I'm trying to do. I would be most thankful if anyone can solve this problem. Any idea why it's ignore the equal sign and just replacing them all? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] postgreSQL and Hibernate
hi, just wanted to know whether anyone on this group uses Hibernate in conjunction with postgreSQL 7.4.1? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Executing dynamic queries (EXECUTE)
Hello! I need to execute a dynamic query and capture your result in a integer variable. I´m using the statement "EXECUTE string", but I don´t obtain to capture the result of dynamic query. See the example: == create or replace function f_population_check() returns bigint as'declare v_tot bigint; v_query varchar(4000); v_count integer; r record;begin v_tot:= 0; for r in select * from pg_tables loop v_count:= 0; v_query := ''select count(*) from '' || r.tablename; v_count:= EXECUTE v_query; if v_count = 0 then RAISE NOTICE ''Empty table % '',r.tablename; end if; v_tot:= v_tot + 1; end loop; return v_tot;end;'language 'plpgsql'; == ERROR: parse error at or near "$1" at character 18CONTEXT: PL/pgSQL function "f_population_check" line 11 at assignment == Somebody could help me? Thanks, Carla Mello.
[SQL] Postgres "dblink" Help
Hi,
I have Two databases names 'MyDb1' and 'MyDb2' in My
System.
I have installed Postgres 7.4 with dblink
What i need is, when ever a NEW record is inserted in
'MyDb1' , i want fire a Trigger or Rule to insert the
Same Record to MyDb2 database.
I am Using MAC OS 10.2
Anybody Got Idea how to do this?
Thanks
--
Some Sample Query Using "dblink" is:
select * from dblink('hostaddr=192.168.1.1
dbname=MyDb1 user=pgsql','select
username,password,usertype from users') as t1(username
varchar,password varchar,usertype varchar);
--
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] An order by question
On Sun, Feb 01, 2004 at 06:22:30PM +0100, Tomasz Myrta wrote: > Dnia 2004-02-01 14:34, U¿ytkownik Michael Fuhr napisa³: > > SELECT * > > FROM records > > ORDER BY lastName, > > firstName, > > SUBSTRING(term FROM 1 FOR 4), > > CASE SUBSTRING(term FROM 5) > >WHEN 'S' THEN 1 > >WHEN 'X' THEN 2 > >ELSE 3 > > END; > > or the easier one: > > order by lastname,firstname, translate(term,'SXF','123'); Blast...I just *knew* I was missing an easy, obvious solution. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Executing dynamic queries (EXECUTE)
Dnia 2004-01-27 16:25, Użytkownik Carla Mello napisał: Hello! I need to execute a dynamic query and capture your result in a integer variable. I´m using the statement "EXECUTE string", but I don´t obtain to capture the result of dynamic query. v_count:= EXECUTE v_query; Documentation: 19.5.4. Executing dynamic queries "The results from SELECT queries are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. So, the only way to extract a result from a dynamically-created SELECT is to use the FOR-IN-EXECUTE form described later." Regards, Tomasz Myrta ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] update more than 1 table (mysql to postgres)
I am migrating from MySQL to Postagres. I have problem with postgres updating 2 tables with one statement. In MySQL I can update 2 tables (parent, child) with a statement like this UPDATE parent LEFT JOIN child ON parent.pid = child.foreign_key SET parent.field1 = 'company', child.field2 = 'john' WHERE child.pid = 7 Or I can also do UPDATE parent, child SET parent.field1 = 'company', child.field2 = 'john' WHERE parent.pid = child.foreign_key AND child.pid = 7 But I couldn't do that in Postgres, Only one table is allowed in an update statement. I tried to create a view, and updating the view, but that was not allowed. I could do 2 SQL updates, but I am sure there is a better way to do this. Anyone have any idea. Appreciated. Tree ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Help! Error with postgresql!
Hello, I have problem when i execute the .sql file in my psql command prompt. The error code is below: pylori=# \i log-clinique.sql CREATE psql:log-clinique.sql:100: ERROR: parser: parse error at or near ".3" psql:log-clinique.sql:107: ERROR: parser: parse error at or near ".3" psql:log-clinique.sql:113: ERROR: parser: parse error at or near ".3" pylori=# \i trigger-clinique.sql " does not exist in the system catalog. createlang: external error DROP CREATE DROP CREATE pylori=# I hope some of you can help me with this. Thank a lot, raingsey
Re: [SQL] Help! Error with postgresql!
Would you mind telling us what's in your SQL file? it seems that there's a problem in it. On 28 Jan 2004, at 05:12, [EMAIL PROTECTED] wrote: Hello, I have problem when i execute the .sql file in my psql command prompt. The error code is below: pylori=# \i log-clinique.sql CREATE psql:log-clinique.sql:100: ERROR: parser: parse error at or near ".3" psql:log-clinique.sql:107: ERROR: parser: parse error at or near ".3" psql:log-clinique.sql:113: ERROR: parser: parse error at or near ".3" pylori=# \i trigger-clinique.sql " does not exist in the system catalog. createlang: external error DROP CREATE DROP CREATE pylori=# I hope some of you can help me with this. Thank a lot, raingsey ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [PERFORM] Set-Returning Functions WAS: On the performance of
Josh Berkus wrote: Bill, First off: discussion moved to the SQL list, where it really belongs. True, it started out as [PERFORM], but is no longer. Well, I would have agreed with the uselessness, until this project. The "source of endless debugging" frightens me! Well, the last time I tried to use this capability was SQL Server 7. On that model, the problems I found were: 1) There was no good way to differentiate the recordsets returned; you had to keep careful track of what order they were in and put in "fillers" for recordsets that didn't get returned. 2) Most of the MS client technology (ODBC, ADO) was not prepared to handle multiple recordsets. I ended up hiring a C-based COM hacker to write me a custom replacement for ADO so that we could handle procedure results reliably. Well, they're already handling what MSSQL gives them in their prototype, so that's not my problem. This can be done with Set Returning Functions. The issue is that the call to the function requires special syntax, and the program calling the function must know what columns are going to be returned at the time of the call. Hmmm, is that clear or confusing? Clear as mud. In my case, my application simply doesn't care what row of what kind are returned. See, I'm writing the server end, and all said and done, it's really just glue (frighteningly thick glue, but glue nonetheless) To be clearer: You can create a Set Returning Function (SRF) without a clearly defined set of return columns, and just have it return "SETOF RECORD". However, when you *use* that function, the query you use to call it needs to have a clear idea of what columns will be returned, or you get no data. I don't understand at all. If I do "SELECT * FROM set_returning_function()" and all I'm going to do is iterate through the columns and rows, adding them to a two dimensional array that will be marshalled as a SOAP message, what about not knowing the nature of the return set can cause me to get no data? All of this is very hackneyed, as I'm sure you realize. Well, the way this project is being done tends to cause that. It was written in VB, it's being converted to VB.NET ... the original backend was MSSQL, now it's being converted to PostgreSQL with C glue to make PostgreSQL talk SOAP ... and all on the lowest budget possible. Overall, I'd say that the programming team you've been inflicted with don't like relational databases, or at least have no understanding of them. Quite possibly. It's amazing to me how well I've apparently self-taught myself relational databases. I've spoken with a lot of people who have had formal schooling in RDBMS who don't really understand it. And I've seen LOTs of applications that are written so badly that it's scarey. I mean, check out http://www.editavenue.com ... they wanted me to optimize their database to get rid of the deadlocks. I've been totally unable to make them understand that deadlocks are not caused by poor optimization, but by poor database programmers who don't really know how to code for multi-user. As a result, I've probably lost the work, but I'm probably better off without it. One of the things I love about working with open source databases is I don't see a lot of that. The people on these lists are almost always smarter than me, and I find that comforting ;) -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL]
Hi! I want to insert values into a table using the C syscalls provided by the libpq library, but i find that i cant insert into the table when i use variables instead of values...like so: int a,b,c,d; using the C function res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); Is there some special way to insert variables rather than plain values? do i have to build functions (in sql) or somehting?help! Thanx Mukta
Re: [SQL] [PERFORM] Set-Returning Functions WAS: On the performance of
Josh Berkus wrote: Bill, I don't understand at all. If I do "SELECT * FROM set_returning_function()" and all I'm going to do is iterate through the columns and rows, adding them to a two dimensional array that will be marshalled as a SOAP message, what about not knowing the nature of the return set can cause me to get no data? Because that's not the syntax for a function that returns SETOF RECORD. The syntax is: SELECT * FROM set_returning_function(var1, var2) AS alias (col1 TYPE, col2 TYPE); That is, if the function definition does not contain a clear row structure, the query has to contain one. This does not apply to functions that are based on a table or composite type: CREATE FUNCTION RETURNS SETOF table1 ... CREATE FUNCTION RETURNS SETOF comp_type Can be called with: SELECT * FROM some_function(var1, var2) as alias; What this means is that you have to know the structure of the result set, either at function creation time or at function execution time. Yep. You're right, I hadn't looked at that, but I'm probably better off creating types and returning setof those types as much as possible. One of the things I love about working with open source databases is I don't see a lot of that. The people on these lists are almost always smarter than me, and I find that comforting ;) Flattery will get you everywhere. Really? I'll have to use it more often. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] update more than 1 table (mysql to postgres)
On Fri, 30 Jan 2004, treeml wrote: > I am migrating from MySQL to Postagres. I have problem with postgres > updating 2 tables with one statement. > > In MySQL I can update 2 tables (parent, child) with a statement like this > > UPDATE parent LEFT JOIN child ON parent.pid = child.foreign_key SET > parent.field1 = 'company', > child.field2 = 'john' > WHERE child.pid = 7 > > Or I can also do > UPDATE parent, child SET parent.field1 = 'company', child.field2 = 'john' > WHERE > parent.pid = child.foreign_key > AND child.pid = 7 > > > But I couldn't do that in Postgres, > Only one table is allowed in an update statement. I tried to create a view, > and updating the view, but that was not allowed. I could do 2 SQL > updates, but I am sure there is a better way to do this. Anyone have any > idea. Appreciated. You can use a transaction: begin; update parent set ...; update child set ...; commit; Or if you want to use a rule, you can define a rule to do it: create or replace rule my_view_update_rule as on update to my_view do instead ( ... -j -- Jamie Lawrence[EMAIL PROTECTED] "Perhaps the truth is less interesting than the facts?" - Amy Weiss, Senior Vice President of Communications, RIAA ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] update more than 1 table (mysql to postgres)
Oops! [EMAIL PROTECTED] ("treeml") was seen spray-painting on a wall:
> I am migrating from MySQL to Postagres. I have problem with postgres
> updating 2 tables with one statement.
>
> In MySQL I can update 2 tables (parent, child) with a statement like this
>
> UPDATE parent LEFT JOIN child ON parent.pid = child.foreign_key SET
> parent.field1 = 'company',
> child.field2 = 'john'
> WHERE child.pid = 7
>
> Or I can also do
> UPDATE parent, child SET parent.field1 = 'company', child.field2 = 'john'
> WHERE
> parent.pid = child.foreign_key
> AND child.pid = 7
>
>
> But I couldn't do that in Postgres,
> Only one table is allowed in an update statement. I tried to create a view,
> and updating the view, but that was not allowed. I could do 2 SQL
> updates, but I am sure there is a better way to do this. Anyone have any
> idea. Appreciated.
Well, what's going on here is that PostgreSQL is conforming to the SQL
standards. The syntax MySQL is providing is clearly a deviation from
standards, and once you head down the "we're ignoring standards" road,
you cannot have _any_ expectations of things functioning similarly
(or, for that matter, FUNCTIONING AT ALL) with another database
system.
There are several _possible_ solutions to this; which one is
preferable is certainly in the eye of the beholder:
1. Do two UPDATEs inside a transaction. Perhaps...
begin;
update parent set field1 = 'company' where exists (select *
from child where foreign_key = parent.pid and child.pid = 7);
update child set field2 = 'john' where pid = 7 and exists
(select * from parent where parent.pid = child.foreign_key);
commit;
2. You might set up a view that joins the tables, such as
create view combination as
select parent.pid as parent_pid, child.pid as child_pid,
parent.field1, child.field2
from parent, child
where parent.pid = child.foreign_key;
and then create a RULE allowing updates to this view that allows
updating whichever fields that it is appropriate to allow this on.
This will involve fairly fancy footwork, unfortunately. It's
doable, but there's something of a learning curve...
In either case, there are still two update statements; in the
updatable VIEW situation, they hide a little "out of VIEW" (if you'll
pardon the pun!).
--
"cbbrowne","@","ntlug.org"
http://www3.sympatico.ca/cbbrowne/languages.html
Rules of the Evil Overlord #41. "Once my power is secure, I will
destroy all those pesky time-travel devices."
Re: [SQL] update more than 1 table (mysql to postgres)
>> In MySQL I can update 2 tables (parent, child) with a statement like this >> >> UPDATE parent LEFT JOIN child ON parent.pid = child.foreign_key SET >> parent.field1 = 'company', >> child.field2 = 'john' >> WHERE child.pid = 7 One has to wonder what the above construction is even intended to mean. Since it's a LEFT JOIN, presumably there can be rows coming out of the join that have a "parent" but no "child" row. What does it mean to update child.field2 when there's no child row? You could make about equally good arguments for raising an error, updating the parent side only, or updating neither. Even without a LEFT JOIN, I don't understand what's expected to happen when the same row in one table joins to multiple rows in the other table. One advantage of following standards is that the standards have usually been thought through in some detail (though there are crannies of the SQL spec that hardly seem to deserve that description :-(). This thing has not been thought through. I'm sure the actual behavior of the corner cases in MySQL is just whatever happened to fall out of their implementation. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
