StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Query Sql Count and Business Object

Query Sql Count and Business Object

Olivier — Thu, 27 Feb 2014 08:14:42 GMT

Hello

I have a simple query to count in a sales graph and i group by seller

but Strata Bo ask me all primary key in my view and my query is not disctint or grouping.

Can you tell me , how you start your query with count(*) and some group by on Stratraframe
to learn and reproduce this.

thanks
Olivier,

RE: Query Sql Count and Business Object

Edhy Rijo — Thu, 27 Feb 2014 08:14:42 GMT

Hi Olivier,
You have to change the way you are thinking from VFP to a SQL DBA so you can build your queries better in SQL.

I would build your query this way, for testing purpose set the value of the ProductName and ProductPrice parameters then comment them for production. What makes this query generic in a way that could accept a Product Name or a Product Price is the values of the parameters which are expected as '%' or zero, and in this case since you are using the "LIKE" attribute, then we will test for the string '%' to test for empty, or you can create whatever condition you want to test as empty for the ProductName.

[codesnippet]
-- spGetProductByPriceOrName
declare @ProductName varchar(30)
declare @ProductPrice DECIMAL

set @ProductName = 'm.dupont'
set @ProductPrice = 120000

SELECT
    p.*
FROM
    Product p
WHERE
    (p.Name LIKE @ProductName OR @ProductName = '%') AND
    (p.Price >= @ProductPrice OR @ProductPrice = 0)
[/codesnippet]

In your VB program you would have a method or function like this:
[codesnippet]
        Public Sub MyName(ProductName As String, ProductPrice As Decimal)
            Using cmd As New SqlClient.SqlCommand()
                '-- Create the params
                cmd.CommandTimeout = 0
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "dbo.spGetProductByPriceOrName"

                cmd.Parameters.AddWithValue("@ProductName", ProductName.Trim + "%").SqlDbType = SqlDbType.VarChar
                cmd.Parameters.AddWithValue("@ProductPrice", ProductPrice).SqlDbType = SqlDbType.Int

                Me.ExcecuteScalar(cmd)
            End Using 'cmd
        End Sub
[/codesnippet]

RE: Query Sql Count and Business Object

Olivier — Thu, 27 Feb 2014 02:48:42 GMT

Hello Edhy

What do you think about this ! i want to build a dynamic query !
and do you think it's secure ?

[code]
declare @cName varchar(30);
declare @cPrice varchar(12);
declare @cCondition varchar(max);
declare @MyQuery varchar(max)

set @cName = 'm.dupont';
set @cPrice = '120000';

IF @cName <>''
set @cCondition = 'name like '+@cName+'%';

IF @cPrice <>''
set @cCondition = 'price>='+@cPrice;

SET @MyQuery = 'select * from product where ' + @cCondition
EXEC (@MyQuery)

[/code]

Do you see the way what i need ?

thanks
Olivier

RE: Query Sql Count and Business Object

Olivier — Wed, 26 Feb 2014 13:04:11 GMT

Hello Edhy,

I ll test tomorrow (it's night on france),
but i have already build a view before, and i m no way cause the bo ask primary key and i don't put this,
cause i want to group a record.

may be it's override primary key the solution,

i test and i come back to tell you !

thanks
Olivier

RE: Query Sql Count and Business Object

Edhy Rijo — Wed, 26 Feb 2014 13:03:08 GMT

Hi Olivier,

Here is a sample of a query I used to test before creating the stored procedures:
[codesnippet]
DECLARE @StartDate AS DATE = '1/1/2013'
DECLARE @EndDate AS DATE = '12/31/2013'
DECLARE @PK_InsuranceCompany AS INT = 0

SELECT
    v.CompanyName,
    SUM(v.BrokerCommissionValue) AS BrokerCommissionvalue
FROM
    vCompanyPayments v
WHERE
    CAST(v.ReceiptDate AS DATE) >= @StartDate AND CAST(v.ReceiptDate AS DATE) <= @EndDate AND
    BrokerCommissionvalue > 0 AND
    (v.PK_InsuranceCompany = @PK_InsuranceCompany OR @PK_InsuranceCompany = 0)

GROUP BY
    v.CompanyName

ORDER BY
    v.CompanyName
[/codesnippet]

Notice how I test for the value of the parameter @PK_InsuranceCompany to be = 0 in the WHERE condition, in this case in my VB application I pass either a real value greater than zero or just zero and the I will get all records for that Insurance Company PK or for all.
You can use similar code to replace your VFP coding and in VB you can control how to pass the parameters to your SQLCommand.

RE: Query Sql Count and Business Object

Olivier — Wed, 26 Feb 2014 12:54:41 GMT

thanks michel,

i ll check , the override primary key option.

Can you see the way to build an procedure stored like this ?
to get a condition to put in the Where ?
(i give you a code in vfp cause i dont' get a vb code)

[code]
If ! empty(m.nNBPIECES)
        m.cCondition = m.cCondition + " .AND. CLI_PIECMN<="+Alltr(str(m.nNBPIECES))
    Endif
    If ! empty(m.nSURFACE)
        m.cCondition = m.cCondition + " .AND. CLI_SURF<="+Alltr(str(m.nSURFACE+(m.nSURFACE*M.XPCKORL1/100)))
    Endif

select myfield from mytable where 1=1 and &cCondition

[/code]

it's the last line i don't get in tsql for my proc stor !

thanks
Olivier

RE: Query Sql Count and Business Object

Edhy Rijo — Wed, 26 Feb 2014 12:52:41 GMT

Hi Olivier, Michel,

Michel is right, sometimes a view and a stored procedure to fill the view is what is needed, also if all the aggregated fields are in the view the SQL to query the view may be pretty simple no needing to have a stored procedure for it.

My preference is to try to get all calculations done at the SQL Server level so I can simply use the views in my reports or graphs with a SF Business Binding Source as clean as possible.

RE: Query Sql Count and Business Object

Michel Levy — Wed, 26 Feb 2014 12:42:09 GMT

Hi Olivier,

If you don't want to use the agregating functions provided in your report engine, it is possible (I often prefer to agregate on the SQL Server).

here is a solution (there are also some other ways):

create a view on the server with the count(*) and grouping you need, map a BO on that view, you have the structure (don't forget to enforce a pseudo primary key on the BO - look at "override primary key specification" in the Advanced otions of the BO, choose the columns that may identify a line)
if you need some parameters, create a stored procedure resulting in the same strcuture, having th parameters you need
fill your BO with the stored procedure

That's all, guy!

[hr]

Salut Olivier,

Si tu ne veux pas utiliser les fonctions d'aggrégation de ton moteur de rapport, c'est faisable (je préfère souvent faire les agrégations sur le serveur SQL).

Voilà une solution (il y en a d'autres):

tu crées une vue sur le serveur, avec ton count(*) et tes group by, tu mappes un BO sur cette vue, ça te donne ta structure (n'oublie pas de forcer une pseudo PK sur le BO - regarde "override primary key specification" dans les options avancées du BO, tu choisis une combinaison de colonnes qui peuvent servir d'identifiant unique pour une ligne)
si tu as besoin de paramètres, tu crées une procédure stockée qui te donne le résultat voulu, avec les paramètres nécessaires
tu remplis ton BO avec la procédure stockée

Et c'est fini, mon ami!