CODEWITHSUNDEEP
sqlsql-server
Felipe Bandeira
Felipe Bandeira

Reputation: 3

SQL Server query need to use drop down values from list, how to?

What I need is: I have a screen with some fields and a dropdown list with options for a query, and the drop down list is my secondary ordering rule, like for price, name of client, etc. What happened: Doesn't matter what you select in the drop list, it always returned the same order. The ORDER BY part was like this:

ORDER BY

CAD.NOME_CADASTRO,
PAG.DATA_EMISSAO,
PAG.NUM_DOCTO,
PAG.DATA_VENCTO,
PAG.VALOR_TITULO,

Then I changed to this:

DECLARE @TESTE CHAR(1)
SET @TESTE = 'prmOrd'

'...'

ORDER BY
CASE
 @TESTE
WHEN 'N'  THEN PAG.DATA_EMISSAO
WHEN 'D' THEN  PAG.DATA_VENCTO
WHEN 'C' THEN  CAD.NOME_CADASTRO
WHEN 'V' THEN  PAG.VALOR_TITULO
END

The prmOrd holds the values N, D, C and V which is how the values in the dropdown are handled internally. Now it works, but when generating the report, if I select the N, D or V option, it works fine. When I select C, I get an error which states

Cannot generate report. Could not open DataSet.:Inprincipal

Inprincipal is the name of the query I'm working on. A co-worker told me to cast as char the commands, it would solve the problem, but I can't make it work.

And there's another strange thing happening, it when generating the report I select N, D or V then C, all except C are generated following the command and I get that error message. Then I proceed to close the window and start over, and if I try to generate C first, The error is different:

SQL Server Error: Conversion failed when converting date and/or time from character string.

And from there, trying to generate any of the working options, return this error:

Error: Missing query table name or procedure name.

Any idea what I should do to make it work?

++++++

As requested, this is the full query in original state:

SELECT 
PAG.COD_FILIAL,
PAG.COD_DOCTO,
PAG.SERIE_DOCTO,
PAG.NUM_DOCTO,
PAG.NUM_PARCELA,
PAG.QTDE_PARCELA,
PAG.DATA_VENCTO,
PAG.COD_BANCO_CAIXA,
PAG.COD_FORNECEDOR,
CAD.NOME_CADASTRO,
CAD.APELIDO,
PAG.COD_FORMA_PGTO,
PAG.VALOR_TITULO,
PAG.VALOR_SALDO,
PAG.NATUREZA_TITULO,
PAG.NUM_NOSSO_PAG,
PAG.DATA_EMISSAO,
(CASE SUBSTRING(PAG.COD_DOCTO,1,2)
      WHEN 'AD' THEN PAG.VALOR_TITULO
      ELSE -PAG.VALOR_TITULO
  END) AS VALOR_TITDC,
(CASE SUBSTRING(PAG.COD_DOCTO,1,2)
      WHEN 'AD' THEN PAG.VALOR_SALDO
      ELSE -PAG.VALOR_SALDO
  END) AS VALOR_SALDODC,
PAG.STATUS_TITULO,
PAG.OBSERVACAO
FROM 
TBTITULOPAG PAG INNER JOIN TBCADASTROGERAL CAD
    ON PAG.COD_FORNECEDOR = CAD.COD_CADASTRO 
WHERE  
 PAG.COD_FILIAL BETWEEN '100'  AND '100'  AND
 PAG.COD_DOCTO BETWEEN ''  AND 'Z'  AND
 PAG.NUM_DOCTO BETWEEN '0' AND '999999' AND
 PAG.COD_FORNECEDOR BETWEEN '0' AND '999999' AND 
 PAG.COD_FORMA_PGTO BETWEEN '0'  AND '999999' AND
 PAG.STATUS_TITULO like 'A'  AND
 PAG.NATUREZA_TITULO LIKE 'R'  AND
 PAG.DATA_EMISSAO BETWEEN '01/01/2017'  AND '12/31/2023'

ORDER BY 
CAD.NOME_CADASTRO,
PAG.DATA_EMISSAO,
PAG.NUM_DOCTO,
PAG.DATA_VENCTO,
PAG.VALOR_TITULO

Upvotes: 0

Views: 95

Answers (1)

jarlh
jarlh

Reputation: 44796

The case expression's different return data types must be compatible. Instead do the following:

ORDER BY
CASE @TESTE WHEN 'N'  THEN PAG.DATA_EMISSAO END,
CASE @TESTE WHEN 'D'  THEN PAG.DATA_VENCTO END,
CASE @TESTE WHEN 'C'  THEN CAD.NOME_CADASTRO END,
CASE @TESTE WHEN 'V'  THEN PAG.VALOR_TITULO END

Only one of them will make any order difference.

Upvotes: 1

Related Questions