• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

VB.Net - loop question

I am using VB.Net/ASP.Net:

My SQL is returning data like shown below:

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
   206129      11011      A - TEST PAR NUMBER: 0133043433


i want to show data on the grid like this 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
                                           A - TEST PAR NUMBER: 0133043433


Basically i want concatinate Comments data if Invoice/Vendor are the same...


please help me with a sample code -  -using VB.Net. / Dataset/ looping ....


0
mani_sai
Asked:
mani_sai
  • 13
  • 11
1 Solution
 
slightwv (䄆 Netminder) Commented:
I believe you will have to keep track of the previous values and blank out if the current row is the same.

Look at the rowdatabound method.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowdatabound.aspx

That is a lot of work.  What database are you using?  It might be A LOT easier to do this at the SQL level.  I have some Oracle SQL that does this that should port to most databases.
0
 
mani_saiAuthor Commented:
DB2 SQL - Verson 8.0



0
 
slightwv (䄆 Netminder) Commented:
Here is an Oracle test and SQL.

Again, most larger database support row_number syntax.


drop table tab1 purge;
create table tab1(Vendor varchar2(6), Invoice varchar2(5), Comments varchar2(50));

insert into tab1 values('206126','11011','N - ACH PAR NUMBER: 011470230');
insert into tab1 values('206126','11011','C - T-MASTER BI-WEEKLY PAYMENT ON TICKET SALES');
insert into tab1 values('206128','11011','N - ACH PAR NUMBER: 008413469');
insert into tab1 values('206129','11011','N - ACH PAR NUMBER: 013301536');
insert into tab1 values('206129','11011','A - TEST PAR NUMBER: 0133043433');
commit;


select case when myrn=1 then vendor end vendor,
	case when myrn=1 then invoice end invoice,
	comments
from (
	select vendor, invoice, comments, row_number() over(partition by vendor, invoice order by vendor, invoice) myrn
	from tab1
)
/

Open in new window

Results:
------------------

VENDOR INVOI COMMENTS
------ ----- --------------------------------------------------
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
             A - TEST PAR NUMBER: 0133043433

Open in new window

0
Industry Leaders: 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!

 
slightwv (䄆 Netminder) Commented:
>>DB2 SQL - Verson 8.0

Caught me posting the test.

Does DB2 support ROW_NUMBER?
0
 
slightwv (䄆 Netminder) Commented:
0
 
mani_saiAuthor Commented:
can you give me example using DB2 version 8.0?

Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>can you give me example using DB2 version 8.0?

Sorry but no.  I only have Access to Oracle and well, Access.

From what I looked at DB2 has a CASE statement and ROW_NUMBER.

Can you try my SQL as-is?

I might be able to help with syntax issues with the help on the online docs.
0
 
mani_saiAuthor Commented:
WITH    
T0 (VENDOR, INVOICE, Comments)
AS   (SELECT API.VENDOR, API.INVOICE,
SUBSTRING(OBJECT,6,1) || ' - ' || Trim(SUBSTRING(OBJECT, LOCATE(',',OBJECT,LOCATE(',',OBJECT, LOCATE(',',OBJECT)+1)+1) +1, 9999)) AS Comments
FROM LAWQA.DBAPAPI API
LEFT JOIN LAWQA.L_HAPI L ON L.L_INDEX = API.L_INDEX
Where API.INVOICE = '11011' AND API.COMPANY = 5
ORDER BY API.VENDOR, API.INVOICE
FETCH FIRST 1000 rows only )

Select Case When myrn=1 Then Vendor End Vendor,      
       Case When myrn=1 Then Invoice End Invoice,
      Comments
From (Select vendor, invoice, comments, row_number() over(partition by vendor, invoice
Order by vendor, invoice) myrn
      From T0
)


i am getting this error:
 SQL0104N  An unexpected token "<END-OF-STATEMENT>" was found following "".  Expected tokens may include:  "AS CL IN LOG OUT DATA <IDENTIFIER>".  SQLSTATE=42601


0
 
