REST passing variable into select or Load

[ChannaK]

Dear Below are my rest API 

i am extracting Parts Details for Each creditmemos

my issue here is how can i add "vCreditmemosID" variable to select clause, i need to see creditmemo and parts in single table?

i tried to add $(vCreditmemosID) in to [parts] table but dosent work and suggestion

creditmemos:

LOAD

    id as MemoID

FROM [lib://Downloads:Data/creditmemos.qvd]

(qvd);

FOR vCreditmemos = 0 to NoOfRows('creditmemos')

    LET  vCreditmemosID = peek('MemoID',$(vCreditmemos),'creditmemos');

RestConnectorMasterTable:

SQL SELECT 

"success",

"__KEY_root",

(SELECT "TEST",

"id",

"invoiceId",

"debitMemoId",

"amount",

"createdDate",

"createdById",

"updatedDate",

"updatedById",

"__FK_parts"

FROM "parts" FK "__FK_parts")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION(Url "https://rest.eu.zuora.com/v1/creditmemos/$(vCreditmemosID)/parts", HTTPHEADER "Authorization" "Bearer TEST");

;

next vCreditmemos;

[parts]:

LOAD

[id] ,

[invoiceId],

[debitMemoId],

[amount],

[createdDate],

[createdById],

[updatedDate],

[updatedById],

[__FK_parts] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_parts]);

[root]:

LOAD [success],

[__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

[rubenmarin]

Hi, based on the documentation it looks right: https://www.zuora.com/developer/api-references/api/operation/GET_CreditMemoParts/

I would try to cretae a test passing a fixed creditmemoid and make some tries until you get the data parts of the credit memo. Once you get to know how it works it will be easier to add it in the bucle.

[ChannaK]

@rubenmarin  thank you Ruben

i just want to $(vCreditmemosID) add this variable into select statement, which i am passing into variable

SQL SELECT 

"success",

"__KEY_root",

(SELECT "TEST",

"id",

"invoiceId",

"debitMemoId",

"amount",

"createdDate",

"createdById",

"updatedDate",

"updatedById",

$(vCreditmemosID) as MemoID,//some thing like this but not working

"__FK_parts"

FROM "parts" FK "__FK_parts")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION(Url "https://rest.eu.zuora.com/v1/creditmemos/$(vCreditmemosID)/parts", HTTPHEADER "Authorization" "Bearer TEST");

;

[ChannaK · Accepted solution]

[parts]:

LOAD [id],

'$(vCreditmemosID)' as TEST,

[invoiceId],

[debitMemoId],

[amount],

[createdDate],

[createdById],

[updatedDate],

[updatedById],

[__FK_parts] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_parts]);

since it is creating multiple parts tables i created folder and load all, then i do union