May 22, 2018 at 10:25 pm
I used Stuff() to combine the rows for ModifyName after unpivoting Charity & Super and placing them in Curr's column, but the result of the currNames didn't comply with Cognos's rules regarding blobs. I did all I could to configure Report Studio with no FM access to get the query to return data. I did, but I couldn't filter it. So I'm trying to replace the xml path() portion of the query to produce: currType and ModifyName is concatenated with a hyphen if ModifyName is populated
| ComicsCode | ComicsName | issuenum | slipnum | Date | currType | curr | kType | currrNameS |
| 2055 | marvel | DC9999 | DC99801 | 1/24/1901 | Modify | -55 | Help | Modify - Modify-Sky |
| 2055 | marvel | DC9999 | DC99801 | 1/24/1901 | Modify | -3 | Charity | Modify; Modify-Sky |
| 2055 | marvel | DC9999 | DC99801 | 1/24/1901 | Modify | -2 | Charity | Modify; Modify-Sky |
| 2055 | marvel | DC9999 | DC99801 | 1/24/1901 | Modify | 0 | Super | Modify; Modify-Sky |
from this:
d:
| ComicsCode | ComicsName | issuenum | slipnum | Date | currType | curr | charity | ModifyName | Super |
| 2055 | marvel | DC9999 | DC99801 | 1/24/1901 | Modify | -55 | -2 | Modify-Sky | 0 |
| 2055 | marvel | DC9999 | DC99801 | 1/24/1901 | Modify | 0 | -3 |
---^^^^^^^^^^replace
--- Using a blob column in this query requires that the query subject [Q1].[SQL2] must have either a key or a unique index.
May 23, 2018 at 12:42 am
Simple syntax error in the value function
😎
This should work
SELECT
d.[currType]
,STUFF(
(SELECT
'; ' + d.currName
from [foo].[dbo].[zool] d
where f.[issuenum] = d.[issuenum]
and f.[slipnum] = d.[slipnum]
and f.[comicsName] = d.[comicsName]
FOR XML PATH(''), TYPE).value('(./text())[1]','NVARCHAR(2000)'),1,1,'') AS [currNames]
FROM [foo].[dbo].tbl] f
LEFT outer join [foo].[dbo].[Zool] d
on f.[billNo] = d.[billNo];
May 23, 2018 at 6:53 am
Eirikur Eiriksson - Wednesday, May 23, 2018 12:42 AMSimple syntax error in the value function
😎This should work
SELECT
d.[currType]
,STUFF(
(SELECT
'; ' + d.currName
from [foo].[dbo].[zool] d
where f.[issuenum] = d.[issuenum]
and f.[slipnum] = d.[slipnum]
and f.[comicsName] = d.[comicsName]
FOR XML PATH(''), TYPE).value('(./text())[1]','NVARCHAR(2000)'),1,1,'') AS [currNames]
FROM [foo].[dbo].tbl] f
LEFT outer join [foo].[dbo].[Zool] d
on f.[billNo] = d.[billNo];
Thank you, Is there a correlated query or self join approach that could be used to keep the data type of the colmn from becoming a blob?
May 23, 2018 at 7:13 am
ackrite55 - Wednesday, May 23, 2018 6:53 AMEirikur Eiriksson - Wednesday, May 23, 2018 12:42 AMSimple syntax error in the value function
😎This should work
SELECT
d.[currType]
,STUFF(
(SELECT
'; ' + d.currName
from [foo].[dbo].[zool] d
where f.[issuenum] = d.[issuenum]
and f.[slipnum] = d.[slipnum]
and f.[comicsName] = d.[comicsName]
FOR XML PATH(''), TYPE).value('(./text())[1]','NVARCHAR(2000)'),1,1,'') AS [currNames]
FROM [foo].[dbo].tbl] f
LEFT outer join [foo].[dbo].[Zool] d
on f.[billNo] = d.[billNo];Thank you, Is there a correlated query or self join approach that could be used to keep the data type of the colmn from becoming a blob?
No need for that, the value function does the conversion to nvarchar(2000) in this case.
😎
The text() function also avoids the construct of an XML for the output, cuts the effort almost in half. The role of the stuff function is simply to remove the leading semicolon, does not contribute to the concatenation.
May 23, 2018 at 8:34 am
Eirikur Eiriksson - Wednesday, May 23, 2018 7:13 AMackrite55 - Wednesday, May 23, 2018 6:53 AMEirikur Eiriksson - Wednesday, May 23, 2018 12:42 AMSimple syntax error in the value function
😎This should work
SELECT
d.[currType]
,STUFF(
(SELECT
'; ' + d.currName
from [foo].[dbo].[zool] d
where f.[issuenum] = d.[issuenum]
and f.[slipnum] = d.[slipnum]
and f.[comicsName] = d.[comicsName]
FOR XML PATH(''), TYPE).value('(./text())[1]','NVARCHAR(2000)'),1,1,'') AS [currNames]
FROM [foo].[dbo].tbl] f
LEFT outer join [foo].[dbo].[Zool] d
on f.[billNo] = d.[billNo];Thank you, Is there a correlated query or self join approach that could be used to keep the data type of the colmn from becoming a blob?
No need for that, the value function does the conversion to nvarchar(2000) in this case.
😎
The text() function also avoids the construct of an XML for the output, cuts the effort almost in half. The role of the stuff function is simply to remove the leading semicolon, does not contribute to the concatenation.
Thanks!!!! Tested & compatibility is confirmed. Greatly appreciated!
May 23, 2018 at 9:16 am
ackrite55 - Wednesday, May 23, 2018 8:34 AMEirikur Eiriksson - Wednesday, May 23, 2018 7:13 AMackrite55 - Wednesday, May 23, 2018 6:53 AMEirikur Eiriksson - Wednesday, May 23, 2018 12:42 AMSimple syntax error in the value function
😎This should work
SELECT
d.[currType]
,STUFF(
(SELECT
'; ' + d.currName
from [foo].[dbo].[zool] d
where f.[issuenum] = d.[issuenum]
and f.[slipnum] = d.[slipnum]
and f.[comicsName] = d.[comicsName]
FOR XML PATH(''), TYPE).value('(./text())[1]','NVARCHAR(2000)'),1,1,'') AS [currNames]
FROM [foo].[dbo].tbl] f
LEFT outer join [foo].[dbo].[Zool] d
on f.[billNo] = d.[billNo];Thank you, Is there a correlated query or self join approach that could be used to keep the data type of the colmn from becoming a blob?
No need for that, the value function does the conversion to nvarchar(2000) in this case.
😎
The text() function also avoids the construct of an XML for the output, cuts the effort almost in half. The role of the stuff function is simply to remove the leading semicolon, does not contribute to the concatenation.Thanks!!!! Tested & compatibility is confirmed. Greatly appreciated!
You are very welcome!
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply