Workflow SQL Query, dynamic IN operator

Permanently deleted user

January 18, 2016 20:51

Hi Folks,

I`m using the Workflow SQL Query Tool.

I`ve defined Parameters to make the Command dynamic.

However, I`m attempting to build the query using the IN operator eg. WHERE City IN ('Paris','London') based on values obtained from an itemlist in a form. I`ve converted the itemList to an Array and used a forEach to iterate and get each individual component. The issues I`m coming across are:

how to structure the parameter to place a single quote around each individual component eg 'Paris' not Paris.
place the comma between the components eg 'Paris','London'
include the brackets eg. ('Paris','London')
lastly, pass the parameter to the command without messing with the brackets and single quotes.

Any help is greatly appreciated.

cheers,

Greg

Comments

10 comments

Permanently deleted user

January 19, 2016 22:28
Hi Greg,

I also looked at this but could never get the query to be passed to the SQL statement successfully. Check out this thread for more info https://support.geocortex.com/essentialsGSCForum?sub-nav=forum&main-nav=essentials&#!/feedtype=SINGLE_QUESTION_DETAIL&id=9066000000008z1AAA (https://support.geocortex.com/essentialsGSCForum?sub-nav=forum&main-nav=essentials&#!/feedtype=SINGLE_QUESTION_DETAIL&id=9066000000008z1AAA) . As of GE 4.3 there is capability for, "SQL Query and SQL Non-Query now accept DataTable parameter values if the backing database is SQL Server. This can be useful when creating parameterized IN clauses." according to the release notes. I haven't seen an example of this from anyone and am not sure how it needs to be setup to work. Hopefully someone from Latitude can provide some extra information.
0
Nico Burgerhart

January 20, 2016 10:21
You can try something like

WHERE YOURFIELDNAME IN (SELECT regexp_substr(:yourparametername,'[^,]+', 1, level) FROM dual CONNECT BY regexp_substr(:yourparametername, '[^,]+', 1, level) IS NOT NULL )

(YOURFIELDNAME is a text field and yourparametername is filled with a comma separated list of values)
0
Nico Burgerhart

January 20, 2016 10:22
The example is for an Oracle database.
0
Permanently deleted user

January 20, 2016 16:27
Hi Nico,

I'm on SQL Server... so no go with your code. Transact SQL doesnt have an equivelant to regexp_substr. Thanks for the suggestion.

Hi Mark

I've implemented this solution by attempting to pass a DataTable as a parameter to the command. However, I get the error: Unhandled exception: 'One or more errors occurred.' in activity '1.10: SqlQuery - Roll'. One or more errors occurred. The table type parameter 'Status' must have a valid type name.
Reading this post, the solution seems to be to use a Stored Proceedure... however, I have no idea how to execute a SP from within a workflow, pass the parameter and then receive the response.

http://stackoverflow.com/questions/17817997/system-argumentexception-the-table-type-parameter-must-have-a-valid-type-name

thanks,

Greg
0
Permanently deleted user

January 20, 2016 20:23
Hi Greg

here is an example workflow plus the stored procedure it references:

----------- The workflow   -------------

<Activity mc:Ignorable="sads sap" x:Class="{x:Null}" sap:VirtualizedContainerService.HintSize="266,332" mva:VisualBasic.Settings="Assembly references and imported namespaces serialized as XML namespaces"

xmlns="http://schemas.microsoft.com/netfx/2009/xaml/activities"

xmlns:eac="clr-namespace:ESRI.ArcGIS.Client;assembly=ESRI.ArcGIS.Client"

xmlns:eacg="clr-namespace:ESRI.ArcGIS.Client.Geometry;assembly=ESRI.ArcGIS.Client"

xmlns:eact="clr-namespace:ESRI.ArcGIS.Client.Tasks;assembly=ESRI.ArcGIS.Client"

xmlns:gce="clr-namespace:Geocortex.Core.Extensions;assembly=Geocortex.Core"

xmlns:gfc="clr-namespace:Geocortex.Forms.Client;assembly=Geocortex.EssentialsWpfApi"

xmlns:gfci="clr-namespace:Geocortex.Forms.Client.Items;assembly=Geocortex.EssentialsWpfApi"

xmlns:gr="clr-namespace:Geocortex.Reporting;assembly=Geocortex.Reporting"

xmlns:gwa="clr-namespace:Geocortex.Workflow.Activities;assembly=Geocortex.Workflow"

xmlns:gwa1="clr-namespace:Geocortex.Workflow.Activities;assembly=Geocortex.Workflow.Activities"

xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

xmlns:mva="clr-namespace:Microsoft.VisualBasic.Activities;assembly=System.Activities"

xmlns:s="clr-namespace:System;assembly=System.Core"

xmlns:s1="clr-namespace:System;assembly=System"

xmlns:s2="clr-namespace:System;assembly=mscorlib"

xmlns:sa="clr-namespace:System.Activities;assembly=System.Activities"

xmlns:sads="http://schemas.microsoft.com/netfx/2010/xaml/activities/debugger"

xmlns:sap="http://schemas.microsoft.com/netfx/2009/xaml/activities/presentation"

xmlns:scg="clr-namespace:System.Collections.Generic;assembly=mscorlib"

xmlns:sd="clr-namespace:System.Data;assembly=System.Data"

xmlns:sl="clr-namespace:System.Linq;assembly=System.Core"

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">

<Sequence sap:VirtualizedContainerService.HintSize="226,252" mva:VisualBasic.Settings="Assembly references and imported namespaces serialized as XML namespaces">

    <Sequence.Variables>

      <Variable x:TypeArguments="sd:DataTable" Name="theDataTableSues" />

      <Variable x:TypeArguments="x:String" Name="strSues" />

      <Variable x:TypeArguments="x:String" Name="selectedLfCode" />

    </Sequence.Variables>

    <sap:WorkflowViewStateService.ViewState>

      <scg:Dictionary x:TypeArguments="x:String, x:Object">

        <x:Boolean x:Key="IsExpanded">True</x:Boolean>

      </scg:Dictionary>

    </sap:WorkflowViewStateService.ViewState>

    <gwa1:SqlQuery CommandText="exec dbo.[rdc_sp_GIS_qry_GIS_sue_LandFeat] @SUELIST, @Mode, @LfCode" ConnectionConfigurationName="OZONEDW"

    sap:VirtualizedContainerService.HintSize="204,128" Results="[theDataTableSues]">

      <gwa1:SqlQuery.Parameters>

        <InArgument x:TypeArguments="x:String" x:Key="@SUELIST">[strSues]</InArgument>

        <InArgument x:TypeArguments="x:String" x:Key="@Mode">Sues</InArgument>

        <InArgument x:TypeArguments="x:String" x:Key="@LfCode">[selectedLfCode]</InArgument>

      </gwa1:SqlQuery.Parameters>

    </gwa1:SqlQuery>

</Sequence>

</Activity>

---------------- And the Stored Procedure --------------

USE [Ozone_Live_Lite]

GO

/****** Object: StoredProcedure [dbo].[rdc_sp_GIS_qry_GIS_sue_LandFeat]    Script Date: 21/01/2016 9:20:35 a.m. ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

CREATE PROCEDURE [dbo].[rdc_sp_GIS_qry_GIS_sue_LandFeat]

    @sueList NVARCHAR(2000)

   ,@Mode NVARCHAR(20)

   ,@LfCode NVARCHAR(30)

AS

    BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

        SET NOCOUNT ON;

        IF @Mode = 'Codes'

            BEGIN

                SELECT DISTINCT

                    LF_Code AS Code

                   ,LF_Code_Desc AS [Desc]

                       --, LF_SubCode

                       --, LF_SubCode_Desc

                FROM

                    vw_GIS_Sue_LF AS p

                JOIN fn_GIS_iter_intlist_to_table(@sueList) AS s

                    ON p.SUE = s.number

            END

        IF @Mode = 'Sues'

            BEGIN

                SELECT DISTINCT

                    Sue

                       --, LF_SubCode

                       --, LF_SubCode_Desc

                FROM

                    vw_GIS_Sue_LF AS p

                JOIN fn_GIS_iter_intlist_to_table(@sueList) AS s

                    ON p.SUE = s.number

                       AND LF_Code = @LfCode

            END

    END

--------------------------------------

Hope that helps

Regards

Ralph Price
0
Permanently deleted user

January 28, 2016 16:46
Hi Ralph,

This is great, thanks for taking the time to explain. I'm sure others will appreciate this solution too.

cheers,

Greg
0
Permanently deleted user

June 13, 2016 23:18
Hi Ralph,

Would you mind if I transferred this workflow to our Code Gallery so others can easily find it and re-use it for their own purpose?

Regards,

Wayne

Latitude Geographics Support Analyst
0
Permanently deleted user

June 13, 2016 23:28
Hi Wayne

that would be fine. Thanks for asking.

Not necessarily the best way, most efficient etc but it is one that is working for us

Regards

Ralph Price
0
Permanently deleted user

June 13, 2016 23:35
Also should probably include links to:
These are other points on the web where very similar coding is used. I can't remember where I originally got my start from sadly.

Regards

Ralph Price
0
Permanently deleted user

June 14, 2016 22:11
Excellent, thanks Ralph. I have created the article here -

https://support.geocortex.com/essentialsGSCCodeGallery?sub-nav=codegall&main-nav=essentials&#!/feedtype=SINGLE_ARTICLE_DETAIL&dc=Geocortex_Essentials_cg&criteria=BESTANSWERS&id=kA460000000bljw

Please let me know if you would like anything changed.

Regards,

Wayne Richard

Latitude Geographics Group Ltd.

Head Office: 300 – 1117 Wharf Street Victoria, BC Canada V8W 1T7

Tel: (250) 381-8130 | Fax: (250) 381-8132 | wrichard@latitudegeo.com

Developers of Geocortex web-based mapping software | www.geocortex.com

An Esri Platinum Business Partner
0

Please sign in to leave a comment.

Workflow SQL Query, dynamic IN operator

Comments

Didn't find what you were looking for?