Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

DB2 Sql

here is my data:

Vendor          Invoice          Comments
   206126      11011      N - ACH PAR NUMBER: 011470230
   206126      11011      C - T-MASTER BI-WEEKLY PAYMENT ON TICKET SALES
   206128      11011      N - ACH PAR NUMBER: 008413469
   206129      11011      N - ACH PAR NUMBER: 013301536

i want to write DB2 sql query to get results shown below:

   206126      11011      N - ACH PAR NUMBER: 011470230
            C - T-MASTER BI-WEEKLY PAYMENT ON TICKET SALES
   206128      11011      N - ACH PAR NUMBER: 008413469
   206129      11011      N - ACH PAR NUMBER: 013301536


basically when the Invoice and Vendor are same, i want to concatinate Comments and show on single row.


Thanks

0
mani_sai
Asked:
mani_sai
  • 7
  • 6
1 Solution
 
momi_sabagCommented:
which version of db2 are you using?

you can do it using recursive sql

with a as (
   select   vendor, invoice, comments, rown from (select vendor, invoice, comments, row_number() over(partition by vendor, invoice   order by comments) rown from your_table ) t
     where rown = 1
union all


   select   t1.vendor, t1.invoice, t1.comments || ' ' || t2.comments, t2.rown from (select vendor, invoice, comments, row_number() over(partition by vendor, invoice   order by comments) rown from your_table ) t1 join a t2 on t1.rown = t2.rown - 1
)

select vendor, invoice, comments
from  (select t1.*, count(*) over(partition by vendor, invoice) cnt from a t1)
where rown = cnt



or something like it
0
 
mani_saiAuthor Commented:
DB2 Version 8.0
0
 
momi_sabagCommented:
in that case
i think you will have to write a stored procedure for that
there was a different question someone asked this morning about the same thing
you can find the solution there
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mani_saiAuthor Commented:
is it doable with common table expression?
0
 
momi_sabagCommented:
nope
unless you have a maximum number of rows per vendor and invoice
in that case, we can try doing it with joins
0
 
mani_saiAuthor Commented:
Maximum would be 3.   (N, A ,C)

206126      11011      N - ACH PAR NUMBER: 011470230
206126      11011      C - T-MASTER BI-WEEKLY PAYMENT ON TICKET SALES
206126      11011      A - T-MASTER BI-WEEKLY




0
 
momi_sabagCommented:
so try

select t1.vendor, t1.invoice, t1.comments || ' ' || coalesce(t2.comments, '') || coalesce(t3.comments, '')
from your_table t1 left outer join your_table t2 on t1.vendor = t2.vendor and t1.invoice = t2.invoice and t2.comments like 'C%'
left outer  join your_table t3 on t1.vendor = t3.vendor and t1.invoice = t3.invoice and t3.comments like 'A%'
where t1.comments like 'N%'


this query assumes that N will always be present
if it is not true, you can replace the comparisons according to the one that will always be present
0
 
mani_saiAuthor Commented:
N or C or A will be present.

it is not N always..
0
 
momi_sabagCommented:
so try




select case when t1.vendor is null and t2.vendor is null then t3.vendor when t2.vendor is null then t1.vendor else t2.vendor end vendor,
case when t1.invoice is null and t2.invoice is null then t3.invoice when t2.invoice is null the t1.invoice else t2.invoice end invoice,
 
coalesce(t1.comments,'') || ' ' || coalesce(t2.comments, '') || coalesce(t3.comments, '')
from your_table t1 full outer join your_table t2 on t1.vendor = t2.vendor and t1.invoice = t2.invoice and t1.comments like 'N%' and t2.comments like 'C%'
left outer  join your_table t3 on t1.vendor = t3.vendor and t1.invoice = t3.invoice and t1.comments like 'N%' and t3.comments like 'A%'
0
 
mani_saiAuthor Commented:
Thanks.

i am seeing duplicates

   206126      11011      N - ACH PAR NUMBER: 011470230 C - T-MASTER BI-WEEKLY PAYMENT ON TICKET SALES
   206126      11011      C - T-MASTER BI-WEEKLY PAYMENT ON TICKET SALES


i just need the top row only....
0
 
momi_sabagCommented:
so try this

with a as (
select case when t1.vendor is null and t2.vendor is null then t3.vendor when t2.vendor is null then t1.vendor else t2.vendor end vendor,
case when t1.invoice is null and t2.invoice is null then t3.invoice when t2.invoice is null the t1.invoice else t2.invoice end invoice,
 
coalesce(t1.comments,'') || ' ' || coalesce(t2.comments, '') || coalesce(t3.comments, '')
from your_table t1 full outer join your_table t2 on t1.vendor = t2.vendor and t1.invoice = t2.invoice and t1.comments like 'N%' and t2.comments like 'C%'
left outer  join your_table t3 on t1.vendor = t3.vendor and t1.invoice = t3.invoice and t1.comments like 'N%' and t3.comments like 'A%'
),
b as (
 select *, row_number() over(partition by invoice, vendor order by len(comments) desc ) rown from a
)
select vendor, invoice, comments
from b
where rown = 1
0
 
mani_saiAuthor Commented:
Thanks for your solution.
it works, but the performance is get a big hit.

So i ended up coding the logic in .Net.  it works much much faster.


Thanks for your support.
0
 
mani_saiAuthor Commented:
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now