Re: [SQL] curly braces to group outer joins in queries from OpenOffice.org?
Hi
AFAK jdbc 3.0 requires driver to support curly braces for escape sequences.
For example:
{d yyy-mm-dd}
{t hh:mm:ss}
{ts -mm-dd hh:mm:ss[.f...]}
{oj outer-join}
{fn scalar-function}
Chapter 6.2 states that drivers must support escape syntax.
Chapter 13.4 defines escape syntax.
http://java.sun.com/products/jdbc/download.html
Regards,
Tõnu Põld
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to know column constraints via system catalog tables
>
> The only issue I find with your SQL is it relies on "(a.attnum = x.conkey[1] or
> a.attnum = x.conkey[2])" which assumes
there is two columns forming the primary key of a table. Perhaps, I should explain
what I'm trying to achieve with this
SQL.
>
Not exactly. Look at this
CREATE TABLE films (
codeCHAR(5),
title VARCHAR(40),
did DECIMAL(3),
date_prod DATE,
kindVARCHAR(10),
len INTERVAL HOUR TO MINUTE,
CONSTRAINT code_title PRIMARY KEY(code,title)
);
CREATE TABLE films_02 (
codeCHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
title CHARACTER VARYING(40) NOT NULL,
did DECIMAL(3) NOT NULL,
date_prod DATE,
kindCHAR(10),
len INTERVAL HOUR TO MINUTE
);
SELECT
upper(u.usename) AS TBL_OWNER, upper(c.relname) AS TBL_NAME,
upper(a.attname) AS COL_NAME, a.atttypid AS COL_TYPE,
int4larger(a.attlen, a.atttypmod - 4) AS COL_LENGTH,
CASE WHEN a.attnotnull=true THEN 0 ELSE 1 END AS COL_NULL, a.attnum AS COL_SEQ,
CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d
WHERE d.adrelid = a.attrelid and d.adnum = a.attnum) THEN
1
ELSE
0
END AS COL_DEFAULT
from pg_attribute a, pg_constraint x,
pg_class c left join pg_user u on (u.usesysid = c.relowner)
where c.oid = a.attrelid and not (c.relname ~* 'pg_') and
c.relkind = 'r' and a.attnum > 0 and
c.relfilenode=x.conrelid and x.contype='p' and c.relname like 'film%' and
(a.attnum = x.conkey[1] or a.attnum = x.conkey[2]) ;
tbl_owner | tbl_name | col_name | col_type | col_length | col_null | col_seq |
col_default
---+--+--+--++--+-+-
CH| FILMS| CODE | 1042 | 5 |0 | 1 |
0
CH| FILMS| TITLE| 1043 | 40 |0 | 2 |
0
CH| FILMS_02 | CODE | 1042 | 5 |0 | 1 |
0
(3 rows)
My supposition was it's no sign of good design to form primary keys from more
than two columns.
As you can see primary keys on a single column will be listed as well.
So, if you want to deal with wider keys,
you can add "or a.attnum = x.conkey[3]" etc.
> I'm writing an abstraction layer (abit like persistent but less ambitious). I hope
> to make this C++ layer generic for
any RDBMS as long as the RDMS allow discovery of a table's columns name and the
columns' data type. The RDBMS should als
o expose the field(s) used to form the primary key of a table. This will free the
programmer from coding the class data
member to correspond to the underlying table's fields (automate those tedious tasks of
mapping OO classes to database ta
bles).
Good Idea. But I've found it's always a PITA to question system tables,
no matter which RDBMS you're on.
> I'm using libpqxx for postgreSQL, I had thought of a hack which is to strip the
> {1,2..} string returned by the conkey
of pg_constraint to get the column numbers. It's not pretty as I have to execute at
least two queries but it should be w
orkable.
Anyway.
Regards, Christoph
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Conversion Problem
Apologies as this probably isn't really for this list but... In postgresql you can execute a statement such as: SELECT 1 > 2; And it would return 'f' Does anyone know if you can do this in SQL Server as I have to do a conversion of some prewritten SQL code. MTIA, Graham. ---(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] Looks are important
Hi Tom, Switching to a fixed-width font did the trick. Thanks for the help. George - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "George Weaver" <[EMAIL PROTECTED]> Cc: "Josh Berkus" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Louise Cofield" <[EMAIL PROTECTED]> Sent: Wednesday, November 12, 2003 9:31 PM Subject: Re: [SQL] Looks are important > "George Weaver" <[EMAIL PROTECTED]> writes: > > ... the ' 's are not quite the same width as= > > an ordinary number or letter. Thus the physical display length of "30 cha= > > racters" (padded) can vary from row to row. The result is that the kinds do= > > n't necessary line up neatly. I need to concatenate the two as they are be= > > ing displayed as one column in a drop down combobox. > > Use a fixed-width font. > > > Is what I'm trying to do possible??? > > Not with a variable-width font that you haven't even told us the exact > character widths of ... > > 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]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Conversion Problem
> > Apologies as this probably isn't really for this list but... Right. > > In postgresql you can execute a statement such as: > > SELECT 1 > 2; > > And it would return 'f' > > Does anyone know if you can do this in SQL Server as I have to do a > conversion of some prewritten SQL code. No, I don't know. Can't you simply try? > > MTIA, > > Graham. > > Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Conversion Problem
On Thursday 13 November 2003 12:25, Graham wrote: > Apologies as this probably isn't really for this list but... > > In postgresql you can execute a statement such as: > > SELECT 1 > 2; > > And it would return 'f' > > Does anyone know if you can do this in SQL Server as I have to do a > conversion of some prewritten SQL code. If you look here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_3qpf.asp "Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set." Generally you'd use a BIT type instead of a boolean, but that's not going to help you here. I'm not even sure if you can use a boolean expression in the column-list part of a select. The only thing I can think of is to use a case: SELECT WHEN 1>2 THEN 1 ELSE 0 END; As to why MSSQL doesn't support booleans, you could try asking their tech support, but I wouldn't get your hopes up. -- Richard Huxton Archonet Ltd ---(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] Looks are important
Title: Message Using a fixed-width font as Tom suggests, concatenate an additional space between the no field and the kind field: SELECT RPAD(no,30,' ') || ' ' || TRIM(tableb.kind) FROM tablea WHERE tablea.kind = tableb.kind Louise -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of George WeaverSent: Wednesday, November 12, 2003 8:15 PMTo: Josh Berkus; [EMAIL PROTECTED]; Louise CofieldSubject: Re: [SQL] Looks are important Hi Louise, Josh, Thanks for the suggestions. What I'm trying to accomplish is to have a space between no and kind. Length(no) can vary. I would like all the kinds to line up evenly when displayed, with a space between no and kind. But when I RPAD no (to try and get an even starting point for kind), the ' 's are not quite the same width as an ordinary number or letter. Thus the physical display length of "30 characters" (padded) can vary from row to row. The result is that the kinds don't necessary line up neatly. I need to concatenate the two as they are being displayed as one column in a drop down combobox. Is what I'm trying to do possible??? George - Original Message - From: Louise Cofield To: 'George Weaver' ; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:19 PM Subject: RE: [SQL] Looks are important Try the TRIM function or the LTRIM function: SELECT RPAD(no,30,' ') || TRIM(tableb.kind) FROM tablea WHERE tablea.kind = tableb.kind Louise -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of George WeaverSent: Wednesday, November 12, 2003 3:12 PMTo: [EMAIL PROTECTED]Subject: [SQL] Looks are important Hi Everyone, I am trying to concatenate two fields through a query: SELECT RPAD(no,30,' ') || tableb.kind FROM tablea WHERE tablea.kind = tableb.kind The result gives (for example): 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue What I would like to have is better alignment: 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad? Thanks in advance, George
[SQL] STRICT function returning a composite type
I noted that such a function returns an empty rowset if a NULL value is
passed as an argument. Is it a bug or feature? I wish it was a feature,
because I probably want to use this behavour.
Here's an example:
CREATE TYPE ts_bounds AS (
sdate timestamptz,
edate timestamptz
);
CREATE OR REPLACE FUNCTION overlap_bounds(timestamptz, timestamptz, timestamptz,
timestamptz)
RETURNS ts_bounds AS '
DECLARE
sdate1 ALIAS FOR $1;
edate1 ALIAS FOR $2;
sdate2 ALIAS FOR $3;
edate2 ALIAS FOR $4;
res ts_bounds%rowtype;
BEGIN
res.sdate := CASE WHEN sdate1 > sdate2 THEN sdate1 ELSE sdate2 END;
res.edate := CASE WHEN edate1 < edate2 THEN edate1 ELSE edate2 END;
IF res.sdate > res.edate THEN
res.sdate := NULL;
res.edate := NULL;
END IF;
RETURN res;
END' LANGUAGE 'plPgSQL' STRICT;
fduch=# SELECT * from overlap_bounds('-infinity', 'today', 'yesterday', 'infinity');
sdate | edate
+
2003-11-12 00:00:00+03 | 2003-11-13 00:00:00+03
(1 row)
fduch=# SELECT * from overlap_bounds('-infinity', 'today', 'yesterday', null);
sdate | edate
---+---
(0 rows)
What I want is to get no rows if given intervals don't overlap instead of:
fduch=# SELECT * from overlap_bounds('-infinity', 'yesterday', 'today', 'infinity');
sdate | edate
---+---
|
(1 row)
Is it possible without returning SETOF ts_bounds?
fduch=# SELECT version();
version
-
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
--
Fduch M. Pravking
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] STRICT function returning a composite type
On Thursday 13 November 2003 16:08, Alexander M. Pravking wrote: > I noted that such a function returns an empty rowset if a NULL value is > passed as an argument. Is it a bug or feature? I wish it was a feature, > because I probably want to use this behavour. >From the SQL commands section of the manual: RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns NULL whenever any of its arguments are NULL. If this parameter is specified, the function is not executed when there are NULL arguments; instead a NULL result is assumed automatically. -- Richard Huxton Archonet Ltd ---(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] STRICT function returning a composite type
On Thu, Nov 13, 2003 at 05:14:27PM +, Richard Huxton wrote: > RETURNS NULL ON NULL INPUT or STRICT indicates that the function always > returns NULL whenever any of its arguments are NULL. If this parameter is > specified, the function is not executed when there are NULL arguments; > instead a NULL result is assumed automatically. Does "NULL result" mean an empty rowset if the function returns a record? -- Fduch M. Pravking ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] STRICT function returning a composite type
"Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > On Thu, Nov 13, 2003 at 05:14:27PM +, Richard Huxton wrote: >> RETURNS NULL ON NULL INPUT or STRICT indicates that the function always >> returns NULL whenever any of its arguments are NULL. > Does "NULL result" mean an empty rowset if the function returns a record? No, it means a null record. "Empty rowset" would apply to a function declared to return SETOF something. (I believe that is how we interpret the concept of strictness for functions returning sets.) 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])
Re: [SQL] STRICT function returning a composite type
On Thu, Nov 13, 2003 at 12:27:58PM -0500, Tom Lane wrote: > "Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > > Does "NULL result" mean an empty rowset if the function returns a record? > > No, it means a null record. "Empty rowset" would apply to a function > declared to return SETOF something. (I believe that is how we > interpret the concept of strictness for functions returning sets.) Very well then... Can I return a null record from such function explicitly? Sorry, I could't find it anywhere in docs or examples. -- Fduch M. Pravking ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] STRICT function returning a composite type
"Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > Very well then... Can I return a null record from such function > explicitly? Sorry, I could't find it anywhere in docs or examples. Not sure. Seems like you should be able to, but I've never tried it. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] STRICT function returning a composite type
On Thu, Nov 13, 2003 at 12:35:41PM -0500, Tom Lane wrote: > "Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > > Very well then... Can I return a null record from such function > > explicitly? Sorry, I could't find it anywhere in docs or examples. > > Not sure. Seems like you should be able to, but I've never tried it. Thanks for a quick response, guys. I'll try to find it myself and will let you know if I did ;) -- Fduch M. Pravking ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Need Help
Hi! If I've a table like this kk kj pngk vote 01 02 a 12 01 02 b 10 01 03 c 5 and I want to have a query so that it give me a result as below. The condition is for each record with the same kk and kj but difference pngk will be give a mark *; [In this example for record 1 and record 2 we have same kk=01 and kj=02 but difference pngk a and b so we give * for the mark] kk kj pngk vote mark 01 02 a12 * 01 02 b10 * 01 03 c 5 How should I write the query? Thanks in advanced. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Need Help
On Fri, Nov 14, 2003 at 09:04:47 +0800, Abdul Wahab Dahalan <[EMAIL PROTECTED]> wrote: > Hi! > > If I've a table like this > > kk kj pngk vote > 01 02 a 12 > 01 02 b 10 > 01 03 c 5 > > and I want to have a query so that it give me a result as below. > > The condition is for each record with the same kk and kj > but difference pngk will be give a mark *; > [In this example for record 1 and record 2 we have same kk=01 and kj=02 > but difference pngk a and b so we give * for the mark] > > > kk kj pngk vote mark > 01 02 a12 * > 01 02 b10 * > 01 03 c 5 > > How should I write the query? You could do something like: select a.kk, a.kj, a.pngk, a.vote, b.star from table a left join (select '*' star, kk, kj from table group by kk, kj having count(*) > 1) b on (a.kk = b.kk and a.kj = b.kj); I didn't test this so there might be a syntax problem, but it should make it clear how to do what you want. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
