Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
marcelloclaussen  
#1 Posted : Monday, July 17, 2017 8:18:11 AM(UTC)
marcelloclaussen

Rank: Newbie

Groups: Registered
Joined: 7/17/2017(UTC)
Posts: 3
United Kingdom
Location: London

Thanks: 1 times
Hi,

I am trying to Change Data depending on whether the contact is already in the database or not. What I am doing is this:

On the Change Data field, I am running the following Test Expression using dbselect():

Code:
SELECT CASE WHEN EXISTS (
    SELECT *
    FROM Table
    WHERE EMAIL = ‘#Email’
)
THEN CAST('TRUE' AS BIT)
ELSE CAST('FALSE' AS BIT) END


When I click on “Test Expression” it returns True or False as expected, but when updating the preview or running the job, it says:

Code:
An error was encountered when parsing an expression. Error details:

Tab: Change Data  Column: Test Expression  Row: 1

The expression  dbselect("T", "SELECT CASE WHEN EXISTS (

    SELECT *

    FROM Table

    WHERE EMAIL = '#Email'

)

THEN CAST('TRUE' AS BIT)

ELSE CAST('FALSE' AS BIT) END")  does not give a True or False result


I guess that since I have put TRUE and FALSE between single quotes it is being sent as a String and Inaport might be expecting a Boolean. I have tried changing it to: THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) but got the same result.

Maybe I am overworking this query, but I think this should work. Any advice?
ipadmin  
#2 Posted : Tuesday, July 18, 2017 8:26:37 AM(UTC)
ipadmin

Rank: Administration

Groups: Administrators
Joined: 8/17/2015(UTC)
Posts: 22

Was thanked: 2 time(s) in 2 post(s)
Hi Marcello

I think the issue is that the dbselect() will always return a string value, that tyou will then have to convert.

I suggest you modify to return teh 'TRUE' or 'FALSE', then test that:

dbselect() = 'TRUE' -- will result is a boolean true if the result of the dbselect() is TRUE

Regards

Support
thanks 1 user thanked ipadmin for this useful post.
marcelloclaussen on 7/20/2017(UTC)
marcelloclaussen  
#3 Posted : Thursday, July 20, 2017 1:42:22 AM(UTC)
marcelloclaussen

Rank: Newbie

Groups: Registered
Joined: 7/17/2017(UTC)
Posts: 3
United Kingdom
Location: London

Thanks: 1 times
Ok, so as I mentioned in my first post, I may be overworking this query, so I have managed to do it in a shorter, simpler and more efficient version as below:

Code:
dbselect(“T”, “SELECT COUNT(*) FROM Contact WHERE EMAIL IN (‘#Email’)”)=’1’


This SQL query returns the count of entries, as it is a requirement in our environment that emails must be unique, it will always return 1 or 0. Dbselect is now delivering TRUE or FALSE to Inaport as expected.

Thank you ipadmin, your reply put me in the right direction.

Edited by user Thursday, July 20, 2017 1:45:45 AM(UTC)  | Reason: Not specified

ipadmin  
#4 Posted : Thursday, July 20, 2017 6:24:02 AM(UTC)
ipadmin

Rank: Administration

Groups: Administrators
Joined: 8/17/2015(UTC)
Posts: 22

Was thanked: 2 time(s) in 2 post(s)
Glad to help.
Users browsing this topic
Guest
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF | YAF © 2003-2018, Yet Another Forum.NET
This page was generated in 0.102 seconds.

Notification

Icon
Error