Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Calling sql function through javascript node OR sql node

Avatar

Avatar
Validate 1
Level 1
Ramaswami
Level 1

Likes

0 likes

Total Posts

100 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Applaud 5
View profile

Avatar
Validate 1
Level 1
Ramaswami
Level 1

Likes

0 likes

Total Posts

100 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Applaud 5
View profile
Ramaswami
Level 1

18-03-2020

Hi Team, 

 

I have a SQL function which is returning some value and i need to check what is the value it is returning. Do we have any javascript function in adobe check what the function is returning through a workflow. 

 

i tried like below inside javascript node of workflow but it errored out: 

nova_offer_validateActiveOfferCountTest(x integer) : this is my postgre function 

 

 function getCampaignLabel1()
{
return sqlGetString(nova_offer_validateActiveOfferCountTest(5560752))
}
logInfo("Function is returning:"+getCampaignLabel1())

 

SQL function under Administration>Configuration>SQL scripts>rsn:postgresql-rsn-novaOffer.sql : 

 

CREATE OR REPLACE FUNCTION nova_offer_validateActiveOfferCountTest(P_IOPERATIONID INTEGER)
RETURNS VARCHAR AS $func$
DECLARE
L_overActiveOfferLimit INTEGER := 0;
OFFERCOUNT INTEGER :=0;
L_errorCode VARCHAR(8) := '1';
BEGIN
SELECT IRSNMAXACTIVENOVAOFFERS INTO L_overActiveOfferLimit
FROM NMSOPERATION no
WHERE no.IOPERATIONID = P_IOPERATIONID;
SELECT COUNT(RNO.INOVAOFFERSID) cnt INTO OFFERCOUNT
FROM RN_NOVA_OFFER RNO
WHERE RNO.IOPERATIONID = P_IOPERATIONID
AND RNO.IACTIVE = 1;
if OFFERCOUNT > L_overActiveOfferLimit then
L_errorCode := '0';
END IF;
RETURN L_errorCode;
EXCEPTION
WHEN others THEN
RAISE EXCEPTION 'nova_offer__validateActiveOfferCount ERROR: %', SQLERRM;
END;
$func$ language plpgsql;

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

961 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

961 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
wodnicki
MVP

18-03-2020

Hi,

 

Use sqlGetString(). If you want to expose the function to xtk, follow this guide:

 

Thanks,

-Jon

Answers (0)