mani_saiAuthor Commented:
T0 table will have datal like this:

   186589      11011      
   188897      11011      
   189011      11011      C - 2011 MERCHANTS ASSOCIATION DUES
   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
   206130      11011      N - ACH PAR NUMBER: 011470232
   206131      11011      N - ACH PAR NUMBER: 011470234
0
 
slightwv (䄆 Netminder) Commented:
Looks like row_number might not be available until DB2 9 based on the docs at ibm.com.

I'll see if there is a similar work-around like in Oracle.

If you happen to know please help out.  Oracle has a dynamic column called rownum that is attached to a result set.

Do you know id DB2 has anything similar?
0
 
mani_saiAuthor Commented:
Row number work in DB2 8.0:


I tested this query below , and it works:

WITH    
T0 (VENDOR, INVOICE, Comments)
AS   (SELECT API.VENDOR, API.INVOICE,
SUBSTRING(OBJECT,6,1) || ' - ' || Trim(SUBSTRING(OBJECT, LOCATE(',',OBJECT,LOCATE(',',OBJECT, LOCATE(',',OBJECT)+1)+1) +1, 9999)) AS Comments
FROM LAWQA.DBAPAPI API
LEFT JOIN LAWQA.L_HAPI L ON L.L_INDEX = API.L_INDEX
Where API.INVOICE = '11011' AND API.COMPANY = 5
ORDER BY API.VENDOR, API.INVOICE
FETCH FIRST 1000 rows only )

Select Vendor, Invoice, Comments, ROW_NUMBER() OVER (PARTITION BY Vendor, invoice
Order by Vendor, Invoice) MYRN From T0


i get this output:

   206789      11011      C - RECOVERY FEES - CLAIM # 11 COMMONWEALTH COURT      1
    36773      11011            1
   180434      11011            1
   206130      11011      N - ACH PAR NUMBER: 011470232      1
   116298      11011      C - 2011 TOWN & COUNTRY TAXES      1
   206126      11011      N - ACH PAR NUMBER: 011470230      1
   206126      11011      C - T-MASTER BI-WEEKLY PAYMENT ON TICKET SALES      2
    25833      11011            1
   188897      11011            1


i just need the outer select case to get final output, which is throwing the error...
0
 
slightwv (䄆 Netminder) Commented:
>>Row number work in DB2 8.0:

Way cool!  Saves me a lot of research.

>>just need the outer select case to get final output

Can you not do nested SQL in DB2 or is it the actual case statements?


See if this runs:

...
FETCH FIRST 1000 rows only )

Select Vendor, Invoice, Comments, case vendor='206789' then 'Hello' end myTest,  ROW_NUMBER() OVER (PARTITION BY Vendor, invoice
Order by Vendor, Invoice) MYRN From T0
0
 
mani_saiAuthor Commented:
CASE statement works:

WITH    
T0 (VENDOR, INVOICE, Comments)
AS   (SELECT API.VENDOR, API.INVOICE,
SUBSTRING(OBJECT,6,1) || ' - ' || Trim(SUBSTRING(OBJECT, LOCATE(',',OBJECT,LOCATE(',',OBJECT, LOCATE(',',OBJECT)+1)+1) +1, 9999)) AS Comments
FROM LAWQA.DBAPAPI API
LEFT JOIN LAWQA.L_HAPI L ON L.L_INDEX = API.L_INDEX
Where API.INVOICE = '11011' AND API.COMPANY = 5
ORDER BY API.VENDOR, API.INVOICE
FETCH FIRST 1000 rows only )

Select Vendor, Invoice, Comments,
CASE When Trim(Vendor)='206789' then 'Hello'
end myTest,  
ROW_NUMBER() OVER (PARTITION BY Vendor, Invoice
Order by Vendor, Invoice) MYRN From T0

Result:
   206789      11011      C - RECOVERY FEES - CLAIM # 11 COMMONWEALTH COURT      Hello      1

0
 
mani_saiAuthor Commented:
i just need to concatinate Comments when Invoice and Vendor are same...

