Estou criando uma consulta no scriptcase com essa sentença SQL abaixo:
SELECT
A.CHAPA AS CHAPA,
B.NOME AS ‘COLABORADOR’,
UPPER(C.DESCRICAO) AS ‘SEÇÃO’,
(CONVERT (VARCHAR,MAX(A.INICIOPER),103)) AS ‘INÍCIO PERÍODO’,
(CONVERT (VARCHAR,MAX(A.FIMPER),103)) AS ‘FIM PERÍODO’,
DBO.FORMATO_HORA( (SUM((A.EXTRAANT)-(A.ATRASOANT+A.FALTAANT))),2) AS ‘ANTERIOR’,
DBO.FORMATO_HORA( (SUM((A.EXTRAATU)-(A.ATRASOATU+A.FALTAATU))),2) AS ‘PERIODO’,
DBO.FORMATO_HORA( (SUM((A.EXTRAANT+A.EXTRAATU)-(A.ATRASOANT+A.ATRASOATU+A.FALTAANT+A.FALTAATU))),2) AS ‘SALDO’
FROM ASALDOBANCOHOR A(NOLOCK), PFUNC B(NOLOCK), PSECAO C(NOLOCK), GCOLIGADA G(NOLOCK)
WHERE
G.CODCOLIGADA=B.CODCOLIGADA AND
B.CHAPA=A.CHAPA AND
B.CODCOLIGADA=A.CODCOLIGADA AND
C.CODIGO=B.CODSECAO AND C.CODCOLIGADA=B.CODCOLIGADA AND
B.CODSITUACAO <> 'D’AND
C.CODIGO = ‘001.3.4.3’ AND
A.CHAPA NOT IN (‘999998’) AND
A.INICIOPER >= ‘2017/01/01’
AND
A.FIMPER <= ‘2017/01/31’
AND
B.CODTIPO NOT IN(‘A’,‘D’) AND
(B.DATADEMISSAO IS NULL OR B.DATADEMISSAO BETWEEN ( SELECT TOP 1 INICIOMENSAL FROM APERIODO WHERE A.CODCOLIGADA=B.CODCOLIGADA GROUP BY INICIOMENSAL, CODCOLIGADA ORDER BY INICIOMENSAL DESC) AND
( SELECT TOP 1 FIMMENSAL FROM APERIODO WHERE A.CODCOLIGADA=B.CODCOLIGADA GROUP BY FIMMENSAL,CODCOLIGADA ORDER BY FIMMENSAL DESC))
GROUP BY A.CHAPA,B.NOME,B.DATAADMISSAO, B.CODSITUACAO,C.DESCRICAO, A.EXTRAANT,B.CHAPA,B.SALARIO,B.JORNADAMENSAL /60,B.SALARIO / (B.JORNADAMENSAL /60)
e esta mostrando a mensagem abaixo:
ERRO
Erro ao acessar o banco de dados
SQLState: 42S22 Error Code: 207 Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name ‘SECAO’. SQLState: 42S22 Error Code: 207 Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name ‘INICIO_PERIODO’. SQLState: 42S22 Error Code: 207 Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name ‘FIM_PERIODO’.
SelectLimit(SELECT CHAPA, COLABORADOR, SECAO, INICIO_PERIODO, FIM_PERIODO, ANTERIOR, PERIODO from (SELECT A.CHAPA AS CHAPA, B.NOME AS ‘COLABORADOR’, UPPER(C.DESCRICAO) AS ‘SEÇÃO’, (CONVERT (VARCHAR,MAX(A.INICIOPER),103)) AS ‘INÍCIO PERÍODO’, (CONVERT (VARCHAR,MAX(A.FIMPER),103)) AS ‘FIM PERÍODO’, DBO.FORMATO_HORA( (SUM((A.EXTRAANT)-(A.ATRASOANT+A.FALTAANT))),2) AS ‘ANTERIOR’, DBO.FORMATO_HORA( (SUM((A.EXTRAATU)-(A.ATRASOATU+A.FALTAATU))),2) AS ‘PERIODO’, DBO.FORMATO_HORA( (SUM((A.EXTRAANT+A.EXTRAATU)-(A.ATRASOANT+A.ATRASOATU+A.FALTAANT+A.FALTAATU))),2) AS ‘SALDO’ FROM ASALDOBANCOHOR A(NOLOCK), PFUNC B(NOLOCK), PSECAO C(NOLOCK), GCOLIGADA G(NOLOCK) WHERE G.CODCOLIGADA=B.CODCOLIGADA AND B.CHAPA=A.CHAPA AND B.CODCOLIGADA=A.CODCOLIGADA AND C.CODIGO=B.CODSECAO AND C.CODCOLIGADA=B.CODCOLIGADA AND B.CODSITUACAO <> 'D’AND C.CODIGO = ‘001.3.4.3’ AND A.CHAPA NOT IN (‘999998’) AND A.INICIOPER >= ‘2017/03/01’ AND A.FIMPER <= ‘2017/03/31’ AND B.CODTIPO NOT IN(‘A’,‘D’) AND (B.DATADEMISSAO IS NULL OR B.DATADEMISSAO BETWEEN ( SELECT TOP 1 INICIOMENSAL FROM APERIODO WHERE A.CODCOLIGADA=B.CODCOLIGADA GROUP BY INICIOMENSAL, CODCOLIGADA ORDER BY INICIOMENSAL DESC) AND ( SELECT TOP 1 FIMMENSAL FROM APERIODO WHERE A.CODCOLIGADA=B.CODCOLIGADA GROUP BY FIMMENSAL,CODCOLIGADA ORDER BY FIMMENSAL DESC)) GROUP BY A.CHAPA,B.NOME,B.DATAADMISSAO, B.CODSITUACAO,C.DESCRICAO, A.EXTRAANT,B.CHAPA,B.SALARIO,B.JORNADAMENSAL /60,B.SALARIO / (B.JORNADAMENSAL /60) ) nm_sel_esp , 17, 0)
Se executar no BUILDER funciona sem problema e no banco de dados também.