AS/400 >> AS400 SQL Group By Question

by Shannon Sumner » Wed, 30 Jul 2003 22:03:33 GMT

Hello all,



I could use your help. I'm new to SQL on the 400. I'm looking to select
one record from a table per unique value in a given field. In mysql I'd do
this with a group by statement, but on the 400, every field in the select
must be in the group by. This defeats the purpose of the query. Does
somebody know a work around? I'd like only to group by one field.



Thanks,



Shannon Sumner

XXXX@XXXXX.COM






AS/400 >> AS400 SQL Group By Question

by » Wed, 30 Jul 2003 23:52:22 GMT


You have to group by all the selected fields which are not summaries
The only thing which I cant beleive is that you have to specify all the
fields individually, SQL knows its going to demand them so why dosnt it just
accept 'group' as a keyword & just go and do it ?

Jonathan




do

AS/400 >> AS400 SQL Group By Question

by Shannon Sumner » Thu, 31 Jul 2003 03:01:21 GMT

Hello,

I'll be more specific with my problem ...

Here's a sample of the data ...

BFNAME BLNAME BADD3
BZIPA BSTATE BCTRY ACCT#
DELORIS MARTIN 1211 JENNY ROAD 21014 MD
US 40854523
DELORIS MARTIN 1211 JENNY ROAD 21014 MD
US 40854523
DELORIS MARTIN 1211 JENNY ROAD 21014 MD
US 40854523
DELORIS MARTIN 1211 JENNY ROAD 21014 MD
US 40854523
DELORIS MARTIN 1211 JENNY ROAD 21014 MD
US 40854523
DELORIS MARTIN 1211 JENNY ROAD 21014 MD
US 40854523
DELORIS MARTIN 1211 JENNY ROAD 21014 MD
US 40854523
DELORIS MARTIN 1211 JENNY RD 21014
MD US 40854523
D. MARTIN 1211 JENNY RD
21014 MD US 40854523
JAMES MARTIN 1211 JENNY RD 21014
MD US 40854523
CAROLYN D MARTIN 714-2B COUNTRY VILLAGE DR 21014 MD US
111254520
CAROLYN D MARTIN 714-2B COUNTRY VILLAGE DR 21014 MD US
111254520
CAROLYN D MARTIN 714-2B COUNTRY VILLAGE DR 21014 MD US
111254520

I'd like to extract only the first occurance of a customer by account
number.

If I use an order by in my select I get ...

BFNAME BLNAME BADD3
BZIPA BSTATE BCTRY ACCT#
DELORIS MARTIN 1211 JENNY ROAD 21014 MD
US 40854523
DELORIS MARTIN 1211 JENNY RD 21014
MD US 40854523
D. MARTIN 1211 JENNY RD
21014 MD US 40854523
JAMES MARTIN 1211 JENNY RD 21014
MD US 40854523
CAROLYN D MARTIN 714-2B COUNTRY VILLAGE DR 21014 MD US
111254520

I'd like to group the first four records into one ...

How would I do this?

Thanks,

Shannon Sumner
XXXX@XXXXX.COM


just


select
select

AS/400 >> AS400 SQL Group By Question

by Sam L. » Thu, 31 Jul 2003 10:14:51 GMT

ou've got denormalized data. Are you sure that each record with
the same ACCT# has identical name/address info?

If so, then try SELECT DISTINCT BFNAME, BLNAME, BADD3, BZIPA,
BSTATE, BCTRY, ACCT#.

Sam

"Shannon Sumner" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
21014 MD
21014 MD
21014 MD
21014 MD
21014 MD
21014 MD
21014 MD
21014
21014
US
US
US
account
21014 MD
21014
21014
US
news:3f27e9b6$0$25430$ XXXX@XXXXX.COM ...
summaries
specify all the
why dosnt it
looking to
In mysql I'd
in the
query. Does
field.


Similar Threads

1. question updating from sql server to as400 - AS/400

2. Resumes & Recruiters - AS400 Migration Group

3. Add your Resume to AS400 Migration Group - AS/400

4. AS400 Empty cell question

Hello again!

Have a field coming in from an AS400 that's "empty" (if i highlight it
in kinda looks like there might be a space there, but no letter,etc.).
When it copies over to a varchar field in my staging table it doesn't
come over as null, but it apparently isn't coming over as a space
either or anything of the '\s' variety.  Below are the two ways I've
tried to use to convert what is incoming (which is either a letter to
convert to 'Yes' or whatever the empty thing is to convert to '').

1)
// Convert the CustomFieldString2 to 'Yes'/EMPTY.
if (isString(DTSSource("CustomFieldString2")))
{
	custString2 = "Yes";
}
else
{
	custString2 = "";
}
function isString(sStr)
{
	try
	{
		return sStr.constructor==String;
	}
	catch(ex)
	{
		return false;
	}
}
***now this one makes everything, whether it contains a letter or not
empty ('').


2)
if (DTSSource("CustomFieldString2") == "" |
DTSSource("CustomFieldString2") == null)
{
	custString2 = "";
}
else
{
	custString2 = "Yes";
}

***and this one makes everything 'Yes'.

plz help! i've tried everything I can think of, and yes I've also
previously tried adding *.replace(/\s/g,"") in various places to see if
that fixes it and no go =(.

5. Embedded SQL, sub-procedures and activation groups - AS/400

6. Grouping SQL statements

hi all,
i want to group few sql statements into one and run them in DataStage.
However, i am getting the following error:

BEGIN
DELETE FROM DEVSTGEV.STG_EV_RSKV_IO;
COMMIT;
INSERT INTO DEVSTGEV.STG_EV_RSKV_IO(L45__HEADER, COMPANY_CODE, POLICY_BR,
POLICY_NO, POLICY_TYP, RISK_NO, SEQ_NO__IO, TERM_ID, TRAN_DATE, TRAN_TIME,
TRAN_USER, VALID_FLAG, RISK_CLASS, DATE_EFFECT, DATE_TERM, PAGE_NUMBER,
HEAD_NO, GEN_NO, RATING_FLAG, CATEGORY, COVER_01, COVER_02, COVER_03,
COVER_04, COVER_05, DESC_01, DESC_02, DESC_03, DESC_04, DESC_05,
SUM_INS_01, SUM_INS_02, SUM_INS_03, SUM_INS_04, SUM_INS_05, PREMIUM_01,
PREMIUM_02, PREMIUM_03, PREMIUM_04, PREMIUM_05, RRN__IO) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
END;



SQLSTATE=37000, DBMS.CODE=-104
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol
driver][UDB DB2 for Windows, UNIX, and Linux]ILLEGAL SYMBOL FROM; VALID
SYMBOLS ARE BEGIN DELETE


Any suggestions!

Cheers,
San.

7. [SQL] Problem with group by and max

8. Help for a SQL Query statement: group by

I have a table (ORDERS) with this kind structure (this is not the
really table):

OrderId   Product   Amount  Agent1  Agent2  Agent3
________  _______   ______  ______  ______  ______
000001    P1           20   AA      BB      XX
000002    P2            7   BB      CC
000003    P2           12   CC


If i want summarize Amount by Product it's enought to write

Select Product, sum(Amount) from ORDERS group by Product

and so my results will be:

Product  Amount
_______  ______
P1           20
P2           19


Now i want to summarizy by Agent Code, but Agent is "splitted" in 3
columns (Agent1, Agent2, Agent3)

It's possible using only a select statement obtain it?

I want see:

Agent    Amount
_____    ______
AA          20
BB          27
CC          19
XX          20


I think it's not possible?

I'm using db2 8.2 and db2/400 (v5r3)

Best Regards
Roberto