I create a Segment in the design and run the create list without issue.
i then load the Segment into my Marketing Program and when I run it i get the following error message:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 42029] Subquery contains too many values for the IN predicate. (HY000)</
I'm not sure what is causing this issue. Any help would be appreciated.
Here is the full message:
<?xml version="1.0" encoding="us-ascii" ?>
- <jobInfo>
- <jobStats>
<jobID>375</jobID>
<jobType>WriteListFiles</jobType>
<jobUser>USER</jobUser>
<jobState>Error</jobState>
<jobTotalMilliSec>1m 47s 770ms</jobTotalMilliSec>
<jobStartedTime>2010-05-03T15:09:48Z</jobStartedTime>
<jobFinishedTime>2010-05-03T15:11:36Z</jobFinishedTime>
<jobIsCancelling>N</jobIsCancelling>
- <exception>
<message>Job request of type "WriteListFiles" failed.</message>
- <exception>
<message>Error executing the list generation SQL.</message>
- <exception>
<message>Error in executing cursor for WorkNode (Id:0)</message>
- <exception>
<message>Odbc driver returned an error (SQLExecDirectW).</message>
- <exception>
<message>State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 42029] Subquery contains too many values for the IN predicate. (HY000)</message>
</exception>
- <exception>
<message>SQL Issued: SET VARIABLE DISABLE_CACHE_HIT=1 , DISABLE_CACHE_SEED=1 :SELECT saw_0 saw_0, saw_1 saw_1, saw_2 saw_2, saw_3 saw_3, saw_4 saw_4, saw_5 saw_5, saw_6 saw_6, saw_7 saw_7, saw_8 saw_8, saw_9 saw_9, saw_10 saw_10, saw_11 saw_11, saw_12 saw_12, saw_13 saw_13, saw_14 saw_14, saw_15 saw_15, saw_16 saw_16, saw_17 saw_17, saw_18 saw_18, saw_19 saw_19 FROM ((SELECT Account."Account Name" saw_0, Account."Account Location" saw_1, case WHEN "Account Address"."Street Address"= 'Unspecified' THEN NULL ELSE "Account Address"."Street Address" END saw_2, case WHEN "Account Address".City = 'Unspecified' THEN NULL ELSE "Account Address".City END saw_3, case WHEN "Account Address".State = 'Unspecified' THEN NULL ELSE "Account Address".State END saw_4, case WHEN "Account Address"."Postal Code" = 'Unspecified' THEN NULL ELSE "Account Address"."Postal Code" END saw_5, case WHEN "Account Address".Country = 'Unspecified' THEN NULL ELSE "Account Address".Country END saw_6, "Account Organization".Organization saw_7, Contact."First Name" saw_8, Contact."Last Name" saw_9, Contact."Job Title" saw_10, Contact."Person UId" saw_11, '1-28I8Q' saw_12, '1-28I8L' saw_13, '1-1U6E0' saw_14, 3 saw_15, 1001 saw_16, CAST(truncate((RCOUNT(1) / 10000 + 1),0) as varchar(40)) saw_17, Contact.ROW_ID saw_18, Account.ROW_ID saw_19 FROM "Campaign Load - Accounts" WHERE ( Account.ROW_ID IN (( ( ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Account Profile".Status = 'Active') AND ("- Account Profile".Type IN ('Business', 'Household', 'Organization')) AND ("- Account Profile".Class <> 'Designation') AND ("- Account Address"."Delivery Status" NOT IN ('Deceased', 'Undeliverable', 'Unknown')) AND ("- Account Address".Country = 'USA') ) INTERSECT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'N') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) UNION ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'Y') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) EXCEPT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" = 'Non Cash') AND ("- Fact - Transaction Items Non-Anon"."# Transactions" > 0) ))) AND (Contact."Rank on Contact by Account" = 1) ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5, saw_6, saw_7, saw_8, saw_9, saw_10, saw_11, saw_12, saw_13, saw_14, saw_15, saw_16, saw_18, saw_19) ) DT</message>
</exception>
</exception>
</exception>
</exception>
</exception>
</jobStats>
- <detailedInfo>
- <JobDescription Type="List Generation for">
<Description>Writing : Campaign Load - Accounts example <br> Campaign: Giving Report Test</Description>
<Location>Location: /siebel/fs/Marketing/EAI</Location>
</JobDescription>
- <ListParameters IsRequalifying="true">
<NameValuePair Name="campaignID" Value="1-28I8Q" />
<NameValuePair Name=""- Contact Profile".ROW_ID" />
<NameValuePair Name="currentDateShort" Value="5/3/2010" />
<NameValuePair Name="queryCount" />
<NameValuePair Name="primaryOrganizationName" Value="Default Organization" />
<NameValuePair Name="organizationID" />
<NameValuePair Name="tokenNumber" Value="1001" />
<NameValuePair Name="controlGroupPct" Value="0" />
<NameValuePair Name="currentTimeShort" Value="3:09 PM" />
<NameValuePair Name="loadNumber" Value="3" />
<NameValuePair Name="pctAllocationFlag" Value="Y" />
<NameValuePair Name="treatmentID" Value="1-1U6E0" />
<NameValuePair Name="campaignCode" Value="1-3756122" />
<NameValuePair Name="campaignName" Value="Giving Report Test" />
- <TemplateInfo TemplateForEach="segments">
- <Instance>
<NameValuePair Name="segmentPath" Value="/shared/CCCi/Giving Report US 2 Pg Cash Only - DMC" />
<NameValuePair Name="segmentID" Value="1-28I8L" />
<NameValuePair Name="segmentPredicate" Value="Account.ROW_ID IN (( ( ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Account Profile".Status = 'Active') AND ("- Account Profile".Type IN ('Business', 'Household', 'Organization')) AND ("- Account Profile".Class <> 'Designation') AND ("- Account Address"."Delivery Status" NOT IN ('Deceased', 'Undeliverable', 'Unknown')) AND ("- Account Address".Country = 'USA') ) INTERSECT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'N') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) UNION ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'Y') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) EXCEPT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" = 'Non Cash') AND ("- Fact - Transaction Items Non-Anon"."# Transactions" > 0) ))" />
</Instance>
</TemplateInfo>
<NameValuePair Name="offerName" Value="Giving Report Parent Offer" />
<NameValuePair Name="currentUser" Value="USER" />
<NameValuePair Name="allocationLimit" Value="100" />
<NameValuePair Name="currentTimeLong" Value="3:09:48 PM" />
<NameValuePair Name="currentTimeODBC" Value="15:09:48" />
<NameValuePair Name="positionID" />
<NameValuePair Name="constrainingWaveID" Value="''" />
<NameValuePair Name="marketingFileSystem" Value="/siebel/fs/" />
<NameValuePair Name="organizationName" />
<NameValuePair Name="currentDateLong" Value="Monday, May 03, 2010" />
<NameValuePair Name="currentDateODBC" Value="2010-05-03" />
<NameValuePair Name="currentDateTimeShort" Value="5/3/2010 3:09:48 PM" />
<NameValuePair Name="constrainingStageFlag" Value="N" />
<NameValuePair Name="treatmentChannel" Value="Direct Mail" />
<NameValuePair Name="currentDateTimeLong" Value="Monday, May 03, 2010 3:09:48 PM" />
<NameValuePair Name="currentDateTimeODBC" Value="2010-05-03 15:09:48" />
<NameValuePair Name="currentDatetime" Value="5/3/2010 3:09:48 PM" />
<NameValuePair Name="formatName" Value="Campaign Load - Accounts example" />
<NameValuePair Name="batchSize" Value="10000" />
<NameValuePair Name="batchNumber" Value="CAST(truncate((RCOUNT(1) / 10000 + 1),0) as varchar(40))" />
<NameValuePair Name="jobID" Value="375" />
</ListParameters>
- <PrepareSegmentPredicate>
- <PredicateInstance CacheOverride="Default" CountOverride="Default" RefreshCache="false">
- <ListCatalog Name=""Campaign Load - Accounts"">
<PrimaryQualifiedItem Name="Accounts" />
<SecondaryQualifiedItem Name="Contacts" />
</ListCatalog>
</PredicateInstance>
<ExecutionOrderMgr TotalTime="0ms" WorkNodes="0" />
<ListPredicateSQL>Account.ROW_ID IN (( ( ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Account Profile".Status = 'Active') AND ("- Account Profile".Type IN ('Business', 'Household', 'Organization')) AND ("- Account Profile".Class <> 'Designation') AND ("- Account Address"."Delivery Status" NOT IN ('Deceased', 'Undeliverable', 'Unknown')) AND ("- Account Address".Country = 'USA') ) INTERSECT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'N') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) UNION ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'Y') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) EXCEPT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" = 'Non Cash') AND ("- Fact - Transaction Items Non-Anon"."# Transactions" > 0) ))</ListPredicateSQL>
</PrepareSegmentPredicate>
- <ExecutionOrderMgr TotalTime="1m 47s 484ms" WorkNodes="1">
- <WorkNodeExecLogical Order="0" Id="0" State="Error" Time="1m 47s 483ms" CursorID="256269" CursorState="Error" CursorTime="1m 47s 479ms">
- <ExecutedSQL>
<SQL>SET VARIABLE DISABLE_CACHE_HIT=1 , DISABLE_CACHE_SEED=1 :SELECT saw_0 saw_0, saw_1 saw_1, saw_2 saw_2, saw_3 saw_3, saw_4 saw_4, saw_5 saw_5, saw_6 saw_6, saw_7 saw_7, saw_8 saw_8, saw_9 saw_9, saw_10 saw_10, saw_11 saw_11, saw_12 saw_12, saw_13 saw_13, saw_14 saw_14, saw_15 saw_15, saw_16 saw_16, saw_17 saw_17, saw_18 saw_18, saw_19 saw_19 FROM ((SELECT Account."Account Name" saw_0, Account."Account Location" saw_1, case WHEN "Account Address"."Street Address"= 'Unspecified' THEN NULL ELSE "Account Address"."Street Address" END saw_2, case WHEN "Account Address".City = 'Unspecified' THEN NULL ELSE "Account Address".City END saw_3, case WHEN "Account Address".State = 'Unspecified' THEN NULL ELSE "Account Address".State END saw_4, case WHEN "Account Address"."Postal Code" = 'Unspecified' THEN NULL ELSE "Account Address"."Postal Code" END saw_5, case WHEN "Account Address".Country = 'Unspecified' THEN NULL ELSE "Account Address".Country END saw_6, "Account Organization".Organization saw_7, Contact."First Name" saw_8, Contact."Last Name" saw_9, Contact."Job Title" saw_10, Contact."Person UId" saw_11, '1-28I8Q' saw_12, '1-28I8L' saw_13, '1-1U6E0' saw_14, 3 saw_15, 1001 saw_16, CAST(truncate((RCOUNT(1) / 10000 + 1),0) as varchar(40)) saw_17, Contact.ROW_ID saw_18, Account.ROW_ID saw_19 FROM "Campaign Load - Accounts" WHERE ( Account.ROW_ID IN (( ( ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Account Profile".Status = 'Active') AND ("- Account Profile".Type IN ('Business', 'Household', 'Organization')) AND ("- Account Profile".Class <> 'Designation') AND ("- Account Address"."Delivery Status" NOT IN ('Deceased', 'Undeliverable', 'Unknown')) AND ("- Account Address".Country = 'USA') ) INTERSECT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'N') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) UNION ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'Y') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) EXCEPT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" = 'Non Cash') AND ("- Fact - Transaction Items Non-Anon"."# Transactions" > 0) ))) AND (Contact."Rank on Contact by Account" = 1) ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5, saw_6, saw_7, saw_8, saw_9, saw_10, saw_11, saw_12, saw_13, saw_14, saw_15, saw_16, saw_18, saw_19) ) DT</SQL>
</ExecutedSQL>
- <exception>
<message>Odbc driver returned an error (SQLExecDirectW).</message>
- <exception>
<message>State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 42029] Subquery contains too many values for the IN predicate. (HY000)</message>
</exception>
- <exception>
<message>SQL Issued: SET VARIABLE DISABLE_CACHE_HIT=1 , DISABLE_CACHE_SEED=1 :SELECT saw_0 saw_0, saw_1 saw_1, saw_2 saw_2, saw_3 saw_3, saw_4 saw_4, saw_5 saw_5, saw_6 saw_6, saw_7 saw_7, saw_8 saw_8, saw_9 saw_9, saw_10 saw_10, saw_11 saw_11, saw_12 saw_12, saw_13 saw_13, saw_14 saw_14, saw_15 saw_15, saw_16 saw_16, saw_17 saw_17, saw_18 saw_18, saw_19 saw_19 FROM ((SELECT Account."Account Name" saw_0, Account."Account Location" saw_1, case WHEN "Account Address"."Street Address"= 'Unspecified' THEN NULL ELSE "Account Address"."Street Address" END saw_2, case WHEN "Account Address".City = 'Unspecified' THEN NULL ELSE "Account Address".City END saw_3, case WHEN "Account Address".State = 'Unspecified' THEN NULL ELSE "Account Address".State END saw_4, case WHEN "Account Address"."Postal Code" = 'Unspecified' THEN NULL ELSE "Account Address"."Postal Code" END saw_5, case WHEN "Account Address".Country = 'Unspecified' THEN NULL ELSE "Account Address".Country END saw_6, "Account Organization".Organization saw_7, Contact."First Name" saw_8, Contact."Last Name" saw_9, Contact."Job Title" saw_10, Contact."Person UId" saw_11, '1-28I8Q' saw_12, '1-28I8L' saw_13, '1-1U6E0' saw_14, 3 saw_15, 1001 saw_16, CAST(truncate((RCOUNT(1) / 10000 + 1),0) as varchar(40)) saw_17, Contact.ROW_ID saw_18, Account.ROW_ID saw_19 FROM "Campaign Load - Accounts" WHERE ( Account.ROW_ID IN (( ( ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Account Profile".Status = 'Active') AND ("- Account Profile".Type IN ('Business', 'Household', 'Organization')) AND ("- Account Profile".Class <> 'Designation') AND ("- Account Address"."Delivery Status" NOT IN ('Deceased', 'Undeliverable', 'Unknown')) AND ("- Account Address".Country = 'USA') ) INTERSECT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'N') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) UNION ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" IN ('Cash', 'Check', 'Credit Card', 'EFT', 'Wire')) AND ("Transaction Item".Amount > 0) AND ("Transaction Item"."Pass Thru Flag" = 'Y') AND ("- Fact - Transaction Items Non-Anon"."# Designations" BETWEEN 1 AND 5) ) ) EXCEPT ( SELECT "- Account Profile".ROW_ID AS ID FROM "CCCi Analytics" WHERE ("- Transaction Date"."Transaction Year" = '2009') AND (Transaction."Transaction Type" = 'Donation') AND (Transaction."Sub Type" = 'Non Cash') AND ("- Fact - Transaction Items Non-Anon"."# Transactions" > 0) ))) AND (Contact."Rank on Contact by Account" = 1) ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5, saw_6, saw_7, saw_8, saw_9, saw_10, saw_11, saw_12, saw_13, saw_14, saw_15, saw_16, saw_18, saw_19) ) DT</message>
</exception>
</exception>
</WorkNodeExecLogical>
</ExecutionOrderMgr>
</detailedInfo>
</jobInfo>