we have row number 1 and 2 , but how to concatinate..

Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>but how to concatinate..

That is where my outer query worked.

Look at my SQL.

For example:
select case when myrn=1 then vendor end vendor,


for the first vendor from the inner row_number() call, "myrn=1" it returns the vendor name.  If the rownum is not 1, it returns null.

I'm not sure how to do this tihout the outer select.  That is where the 'magic' happens.


Just need to see if DB2 can do nested SQL.  It almost has to.
0
 
mani_saiAuthor Commented:
yes, we can use subquery in DB2
0
 
slightwv (䄆 Netminder) Commented:
hmmm....

If the pieces work separately then why don't they work together?

I've re-looked at the sample throwing the error and cannot see it.

I'll continue looking.

I hate to give up on this approach and go back to the rowdatabound approach.  Doing it in SQL will be SO much faster.
0
 
mani_saiAuthor Commented:
i  got it working

here is the sql:
      WITH    
T0 (VENDOR, INVOICE, Comments, MYRN)
AS   (SELECT API.VENDOR, API.INVOICE,
SUBSTRING(OBJECT,6,1) || ' - ' || Trim(SUBSTRING(OBJECT, LOCATE(',',OBJECT,LOCATE(',',OBJECT, LOCATE(',',OBJECT)+1)+1) +1, 9999)) AS Comments,
ROW_NUMBER() OVER (PARTITION BY Vendor, Invoice
Order by Vendor, Invoice) MYRN
FROM LAWQA.DBAPAPI API
LEFT JOIN LAWQA.L_HAPI L ON L.L_INDEX = API.L_INDEX
Where API.INVOICE = '11011' AND API.COMPANY = 5
ORDER BY API.VENDOR, API.INVOICE
FETCH FIRST 1000 rows only )

Select
            Case When MYRN='1' Then Vendor
            ELSE Null
            End Vendor,      
             Case When MYRN='1' Then Invoice
             ELSE Null
             End Invoice,
            Comments From T0


output:

   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
   206130      11011      N - ACH PAR NUMBER: 011470232
   206131      11011      N - ACH PAR NUMBER: 011470234


it is creating seperate row on the table

can we make it on one row, i mean continate data into one row...
0
 
slightwv (䄆 Netminder) Commented:
>>can we make it on one row, i mean continate data into one row...

Missed that in the original requirements.

Do you want all the Comments in a single cell in the grid separated with a <br/> or similar character?

Again, going to post Oracle code but it looks like DB2 again has the many of same functions (not sure about versions though).

I'll post what I have tested in Oracle so you can get an idea.  Then we go through the process of porting to DB2.

Using the same tables and data above:


SELECT vendor,
       invoice,
           EXTRACT(XMLAGG(XMLELEMENT("s", tab1.comments || '<br/>')), '/s/text()').getstringval()
           column3
FROM tab1
GROUP BY vendor, invoice
/

Open in new window

0
 
mani_saiAuthor Commented:
i found out the solution with .net code.
thanks again for all your help.

0
 
slightwv (䄆 Netminder) Commented:
I just reached out to the DB2 Expert on the site.  Hopefully he will be able to help!
0
 
mani_saiAuthor Commented:
thanks
0
 
slightwv (䄆 Netminder) Commented:
>>i found out the solution with .net code.

Glad you found a solution.  Mind if I ask what it was?

I still think this can be done at the database layer a lot more efficiently.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi guys,

This can certainly be done at the database layer.  DB2 is like that.  :)

A couple of quick questions.

-- How many rows can be returned with the same invoice number?  (If only 2, a simple self join may be the best solution.  If many, recursive SQL or a stored procedure is in order.)

-- Is there a limit to the size of the concatenated comment string?  If there are 100 rows with the same invoice number and 100 characters per comment, a 10K string gets pretty unwieldy.


Kent
0
 
slightwv (䄆 Netminder) Commented:
mani_sai,

Now that Kdo is here, if you wish to take a look at the database option just let us know.  Either one of us can reopen the question for you.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 13
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now