SQL Query with a variable?

[Former Member]

In Inventory > Item Master Data > Properties

There are 'Property Name' and a column of checkboxes..

How can I write a query to select the Property Names when the checkboxes are checked?

My problem is the column of checkboxes does not show a table or column where its located, it only shows a variable..

Is there away I can use variables in my SQL query?

Any help is appreciated, thank you.

[Former Member · Accepted solution]

i dont think that code will execute in the Query Generator since it runs on SQL Server

i am a little confused by that but I appreciate your feedback

[Former Member · Accepted solution]

David,

The code I submited is in VB.

You just need to adapt it to SQL.

Best regards,

Vítor Vieira

[Former Member]

Hi David,

Each of those properties are linked to a field named QryGroupX, where the X starts at 1 and ends at 64. Each columns takes a "Y" ou "N" value.

Don't know if this is what you want:

    Public Function GetBPProperties(ByRef oCompany As SAPbobsCOM.Company, ByRef oApplication As SAPbouiCOM.Application, ByRef CardCode As String) As String
        GetBPProperties = ""
        Try
            Dim oRS As SAPbobsCOM.Recordset = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset)

            For i As Integer = 1 To 64
                oRS.DoQuery("SELECT * FROM [dbo].[OCRD] T0 WHERE T0.CardCode='" & CardCode & "' AND T0.QryGroup" & i.ToString & " = 'Y'")
                If oRS.RecordCount > 0 Then
                    GetBPProperties += ", " & i
                End If
            Next
        Catch ex As Exception
            oApplication.MessageBox(ex.Message)
        End Try
    End Function

Hope it helps,

Vítor Vieira