How to pass Pervasive SELECT DB parameter

Question

How to pass Pervasive SELECT DB parameter

Navigation

#1 by (0 votes)

1

I'm having to work with Pervasive database, so I'm having trouble passing a parameter to make a SELECT

I've tried using:

DBCmd.Parameters.Add("@inicio", PsqlDbType.VarChar).Value = date_inicio;
DBCmd.Parameters.Add("@fim", PsqlDbType.VarChar).Value = date_fim;

And I also tried this:

DBCmd.Parameters.AddWithValue("@inicio", "'" + date_inicio + "'");
DBCmd.Parameters.AddWithValue("@fim", "'" + date_fim + "'");

I know that by default Pervasive, dates must be in AAAA-MM-DD format.

That is, date_inicio = 2016-01-01 and date_fim = 2016-12-31

Below is the query:

SELECT a.code_client AS CODIGO, a.raison_sociale AS 'RAZÃO SOCIAL', a.Adresse_1 AS ENDEREÇO, a.Ville AS CIDADE, e.etat AS 'ESTADO', a.code_postal_siege AS CEP, a.Adresse_2 AS BAIRRO, a.Specialite_client AS SEGMENTO, 
                                CASE a.Code_representant 
                                WHEN 0 THEN 'BRAYTON'
                                WHEN 30225 THEN 'DOUGLAS'
                                WHEN 30327 THEN 'FURLAN'
                                WHEN 30431 THEN 'ALLAN'
                                WHEN 30318 THEN 'RUFINO'
                                WHEN 30140 THEN 'GURIAN'
                                END AS 'REPRESENTANTE',
                                SUM(c.montant_ht) AS 'PREÇO PROD',
                                SUM(c.montant_ht - d.vl_icms) AS 'PREÇO LIQ',
                                SUM(d.vl_ipi) AS 'VALOR IPI',
                                SUM(d.vl_icms) AS 'VALOR ICMS',
                                SUM(d.vl_pis) AS 'VALOR PIS',
                                SUM(d.vl_cofins) AS 'VALOR COFINS'
                                FROM client AS a
                                LEFT JOIN  operateur AS b
                                ON a.Code_representant = b.code
                                LEFT JOIN commande AS c
                                ON a.code_client = c.code_cl
                                LEFT JOIN BRAYTONAUX.PedidoVendaAUX AS d
                                ON c.affaire = d.cd_pedido
                                LEFT JOIN client2 AS e
                                ON a.code_client = e.code
                                WHERE c.date_creat BETWEEN CONVERT(@inicio, SQL_DATE) AND CONVERT(@fim, SQL_DATE)
                                GROUP BY a.code_client, a.raison_sociale, a.Adresse_1, a.Ville, e.etat, a.code_postal_siege, a.Adresse_2, a.Specialite_client, a.Code_representant

sql select pervasive

asked by anonymous 02.04.2017 / 00:03

1 answer

How to put punctuation in a sql row Error canceling an image export job on EC2

score 0 · Accepted Answer

By default Pervasive uses the ? to identify the parameter, instead of @ in the query.

So, it looks like this:

SELECT a.code_client AS CODIGO, a.raison_sociale AS 'RAZÃO SOCIAL', a.Adresse_1 AS ENDEREÇO, a.Ville AS CIDADE, e.etat AS 'ESTADO', a.code_postal_siege AS CEP, a.Adresse_2 AS BAIRRO, a.Specialite_client AS SEGMENTO, 
                            CASE a.Code_representant 
                            WHEN 0 THEN 'BRAYTON'
                            WHEN 30225 THEN 'DOUGLAS'
                            WHEN 30327 THEN 'FURLAN'
                            WHEN 30431 THEN 'ALLAN'
                            WHEN 30318 THEN 'RUFINO'
                            WHEN 30140 THEN 'GURIAN'
                            END AS 'REPRESENTANTE',
                            SUM(c.montant_ht) AS 'PREÇO PROD',
                            SUM(c.montant_ht - d.vl_icms) AS 'PREÇO LIQ',
                            SUM(d.vl_ipi) AS 'VALOR IPI',
                            SUM(d.vl_icms) AS 'VALOR ICMS',
                            SUM(d.vl_pis) AS 'VALOR PIS',
                            SUM(d.vl_cofins) AS 'VALOR COFINS'
                            FROM client AS a
                            LEFT JOIN  operateur AS b
                            ON a.Code_representant = b.code
                            LEFT JOIN commande AS c
                            ON a.code_client = c.code_cl
                            LEFT JOIN BRAYTONAUX.PedidoVendaAUX AS d
                            ON c.affaire = d.cd_pedido
                            LEFT JOIN client2 AS e
                            ON a.code_client = e.code
                            WHERE c.date_creat BETWEEN CONVERT(?, SQL_DATE) AND CONVERT(?, SQL_DATE)
                            GROUP BY a.code_client, a.raison_sociale, a.Adresse_1, a.Ville, e.etat, a.code_postal_siege, a.Adresse_2, a.Specialite_client, a.Code_representant

Being the parameter declaration like this:

DBCmd.Parameters.Add("@inicio", PsqlDbType.Date).Value = date_inicio;
DBCmd.Parameters.Add("@fim", PsqlDbType.Date).Value = date_fim;