Skip to main content
Solved

Nested API Call with case / if statement in RSD file


andrew.gebhard
TimeXtender Xpert
Forum|alt.badge.img+2

Hi community, 

I am trying to work with the LinkedIn API to retrieve details about UGC posts (i.e. engagements, clicks, etc.). This information is not part of the LinkedIn Marketing Solutions CDATA connector, so I am using the CDATA provider for REST 2022. 

I first made a call to the posts API to ‘Find Posts by Account’: Posts API - LinkedIn | Microsoft Learn. After that I need to pass each Post ID into the Organizational Entity Share Statistics API to ‘Retrieve Statistics for Specific UGC Posts’: Organization Share Statistics - LinkedIn | Microsoft Learn

The problem I am facing is that the Posts API returns two types of posts (which cannot be filtered in the API): shares and UcgPosts. Each of those types of posts needs a different URL format in the ‘Organizational Share Statistics’ API. So, I need a way to only pass one type of post into the second API. UCG posts are posts where the ID starts with 'urn:li:ugcPost:'

The file RSD 1.txt works, until the first ID from the Posts API is a UcgPost, instead of a share. The first Ucg post that gets passed into the second API returns the following error from the API: 

{"message":"Array parameter 'shares' value 'urn:li:ugcPost:7071823186039828482' is invalid. Reason: Deserializing output 'urn:li:ugcPost:7071823186039828482' failed","status":400}

So I tried to create a case statement in RSD 2.txt , but to no avail. It does not seem to be doing anything different: 

Does anyone have any experience with a filter or case statement between two tables in a nested API call like this? 

Kind regards,

Andrew - E-mergo 

Best answer by Thomas Lind

Hi @andrew.gebhard 

I know this has been a bit dead for a while, but I actually think I know why you get this error.

So if the result of the query after the where clause is not a (1,2,3,4,5) or similar kind of list you will need to set in a = instead. It can’t handle only one value it seems or you will get the error message you see.

SELECT 
[organizationalEntity]
, [totalShareStatistics.clickCount]
, [totalShareStatistics.commentCount]
, [totalShareStatistics.engagement]
, [totalShareStatistics.impressionCount]
, [totalShareStatistics.likeCount]
, [totalShareStatistics.shareCount]
, [totalShareStatistics.uniqueImpressionsCount]
, [ugcPost] 
FROM [REST].[LinkedInUGCPostStatistics CDATA]

WHERE [ugcPost] = (SELECT [id] FROM [REST].[PostIdList] where left( [id],15)= 'urn:li:ugcPost:' )
View original
Did this topic help you find an answer to your question?

4 replies

Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • 952 replies
  • June 12, 2023

Hi @andrew.gebhard 

You could change the setup of the file to be using a Query Slicer instead. There is a few posts about this and I mention it in the RSD guide.

Essentially you would then be able to create a Where clause that did the case statement or the query could be an or statement to select one or the other option.

SELECT * FROM LinkedInUGCPostStatistics WHERE IsUgc = true and ID IN (SELECT ID FROM Posts) OR IsUgc = false ID IN (Select ID From Posts)

 


andrew.gebhard
TimeXtender Xpert
Forum|alt.badge.img+2
  • Author
  • TimeXtender Xpert
  • 64 replies
  • June 12, 2023

Hi Thomas, 

I now have the following RSD files as attached and I have the following query table:

SELECT 
[organizationalEntity]
, [totalShareStatistics.clickCount]
, [totalShareStatistics.commentCount]
, [totalShareStatistics.engagement]
, [totalShareStatistics.impressionCount]
, [totalShareStatistics.likeCount]
, [totalShareStatistics.shareCount]
, [totalShareStatistics.uniqueImpressionsCount]
, [ugcPost] 
FROM [REST].[LinkedInUGCPostStatistics CDATA]

WHERE [ugcPost] IN (SELECT [id] FROM [REST].[PostIdList] where left( [id],15)= 'urn:li:ugcPost:' )

However, I get the error:

2023-06-12T14:44:43.910+02:00    0    The parameter ugcPost is required to be set for this operation.

 

See full error file attached. Am I missing a parameter in my RSD?

 

Kind regards,

Andrew


Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • 952 replies
  • Answer
  • October 17, 2023

Hi @andrew.gebhard 

I know this has been a bit dead for a while, but I actually think I know why you get this error.

So if the result of the query after the where clause is not a (1,2,3,4,5) or similar kind of list you will need to set in a = instead. It can’t handle only one value it seems or you will get the error message you see.

SELECT 
[organizationalEntity]
, [totalShareStatistics.clickCount]
, [totalShareStatistics.commentCount]
, [totalShareStatistics.engagement]
, [totalShareStatistics.impressionCount]
, [totalShareStatistics.likeCount]
, [totalShareStatistics.shareCount]
, [totalShareStatistics.uniqueImpressionsCount]
, [ugcPost] 
FROM [REST].[LinkedInUGCPostStatistics CDATA]

WHERE [ugcPost] = (SELECT [id] FROM [REST].[PostIdList] where left( [id],15)= 'urn:li:ugcPost:' )

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

@andrew.gebhard please let us know if the answer above helps in resolving the issue you are experiencing


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings