[SQL] selecting problems

[Shaun Watts]

What I am trying to do is select all the rows out of the categories
table (see below) and select the sum of j_amount out of the judgment
table.  Right now it is only returning the categories that are in the
judgment table.  I want it to return those fields with the amounts out
of the judgment table, but also the all the other categories from the
categories table with 0 as there amount.  Does anyone have a
suggestion.

select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON
(j_category=ca_code)
where j_case_no='45698'
and j_party_no=1
group by ca_code,ca_desc,ca_dis_cycle

Thanks,
Shaun
- 
Shaun,  
Phone 1-317-913-4160 Fax 1-317-913-4175 
CSI - Computer Systems, Inc. 
"Dictionary is the only place that success comes before work. Hard work is the price 
we 
must pay for success. I think you can accomplish anything if you're willing to pay the 
price."
Vince Lombardi 



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Blank-padding

[Shaun Watts]

I also have an issue with the blank padding at the end of my fields.
Is there any way to eliminate the blank padding at the end of character
fields in a table.

Such as you have field X as a char(6), but storing "abc" in it.  Well
postgres seems to add the padding on the end of string that is being
stored.
So it is stored as "abc   " instead of "abc".  I don't want that padding
there.

I am fairly new to Postgres and have only dealt with Informix database
systems,
which don't store data this way.

Any help is very much appreciated.

Thanks,
Shaun


Shaun Watts
Programmer/Analyst
 
CSI - Computer Systems, Inc. Phone:  317.913.4160
12975 Parkside Drive  Fax:  317.913.4175
Fishers, IN  46038   Toll Free:  800.860.1274
 
"To give anything less than your best is to sacrifice the gift."  --
Steve Prefontaine

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe
Sent: Monday, October 24, 2005 9:46 AM
To: Chris Travers
Cc: Tom Lane; Dean Gibson (DB Administrator); pgsql-sql
Subject: Re: [SQL] Blank-padding

On Sat, 2005-10-22 at 00:39, Chris Travers wrote:
> Tom Lane wrote:
> 
> >"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes:
> >  
> >
> >>I remember that discussion, and I was for the change.  However, upon

> >>doing some testing after reading the above, I wonder if the 
> >>blank-stripping isn't too aggressive.  I have a CHAR(6) field (say,
> >>named Z) that has "abc   " in it.  Suppose I want to append "x" to
Z, 
> >>with any leading spaces in Z PRESERVED.
> >>
> >>
> >
> >(You meant trailing spaces, I assume.)  Why exactly would you want to

> >do that?  You decided by your choice of datatype that the trailing 
> >spaces weren't significant.
> >
> I once built a telecom billing app where this might be important 
> (fixed length fields).  Lets say you have fixed length fields defined 
> as
> char(n) datatypes.  You may want to build a query to generate billing 
> records like:
> select field1 || field2 || field3 || field4 || field5 ... AS 
> bill_record FROM lec_billing_entries;
> 
> It seels to me that I would expect trailing spaces to be preserved in 
> these cases.  Having an implicit rtrim function is asking for
problems.
> Personally I would rather have to call rtrim explicitly than have the 
> backend treat the concatenation differently than if I do it on the
client.

If I rememberate correctificantly, this problem is or was caused by the
|| operator using the same internal logic for varchar / text AND char. 
Tom, is it reasonable / doable to have || have a different set of
internal functions for each of those types.

---(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





---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org