Re: Sql select question

short on storage? my first 'real' project had to be implemented in interpreted BASIC on a TRaSh-80 running XENIX (BASIC because the company president felt he understood it) -- to conserve space we were limited to 2-character variable names, and no (as in absolutely no) space between keywords. so, all comments had to be, well, shall we say, under my breath

"Mr E Guest" <piano.tuner_at_virgin.net> wrote in message news:a5592607.0311211343.46752d30_at_posting.google.com...
| mcs,
|
| It's a fair cop, guv - I'm always guilty of leaving comments out -
| totally agree with explicitly including account 6040 (here I blame the
| days when our sole server was very short on space!). The solution is
| more cunning than logical, I guess - desperate to make it as short as
| possible - doesn't help on the clarity front, but it makes me feel
| better. ;-)
|
| Shaun.
|
| "mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message
 news:<oIqdnRvxeLqYsCOiRVn-uw_at_comcast.com>...
| > clever answer -- did not look correct at first, since i was expecting a
| > decode for each account value
| >
| > looks like your decode is incrementing the sum for #1512, and
 decrementing
| > for 'all others' -- which means #6040; so if they don't balance, you get
| > your rows of interest
| >
| > a couple code maintenance observations
| > [_] i am so used to putting GROUP BY before HAVING that i have long
| > forgotten that HAVING can come first -- but for clarity, i would
 recommend
| > listing GROUP BY first
| > [_] to make the purpose of the DECODE more clear, you may want to
 include
| > 6040 explicitly, or include an inline comment on the
| > not-immediately-apparent logic
| >
| > i'm sure others will have other opinions
| >
| > -- mcs
| >
| > "Mr E Guest" <piano.tuner_at_virgin.net> wrote in message
| > news:a5592607.0311210057.25dada12_at_posting.google.com...
| > | This is abit shorter...
| > |
| > | select ex_inv_ref
| > | from agltransact
| > | where account in (1512, 6040)
| > | having sum(decode(account, 1512, 1, -1)) <> 0
| > | group by ex_inv_ref;
| > |
| > | Shaun.
| > |
| > | "VC" <boston103_at_hotmail.com> wrote in message
 news:<C5Tub.191428$275.658505_at_attbi_s53>...
| > | > Hello andy,
| > | >
| > | > Given:
| > | >
| > | > create table t1(ex_inv_ref int, account int);
| > | >
| > | > the simplest way to do what you want is:
| > | >
| > | > select a.ex_inv_ref, count_6040, count_1512 from
| > | > (select ex_inv_ref, count(*) count_6040 from t1 where account=6040
 group
| > | > by ex_inv_ref) a,
| > | > (select ex_inv_ref, count(*) count_1512 from t1 where account=1512
 group
| > | > by ex_inv_ref) b
| > | > where a.ex_inv_ref=b.ex_inv_ref and count_6040 != count_1512;
| > | >
| > | > Rgds.
| > | >
| > | >
| > | > "andy vandenberghe" <hp275_removethis_at_skynet.be_removethis> wrote in
 message
| > | > news:hp275_removethis-59987E.22531619112003_at_news.skynet.be...
| > | > > Hello everybody,
| > | > >
| > | > > i have the following table (agltransact), in which 2 fields are
 relevant:
| > | > >
| > | > > ex_inv_ref account
| > | > > 15 1512
| > | > > 15 6040
| > | > > 16 1512
| > | > > 16 1512
| > | > > 16 6040
| > | > > 16 6040
| > | > > 17 1512
| > | > > 17 1512
| > | > > 17 1512
| > | > > 17 6040
| > | > > 17 6040
| > | > > 18 1512
| > | > > 18 1512
| > | > > 18 6040
| > | > > 18 6040
| > | > > 18 6040
| > | > > 18 6040
| > | > >
| > | > > I would like to select the ext_inv_value for which there is not an
| > | > > *equal* number of accounts 1512 and 6040; so this is :
| > | > >
| > | > > ext_inv_ref
| > | > > -----
| > | > > 17
| > | > > 18
| > | > >
| > | > > I tried
| > | > >
| > | > > select ext_inv_ref,
| > | > > from agltransact
| > | > > where client='MG' and account in('1512','6040') and ext_inv_type

• 15
  | > | > > and ext_inv_type <= 17
  | > | > > group by ext_inv_ref
  | > | > > having round(count(account)/2,0) != count(account)/2;
  | > | > >
  | > | > > this select only those ext_inv_ref with an uneven number of
  accounts, so
  | > | > > ext_inv_ref
  | > | > > -----------
  | > | > > 17
  | > | > >
  | > | > > how would you write a query like that ?
  | > | > >
  | > | > > thanks,
  | > | > > Andy