Help Converting SQL Server Query To DB2

[jtrapat1]

I have a query which works against SQL Server and I need to run it against DB2 version 7 from a C program.
Here's the query from SQL Server:
------------------------------------------------
Insert into appropsdistinct
select distinct acode, right(pcode,3),catcode, fundcode, left(objectcode,1), sum(summaryvalue),0,0,0
from dob_bps
group by (acode + right(pcode,3) + catcode + fundcode + left(objectcode,1)), acode, right(pcode,3),catcode, fundcode, left(objectcode,1)
---------------------------------------
I think this version of DB2 will support the right and left string functions and the select from...into;

I just don't know how to write the group by so that db2 will understand.

(actually i was hoping someone could explain that group by..)

Thanks in Advance
John

 

[blom0344]

Hello John,

look at the following:

Insert into appropsdistinct
select distinct acode, right(pcode,3),catcode, fundcode, left(objectcode,1), sum(summaryvalue)
from dob_bps
group by
acode,right(pcode,3),catcode,fundcode,left(objectcode,1)

will probably cause error messages like SQL0134N

If the pcode consists of fixed length fields, you should get the job done with the substr function:

Insert into appropsdistinct
select distinct acode, substr(pcode,?,3),catcode, fundcode, substr(objectcode,1,1), sum(summaryvalue)
from dob_bps
group by
acode,substr(pcode,?,3),catcode,fundcode,substr(objectcode,1,1)

The trick with the group by is that when you specify 1 aggregate , you are expexted to specify in the group by over which dimensions you want to aggregate. If you aggregate over 5 dimensions (5 dimensions in output) you will HAVE to give the 5 objects in the 'group by'

You can escape this restriction by setting so-called Grouping Sets in the group by part:


Insert into appropsdistinct
select distinct acode, right(pcode,3),catcode, fundcode, left(objectcode,1), sum(summaryvalue)
from dob_bps
group by
grouping sets ((acode,catcode,fundcode))


which will give you a different effect on sum(summaryvalue) of course.

If the attempts with the left functions fail and pcode's are not of a fixed lenght, we'll have to built in a little check using case to get the substr working properly for that field.

Let me know, in that case....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[jtrapat1]

T. Blom,

Thanks a lot for all the help!

You got me so far in this project - I really appreciate it.
I'm about 95% of the way there-
You were absolutely right about the GROUP BY GROUPING SETS.
I'll study up on the syntax and use of this for my case.
-------------------------------------------
First of all, the latter case that you mentioned is not an issue - all of my fields are of a fixed length.

Could you explain what you meant by:
"which will give you a different effect on sum(summary_value) of course"

How will the aggregate group by affect my sum(summary_value)?

Also, I'm having trouble specifying the five fields that I want to GROUP BY:
I do have an additional field, budget_yr, that I need in my select statement but I don't need it in my group by clause.
In your response above, you chose three fields:
grouping sets ((acode,catcode,fundcode))

How can I get this GROUP BY to include all five fields?
I looked at some syntax and they seem to use combinations of comparisons between each field.
Here's what I tried - It worked but I'll have to compare it against the sql server data to see if it's correct.
---------------------------------------------------

db2 "insert into nysa.bp37t_dob_approp_distinct
select distinct budget_yr, agency_code, substr(prog_code,2,3), category, fund_type, substr(dob_object,1,1),sum(summary_value),0,0,0
from nysa.bp30t_dob_bps
group by grouping sets ((budget_yr,agency_code,category,fund_type)), agency_code, prog_code, category, fund_type, dob_object"

---------------------------------------------------
I'll do more research on GROUP BY GROUPING SETS in the meantime so reply when you can.


Again, thanks for all the help.
John

 

[blom0344]

Hello John,

First of all, the GROUP BY expression is probably the most standard part , evident in all SQL dialects.

For example:


A table:
*******************************
Sales_rep Customer Sales

4001 A 1000
4001 B 500
4001 B 700
4002 C 2000
4002 D 300

Select Salesrep,Customer,Sum(Sales)
From Table
Group By Salesrep,Customer
*******************************
4001 A 1000
4001 B 1200
4002 C 2000
4002 D 300


If you leave out either Salesrep or Customer, you will get an error. You have to repeat these dimensions !!!

Now use Grouping Sets:

Select Salesrep,Customer,Sum(Sales)
From Table
Group By
Grouping Sets ((Salesrep))

gives:

************************************************
4001 A 2200
4001 B 2200
4001 B 2200
4002 C 2300
4002 D 2300


So , in normal circumstances you want to aggregate over the dimensions you use in the output,which means repeating them in the GROUP BY.

I imagine the following should work:


db2 "insert into nysa.bp37t_dob_approp_distinct
select distinct budget_yr, agency_code, substr(prog_code,2,3), category, fund_type, substr(dob_object,1,1),sum(summary_value),0,0,0
from nysa.bp30t_dob_bps
group by budget_yr,agency_code,substr(prog_code,2,3),category,fund_type,substr(dob_object,1,1)"

PS.

If this fills in the remaining 5% , you owe me a star

T. Blom
Information analyst
tbl@shimano-eu.com

 

[jtrapat1]

T. Blom,

That was exactly what I was looking for!
It worked and it looks like I got the correct results.

I appreciate you taking the time to help.

Thanks Again.
John

 

[jtrapat1]

T. Blom,

Thanks for all the help.
I do however have a small problem with one of the tables I'm trying to select into.
If you can't help with this one, don't worry about it.
You've helped me out terrifically already.

Here's the problem:
In this sql statement:
-----------------------------------
db2 "INSERT INTO nysa.bp10t_appropriation
SELECT DISTINCT budget_yr, 100, agency_code, fund_type, prog_code, object_code, purpose_code, sum(adj_fund_amt), sum(dob_req_amt), sum(dob_fte), sum(cur_fte), 'A', 'DataLoad', '2002-10-15-15.16.38.596884' FROM nysa.bp37t_dob_approp_distinct
GROUP BY (budget_yr,agency_code,prog_code,purpose_code,fund_type,object_code)"
------------------------------------------
I also have a column, appr_id, which is part of the key, along with budget_yr, in the table bp10t_appropriation, that I'm trying to insert into.
The appr_id field is not an identity field or auto-increment field but since it's part of the key it has to be unique.
Is there a function or loop that I could put in the above script to handle such a case and dynamically insert a unique appr_id?
I was putting the above script and others like it into a file and executing it from the command line; until I ran into this problem.
Or, do I have to write a program to handle this case?

Like I said, if you don't know of a workaround, I'll go back to writing a program.

Thanks in advance.
John

 

[blom0344]

Hello John,

I am not much of a programmer (and a bit spoiled, cause I let my ETL tool generate unique sequence numbers , should i need any, but in DWH environment this never happens)

I have seen examples of loops in SQL that do what you want, but if you have no cursor available (and you have not , with straightforward SQL) it means for every sequence number generated it has to do a tour along records. For very small tables this will work, but if you are into thousands of records , it will take forever and a day to process the data.

I am sure that you are aware that it is the primary key that has to be unique, which means that the COMBINATION of fields that make up the key has to be unique.

If you think this a bit further, you will see that making the records unique by adding a sort of sequence generator will not be the right solution. You will generate unique combinations, but you have no control over them. How will you tell that the combination 73-A-X will correspond to the right record the NEXT time you run an update on your table.

Try to create a unique multiple-field key on the existing table if you can ....... T. Blom
Information analyst
tbl@shimano-eu.com