March 5, 2013 at 10:15 am
I have a table where I need to take any number of financial distributions and stack the accounts into a string that can be used as an array.
SELECT POPRCTNM, ACTINDX FROM POP30390 //this would for example return say 3 to 6 rows of data per POPRCTNM each with different ACTINDX numbers and related values (in the database but not requested)
So for example:
RCT0117382 3061
RCT0117382 3061
RCT0117382 46
What I want would be
RCT0117382 3061 3061 46
I then would be able to look at ITEMS on the receipt and if they are of a certain class, check if the expected ACTINDX is in the list used (so I can identify problems from improper human processes).
So, how would I create a table that groups on the PO Receipt Number and has a string field that I can then use crystal to convert to a searchable array for specific account indexes?
March 5, 2013 at 10:29 am
You could try something like this (without having your tables, I couldn't test that this actually works):
SELECT POPRCTNM,
STUFF((SELECT ','+ CAST(ACTINDX AS varchar)
FROM POP30390 a
WHERE a.POPRCTNM = d1.POPRCTNM
GROUP BY a.ACTINDX
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM POP30390 d1
But if your goal is to search for a receipt containing a certain number, you should be able to write this as a query without using the FOR XML hack. What conditions are you looking for that would identify a user-entry error?
March 5, 2013 at 11:20 am
Thanks. That works. But how do I name the column? I added "as ACCTSTRING" to the end of the "STUFF" line and it still shows (no column name) in the results.
As far as what I am doing. Having a view with the receipt and a rolled up field of the account indexes means I can link this to the PO Receipt line item details which lets me determine select which items I need to check and that these items are received with financial distributions into the proper financial accounts. For example, Item XYZ should be received into distribution account index 760. The receipt may have a distribution record with other account indexes as well based on the credits and debits that need to be recorded in the T-account model for accounting. The concern comes when parts of our sales inventoy class are received with no distribution into 760 (or 758 or 759) and thus we have an out of balance issue between what is called our "Subledger" and the "General Ledger". I just had to manually find a $13,000 out of balance error and now having gone through that, want to replicate that in a report that simply highlights those issues to be resolved.
So in Crystal Reports, I will take this and have it show Reciepts where Items of our sales inventory classes have no distribution into accounts 758, 759, or 760. But, I can't just simply say that because other accounts are involved and that gives a bunch of junk and goose chasing. Make sense?
March 5, 2013 at 11:24 am
it's not obvious where the alias goes;
here's two examples:
SELECT POPRCTNM,
STUFF((SELECT ','+ CAST(ACTINDX AS varchar)
FROM POP30390 a
WHERE a.POPRCTNM = d1.POPRCTNM
GROUP BY a.ACTINDX
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
AS AliasForStuff
FROM POP30390 d1
SELECT POPRCTNM,
AliasForStuff = STUFF((SELECT ','+ CAST(ACTINDX AS varchar)
FROM POP30390 a
WHERE a.POPRCTNM = d1.POPRCTNM
GROUP BY a.ACTINDX
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM POP30390 d1
Lowell
March 5, 2013 at 11:32 am
I'm glad it's working and Lowell concisely described how to name the returned attribute.
Still, I would love to see an alternative to this query.
March 5, 2013 at 11:37 pm
If you can use the SQLCLR here is an Aggregate Function I wrote to simplify the syntax for these types of queries that performs comparably (sometimes better, sometimes slightly worse) than the XML method you were shown.
GROUP_CONCAT string aggregate for SQL Server[/url]
Here is what your query might look like:
SELECT POPRCTNM,
dbo.GROUP_CONCAT(CAST(ACTINDX AS VARCHAR)) AS AliasForStuff
FROM POP30390 d1
GROUP BY POPRCTNM;
If you wanted a duplicate-free list just add DISTINCT into the Aggregate:
SELECT POPRCTNM,
dbo.GROUP_CONCAT(DISTINCT CAST(ACTINDX AS VARCHAR)) AS AliasForStuff
FROM POP30390 d1
GROUP BY POPRCTNM;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2013 at 1:22 pm
Ok, so the results have duplicates. Is there a way to limit it to distinct? I am getting an error about Group By not working. No details exactly. Also, the GROUP_CONCAT option doesn't work. Apparently that is a MySQL feature not on MS SQL 2008.
I do appreciate the help you all are giving. Just need to now limit to 1 record per POPRCTNM for it to work. I have the rest of my filters working on the Crystal side to compare to the account that should be used.
March 7, 2013 at 1:28 pm
opc.three (3/5/2013)
If you can use the SQLCLR here is an Aggregate Function I wrote to simplify the syntax for these types of queries that performs comparably (sometimes better, sometimes slightly worse) than the XML method you were shown...
That's flipping cool. Thanks for sharing.
March 7, 2013 at 1:31 pm
jmceuin (3/7/2013)
Also, the GROUP_CONCAT option doesn't work. Apparently that is a MySQL feature not on MS SQL 2008.
Did you read the CodePlex site I linked to? dbo.GROUP_CONCAT() is a SQLCLR object that mimics the functionality offered by the MySQL GROUP_CONCAT() function, but on SQL Server. Before you can use it you have to download the object from the CodePlex site and install it in your database. It's not an exact replacement for what MySQL offers due to some SQLCLR implementation limitations but it is very close. Once installed you can say things like this:
SELECT POPRCTNM,
dbo.GROUP_CONCAT(DISTINCT CAST(ACTINDX AS VARCHAR)) AS AliasForStuff
FROM POP30390 d1
GROUP BY POPRCTNM;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2013 at 1:36 pm
Sorry missed that. And I did more tinkering and just added and additional GROUP BY POPRCTNM to the very end of the statement rather than in the middle and it eliminated the duplicates. The error comes up but still processes anyway. Odd. Anyway. Otherwise, per rules if I don't have to install anything, I don't have to go through a bunch of change control nonsense. Adding a "view" to the database has been ruled as a non-issue for change control because it is not the same as changing a table or data nor installing software. Anyway...
Is there a difference in the performance or load that one method places against the database vs the other?
March 7, 2013 at 1:54 pm
jmceuin (3/7/2013)
Sorry missed that. And I did more tinkering and just added and additional GROUP BY POPRCTNM to the very end of the statement rather than in the middle and it eliminated the duplicates. The error comes up but still processes anyway. Odd. Anyway. Otherwise, per rules if I don't have to install anything, I don't have to go through a bunch of change control nonsense. Adding a "view" to the database has been ruled as a non-issue for change control because it is not the same as changing a table or data nor installing software. Anyway...
Adding dbo.GROUP_CONCAT is not 'installing' anything per se. It's just another database object, same as how a VIEW would be considered, a SQLCLR Aggregate Function to be exact. However, using dbo.GROUP_CONCAT will require enabling SQLCLR on the instance in case it is not already enabled, so in that sense it may trip up your change control process since that requires a server option change.
Is there a difference in the performance or load that one method places against the database vs the other?
The SQLCLR performs slightly better in some cases than the XML method, and the XML method performs slightly better in other cases. For general discussion they are comparable on performance. It is all presented on the CodePlex site under documentation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply