I seem to always be forgetting how to include a subquery select as part of my group by statement when I’m doing some sql that requires this.
Example of what I mean:
select tbraccd_pidm, sum(tbraccd_balance), tbraccd_term_code
from tbraccd
where tbraccd_term_code in (‘201001’,‘200909’)
group by tbraccd_pidm, tbraccd_term_code
What I want to do is a subquery select from a phone number, like this, but don’t know how to include that in my group by clause. This would be the subquery select I’m using:
(SELECT (SPRTELE_PHONE_AREA || ‘-’ || SPRTELE_PHONE_NUMBER)
FROM sprtele
WHERE sprtele_pidm = spriden_pidm
AND SPRTELE_STATUS_IND IS NULL
AND ROWNUM
In my LIMITED experience I’ve found that you cannot use a subquery in a
group by clause. But don’t give up!
A while back someone on this list told us about the “with clause”. A
Quest employee I believe. Sorry I don’t recall the name to give proper
credit. L As search in the archives on ‘with with’ might find it.
Anyway, try something like this:
with white_pages as
(SELECT
sprtele_pidm ,
( SPRTELE_PHONE_AREA || ‘-’ || SPRTELE_PHONE_NUMBER ) as Phone
FROM
sprtele
WHERE
SPRTELE_STATUS_IND IS NULL
– AND ROWNUM
)
select
tbraccd_pidm ,
sum( tbraccd_balance ),
tbraccd_term_code ,
Phone
from
tbraccd ,
white_pages
where
– tbraccd_term_code in (‘201001’,‘200909’) and
tbraccd_pidm = white_pages . sprtele_pidm
group by
tbraccd_pidm ,
tbraccd_term_code ,
Phone
I had to comment a couple of lines to pull data from our DB, but you get the
picture. You may want to select more columns from sprtele, so you can tell what
kind of phones you’re looking at. If you’re wanting names, too,
don’t forget about f_format_name.
HTH,
Dan
Daniel B Madvig
Computer Technologies
Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu
If you can break it out of being a subquery, and put it in the main
Select, it’s easy:
select tbraccd_pidm
, tbraccd_term_code
, sprtele_grouped.phone
, sum(tbraccd_balance)
from tbraccd
, (SELECT MAX(SPRTELE_PHONE_AREA || ‘-’ || SPRTELE_PHONE_NUMBER)
Phone
– use MAX to pick a random one if there is more than one
– for a given sprtele_pidm
, sprtele_pidm
FROM sprtele
WHERE SPRTELE_STATUS_IND IS NULL
GROUP BY sprtele_pidm) sprtele_grouped
where tbraccd_term_code in (‘201001’,‘200909’)
and sprtele_grouped.sprtele_pidm (+) = tbraccd.spriden_pidm
group by tbraccd_pidm, tbraccd_term_code, sprtele_grouped.phone
Nate Schroeder
IT Commercial Technical Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167
314-694-2592
Is there a way to convert the erx file to a more universally utilized format? I
want to be able to distribute the database model but am afraid some of the
people I am sending to might not have a modeling program specifically.
Who would ever want to skin a cat anyway?
Me! But only dead ones.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
you will want to consider subquery cardinality before incorporating your
subqueries into the main query specifically:
if the resultset from your subquery cardinality is low then leave it be
if the resultset from your subquery cardinality is high then incorporate
subquery into main body
GROUP BY should be implemented sparingly and only as a precursor for accumulator
functions such as AVG and SUM
roger/norm/anyone?
Martin
Verzicht und Vertraulichkeitanmerkung
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
GROUP BY should be implemented sparingly and only as a
precursor for accumulator functions such as AVG and SUM
roger/norm/anyone?
It's not a recommendation I've ever seen. Aggregate functions are fine -
if you need the answers that they give. There is no need to avoid them
(like GOTO in programming) and reinvent the wheel (much beloved of Java
developers ). I'm pretty sure that Oracle's implementation of
these aggregate functions is a lot more efficient that whatever we could
write ourselves to implement them.
GROUP BY is fine.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
use MAX to pick a random one if there is more than one
Err… max doesn’t provide randomness… not even a bad facsimile
of it. For example, if the phone numbers selected for a given pid are:
999-5551234
999-5559876
999-5554321
You will always, always, always get 999-5559876 using the max function…
unless of course someone changes the dataset. Random implies there’s some
chance that one of the other two numbers will be picked and that’s simply
not true with the max function.
It is possible that when the person said RANDOM he did not mean the mathematical
word random but maybe something in the line of “any old number” and
if all you want is some number then both MAX and MIN will give you “some
number”. Of course Max always gives the biggest and Min always the
smallest.
Apart from Unicode support, the ER Diagram upgrade seems to be one of the more
popular new features for Toad 10. It’s brand new, so don’t feel too bad
for not knowing about it.
