6 Replies Latest reply: Jul 6, 2017 2:24 PM by Rafael Souza RSS

    Criteria field in SQL Query

    Rafael Souza

      Good afternoon people,
      I'm trying to create an incremental load script in QlikView but I can not get the criteria to be interpreted correctly in SQL when loading the ODBC data:


      LET vHoje = Date(Today(),'YYYYMMDD');

      LET vStart = Now();

      LET vFileExists = IF(FileSize('C:\Users\rafael.souza1\Desktop\Apps_Rafael\BaseHeranca.QVD') > 0, -1, 0);

      LET vCriterio = IF(IsNull(vCriterio), vHoje, vCriterio);


      OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=True;User ID=rbsouza;Data Source=DBGESTAOCP;Extended Properties="DSN=DBGESTAOCP;SERVER=pta-sybctr02;PORT=9000;UID=rbsouza;"] (XPassword is BOKHFZJMTbbKHdFMRRMGTMB);


      [TabelaFONTE]:

      SELECT

      a.NuContrato,

      a.NuCpfCnpj,

      a.DtContrato,

      a.CdFilial,

      a.CdProduto,

      a.CdModalidadeProduto,

      a.CdPessoa,

      a.CdOperador AS 'CdColaborador',

      b.VrFinanciamento,

      SUM(CASE WHEN c.FlSomaCusto = 'S' THEN c.VrCusto ELSE 0 END) AS 'VlrCusto'

      FROM

      DBGESTAOCP..TbContrato a,

      DBGESTAOCP..TbContratoFinanceiro b,

      DBGESTAOCP..TbContratoCusto c

      WHERE

        a.NuContrato = b.NuContrato

      AND a.NuContrato = c.NuContrato

      AND a.SqContratoFinanceiroAtivo = b.SqContratoFinanceiro

      AND a.SqContratoFinanceiroAtivo = c.SqContratoFinanceiro

      AND b.SqContratoFinanceiro = c.SqContratoFinanceiro

      AND a.DtLiquidacaoEfetiva IS NULL

      AND a.DtCancelamento IS NULL

      AND a.DtContrato < '$(vCriterio)'

      GROUP BY a.NuContrato, a.NuCpfCnpj, a.DtContrato, a.CdFilial, a.CdProduto, a.CdModalidadeProduto, a.CdPessoa, a.CdOperador, b.VrFinanciamento;


      The sql is reading the inverted variable, instead of reading 06/07/2017 is reading 07/06/2017, Can you help me?