Re: [SQL] count question
Volkan YAZICI wrote: > On Wed, 9 Apr 2008, novice <[EMAIL PROTECTED]> writes: > >> Is it possible to write a query to produce: >> >> meter_id | no_of_bays | bay_id >> --++--- >> 5397 | 2 | 5397-01 >> 5397 | 2 | 5397-02 >> 5409 | 3 | 5409-01 >> 5409 | 3 | 5409-02 >> 5409 | 3 | 5409-03 >> > > Shouldn't this function be the responsitibility of client software, > instead of database? > > > Regards. > May be this will help you ashish=# select meter_id,no_of_bays,meter_id||'-'||generate_series(1,no_of_bays) from meter; meter_id | no_of_bays | ?column? --++------ 5397 | 2 | 5397-1 5397 | 2 | 5397-2 5409 | 3 | 5409-1 5409 | 3 | 5409-2 5409 | 3 | 5409-3 With Regards Ashish === sms START NETCORE to 575758 to get updates on Netcore's enterprise products and services sms START MYTODAY to 09845398453 for more information on our mobile consumer services or go to http://www.mytodaysms.com ===
Re: [SQL] backup
Jyoti Seth wrote: Hi all, Can we take backup of specific data of a table (using where clause)? Thanks, Jyoti Try Selective COPY With Regards Ashish Karalkar EnterpriseDB -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Using Control Flow Functions in a SELECT Statement
Hi Team I am unable to fetch data using following simple query it prompts following error *ORA: 00907: Missing right parenthesis* Query :- SELECT Name AS Title, StatID AS Status, RatingID AS Rating, IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify FROM DVDs *Table Structure :-* CREATE TABLE DVDs ( Name VARCHAR(60) NOT NULL, NumDisks INT NOT NULL , RatingID VARCHAR(4) NOT NULL, StatID CHAR(3) NOT NULL ) rows has been inserted with different numdisks numbers. pls help warm R's
Re: [SQL] Using Control Flow Functions in a SELECT Statement
Hi Team
Thanks FOR your prompt responseBut USING CASE issue still NOT resolved
Oracle prompts same error.
Team its a simple query but really instresting one...
Following data has been updated IN TABLE DVDs
INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)
VALUES('Mash', 2, 'R', 's2'), ('View', 1, 'NR', 's1'), ('Show', 2, 'NR',
's2'), ('Amadeus', 1, 'PG', 's2') , ('Falcon', 1, 'NR', 's2'),
('Africa', 1, 'PG', 's1'), ('Doc', 1, 'G', 's2') , ('Christmas', 1, 'NR',
's1');
On 12/4/06, Bricklen Anderson <[EMAIL PROTECTED]> wrote:
Ashish Ahlawat wrote:
>
> Hi Team
>
> I am unable to fetch data using following simple query it prompts
> following error
>
> *ORA: 00907: Missing right parenthesis*
>
> Query :-
>
> SELECT
>
> Name AS Title, StatID AS Status, RatingID AS Rating,
>
> IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify
>
i. That's an Oracle error message.
ii. IF does not belong in an SQL query. Use CASE.
Re: [SQL] join and sort on 'best match'
hi pls tell me if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need join ??* *Ashish* On 12/13/06, Ragnar <[EMAIL PROTECTED]> wrote: On mið, 2006-12-13 at 10:26 +0100, Dirk Griffioen wrote: > I have been breaking my head on the following problem: how to join 2 > tables and sort the results on the best match. ^ > - there are 3 tables, items, tags and items_tags. The items_tags table > links items to tags. > - I have one item which has certain tags, and I want to look up all > the other items that have those tags as well looks to me like you want to join: items->item_tags->tags->item_tags->items so the basic select is: SELECT * FROM items AS i1 JOIN items_tags AS it1 ON (it1.item_id = i1.id) JOIN tags AS t ON (t.tag_id = it1.tag_id) JOIN items_tags AS it2 ON (it2.tag_id = t.tag_id) JOIN items AS i2 ON (i2.id = it2.item_id) WHERE i1.id=? > - results should be sorted and presented by 'best match': first all > the items that have 3 tags in common, then 2 and last 1 this would be: SELECT i1.id,i2.id,COUNT(*) as quantity FROM items AS i1 JOIN items_tags AS it1 ON (it1.item_id = i1.id) JOIN tags AS t ON (t.tag_id = it1.tag_id) JOIN items_tags AS it2 ON (it2.tag_id = t.tag_id) JOIN items AS i2 ON (i2.id = it2.item_id) WHERE i1.id=? GROUP by i1.id,i2.id ORDER BY quantity DESC > I thought I had found the solution (my test cases worked), but I now > find cases that should be found by the query but are not. if this does not work, please provide us with a counter example. gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] join and sort on 'best match'
Hi team I was just asking that If table ITEM3 has all the columns then why we need to have a join ?? even we put a sorting very easily. *** Any way I have a very intersting question to all of you. I want to fetch more that 70,000 BLOB from different customer servers. the issue is there are some BOLB files with common names on all servers. So I want merge these files into a single BLOB during fetching data. I am able to fetch the BLOB data from all cust servers but unfortunatelly it overwrite previous file. So pls provide any simple query format for the same, assuming two table tab1 & tab 2. Ashish . INDIA On 12/14/06, Markus Schaber <[EMAIL PROTECTED]> wrote: Hi, Ashish, "Ashish Ahlawat" <[EMAIL PROTECTED]> wrote: > hi pls tell me > > if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need > join ??* Please try to write in English, so we can understand and answer your questions. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] fetching & Merging BLOB
Hi team I have a very intersting question to all of you. I want to fetch more that 70,000 BLOB from different customer servers. the issue is there are some BOLB files with common names on all servers. So I want merge these files into a single BLOB during fetching data. Although I am able to fetch the BLOB data from all cust servers but unfortunatelly it overwrite previous same name file instead of merging. So pls provide any simple query format for the same, assuming two table tab1 & tab 2. Ashish . INDIA
[SQL] Fetching BLOBs
Hi team I have a very intersting question to all of you. Pls help me to build this query, I want to fetch more that 70,000 BLOB from different customer servers. the issue is there are some BOLB files with common names on all servers. So I want merge these files into a single BLOB during fetching data. I am able to fetch the BLOB data from all cust servers but unfortunatelly it overwrite previous file. So pls provide any simple query format for the same, assuming two table tab1 & tab 2. Ashish . INDIA
Re: [SQL] need help
Anyone else is using this table simulteniously? With Regards Ashish... - Original Message - From: Penchalaiah P. To: pgsql-sql@postgresql.org Sent: Monday, May 14, 2007 12:20 PM Subject: [SQL] need help Hi . Create table cdano_nya(cdano int4,nyano int4) . I created this table and then I inserted some values to this( 234576,86). Now when I am updating this table .. its not updating ..query is continuously running. When I am stopping query its giving this message..ERROR: canceling statement due to user request.. May I know the reason y its not running.. and I am unable to drop this table also.when I am selecting this table in pgAdmin..its strucking the pgAdmin... Any one can help in this Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] sql server to postgreSQL
Hello All, I have a long list of sql server queries that needs to me remoulded in to postgres format can anyone please suggest me any tool that will convert sqlserver query into postgresql query except SwisSQL - SQLOne Console 3.0 Thanks in advance With regards Ashish Karalkar
[SQL] function to find difference between in days between two dates
Hello all, Is there any function to find differences in days between two dates? I am using select abs(current_date - '2007-06-15') to get the desired result. but I think there must be a function and I am missing it, if so, can anybody please point me to that. Thanks in advance With regards Ashish Karalkar
[SQL] Using case or if to return multiple rows
Hello all, I want to select data from two diffrent table based on third tables column somthing like: select case when t3.date='' then select * from table1 else select * from table 2 from table3 t3 where t3.date='x' Problem is that I have to do it in Plain SQL. Is there a anyway. Thanks in Advance With egards Ashish
[SQL] Birth Day Calculation
Hello All, I want to diplay all the name of persons who falls in between given day and month something like select name from my_table mt where mt.date_of_birth between 7-Jul and 15 - Aug column date_of_birth is of date type is there any way to do this Thanks in advance Ashish...
Re: [SQL] List of FKeys ?
- Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Andreas" <[EMAIL PROTECTED]> Cc: Sent: Monday, August 27, 2007 9:18 AM Subject: Re: [SQL] List of FKeys ? Andreas <[EMAIL PROTECTED]> writes: could I get a list of foreign keys that refer to a column? The information_schema views constraint_column_usage and referential_constraints might help you, or you could dive into the underlying system catalogs. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend Hey Andreas is ur problem is not solved use following SP, I use it for the same reason. just pass the primary key column name and primary key value it will return u list of child table's sp_gen_foreign_keys_tables(OUT par_result text, OUT par_childtables text, IN par_colname character varying, IN par_colvalue integer) AS $BODY$ DECLARE err_data_entity varchar(100) default 'To find child records '; err_operation varchar(100) default 'Select'; curforeign refcursor ; curforeign1 refcursor; tablename text; columnname text; var_str1 text; var_str2 text; countno integer; counter integer; BEGIN par_result :='Successfull'; var_str1:='select distinct(fk_relation),fk_column from core_foreign_keys_view where pk_relation in (select pk_relation from core_foreign_keys_view where pk_column='''|| par_colname||''')'; open curforeign for execute var_str1; found:='true'; par_childtables:=''; while found ='t' loop FETCH curforeign into tablename,columnname ; var_str2:='select count(*) from '|| tablename || ' where ' || columnname ||' = '|| par_colvalue; IF VAR_STR2 IS NULL THEN EXIT; END IF; open curforeign1 for execute var_str2; FETCH curforeign1 into countno; close curforeign1; if countno > 0 then par_childtables:=par_childtables || tablename||'.'||columnname|| ',' ; end if ; end loop; close curforeign ; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Hope this will help With Regards Ashish ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to use serial variable to insert into muiti-recrods?
I think default is a key word whic u r using as a column name in select statment. With Regards Ashish... - Original Message - From: "hu js" <[EMAIL PROTECTED]> To: Sent: Thursday, September 06, 2007 12:41 PM Subject: [SQL] How to use serial variable to insert into muiti-recrods? run: "CREATE TABLE xxx ( id serial NOT NULL, name character varying ); insert into xxx select default values,place_name from air_bui;" fail: "ERROR: syntax error at or near "default" SQL state: 42601 Character: 24" How can I do? Bill Gates _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] foreign key problem
- Original Message - From: "ivan marchesini" <[EMAIL PROTECTED]> To: Sent: Monday, September 24, 2007 6:04 PM Subject: [SQL] foreign key problem Dear users, I'm trying to create constraints into a db they give me from access... I'm using pgadmin to create a new foreign key for the relationship between two table.. I have a table called "generalita" wich contains a column called "cod_carg": ___ select cod_carg from generalita group by cod_carg; cod_carg -- 2 0 1 (4 righe) ___ I have another table, called "diz_carg" that contain, into a column called "cod_carg" (that is a primary key), the domain values for the "cod_carg" column of "generalita": __ select cod_carg from diz_carg group by cod_carg; cod_carg -- 1 0 2 (3 righe) __ so when I try to create the foreign key I obtain: ALTER TABLE generalita ADD CONSTRAINT fkey_diz_cod_carg FOREIGN KEY (cod_carg) REFERENCES diz_carg (cod_carg) ON UPDATE NO ACTION ON DELETE NO ACTION; ERROR: insert or update on table "generalita" violates foreign key constraint "fkey_diz_cod_carg" DETAIL: Key (cod_carg)=( ) is not present in table "diz_carg". can I allow null values for "generalita.cod_carg" although it is a foreign key for "diz_carg.cod_carg"? I apologize if it is a stupid question.... :-( many thanks to all Yes you can , check that column cod_carg of table generalita is not set to NOT NULL. With regards Ashish ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Exclude fields from SELECT command
Pavel Stehule wrote: 2009/3/16 Charles Tam : Hi Everybody I’ve a table with 35 fields and would like to perform a SELECT command without specifying every single field. As such, I’ve use the SELECT * command. Is there an approach to exclude 5 fields from being returned? hello no, there are no way regards Pavel Stehule Thanks for your time. Kind Regards Charles Workaround is create a view on that table excluding those 5 fields and then select * from that view . With regards Ashish Karalkar -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
