Problemas com construção de View

ilanocf · Abril 16, 2019, 1:23am

Olá pessoal,

Estou implantando o sistema no cliente,mas estou tentando gerar uma view que utilizo para fazer relatórios. Ao criar a mesma view em meu servidor a mesma é gerada sem problema algum, mas ao tentar gerar no banco do cliente ocorre o erro #1271 - Ilegal combinação de collations para operação ‘concat’.

Nesse select, tem uma função chamada mascara que também já foi criada no banco do cliente:

DELIMITER $ CREATE FUNCTION `mascara`( `val` VARCHAR(100), `mask` VARCHAR(100) ) RETURNS VARCHAR(100) CHARACTER SET utf8 DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE maskared VARCHAR(100) DEFAULT ""; DECLARE k INT DEFAULT 0; DECLARE i INT DEFAULT 0; WHILE i < CHAR_LENGTH(mask) DO SET i = i + 1; IF SUBSTRING(mask, i, 1) = '#' THEN IF k < CHAR_LENGTH(val) THEN SET k = k+1; SET maskared = CONCAT(maskared, SUBSTRING(val, k, 1)); END IF; ELSE IF i < CHAR_LENGTH(mask) THEN SET maskared = CONCAT(maskared, SUBSTRING(mask, i, 1)); END IF; END IF; END WHILE; RETURN maskared; END; $

E a consulta que preciso gerar a view é essa:

CREATE OR REPLACE VIEW V_PEDIDOS AS
SELECT A.ID, LPAD(A.IDPEDIDO, 5, ‘0’) AS PEDIDO,
DATE_FORMAT(H.DATAPEDIDO, ‘%d/%m/%Y’) AS DTPEDIDO,
H.DATAPEDIDO, H.IDCOLIGADA, H.IDCLIENTE, I.NOME AS CLIENTE,
mascara(I.CPFCNPJ, “##.###.###/####-##”) AS CNPJ,
M.RAZAOSOCIAL AS COLIGADA_NOME, mascara(M.CNPJ, “##.###.###/####-##”) AS COLIGADA_CNPJ,
CONCAT(M.ENDERECO, ', ', M.NUMERO, ’ - ‘, M.BAIRRO,
CASE WHEN M.COMPLEMENTO IS NULL THEN ‘’
WHEN TRIM(M.COMPLEMENTO) = ‘’ THEN ‘’
ELSE CONCAT(’ - ', M.COMPLEMENTO) END,
’ - Cidade: ', N.NOME, ’ - ', UPPER(O.SIGLA)
) AS COLIGADA_ENDERECO,

CONCAT(mascara(M.FONE, “(##) ####-####”),
’ - Celular: ', mascara(M.CELULAR, “(##) # ####-####”),
’ - E-mail: ‘, M.EMAIL,
CASE WHEN M.SITE IS NULL THEN ‘’
WHEN TRIM(M.SITE) = ‘’ THEN ‘’
ELSE CONCAT(’ - Home Page: ', M.SITE) END) AS COLIGADA_FONEEMAIL,

CONCAT(I.ENDERECO,
CASE WHEN I.NUMERO IS NULL THEN ‘’
WHEN TRIM(I.NUMERO) = ‘’ THEN ‘’
ELSE CONCAT(’, ‘, I.NUMERO) END,
CASE WHEN I.COMPLEMENTO IS NULL THEN ‘’
WHEN TRIM(I.COMPLEMENTO) = ‘’ THEN ‘’
ELSE CONCAT(’ - ‘, I.COMPLEMENTO) END,
CASE WHEN I.BAIRRO IS NULL THEN ‘’
WHEN TRIM(I.BAIRRO) = ‘’ THEN ‘’
ELSE CONCAT(’ - ', I.BAIRRO) END) AS ENDERECO,

CONCAT(CASE WHEN I.FONE1 IS NULL THEN ‘’
WHEN TRIM(I.FONE1) = ‘’ THEN ‘’
ELSE CONCAT('Telefone: ', mascara(I.FONE1, “(##) ####-####”)) END,
   CASE WHEN I.FONE2 IS NULL THEN ''
   WHEN TRIM(I.FONE2) = '' THEN ''
   ELSE CONCAT(' - Celular: ', mascara(I.FONE2, "(##) 9 ####-####")) END,
   
   CASE WHEN I.EMAIL IS NULL THEN ''
   WHEN TRIM(I.EMAIL) = '' THEN ''
   ELSE CONCAT(' - E-mail: ', I.EMAIL) END,
   'Cidade: ', K.NOME, ' - Estado: ', UPPER(L.SIGLA),
   ' - Cep: ', mascara(I.CEP, "##.###-###")) AS FONECIDADE,
H.IDFORMAPAGTO, J.NOME AS FORMAPAGTO, H.DESCONTOGERAL,
CASE H.STATUSVENDA WHEN 1 THEN ‘Pendente’
WHEN 2 THEN ‘Pago’
WHEN 3 THEN ‘Cancelado’
WHEN 4 THEN ‘Fechado pela Nobre’
WHEN 5 THEN ‘Fechado pelo Cliente’
ELSE ‘’ END AS SATUSDAVENDA, H.OBSERVACAO,
A.IDPRODUTO, A.QUANTIDADE, B.CODIGO,

CONCAT(
B.NOME,
CASE WHEN B.COMPLEMENTO IS NULL THEN ‘’
WHEN Trim(B.COMPLEMENTO) = ‘’ THEN ‘’ ELSE CONCAT(’ ‘, B.COMPLEMENTO) END,
CASE WHEN B.TAMANHO IS NULL THEN ‘’
WHEN Trim(B.TAMANHO) = ‘’ THEN ‘’ ELSE CONCAT(’ tam. ‘, B.TAMANHO) END,
CASE WHEN E.NOME IS NULL THEN ‘’
WHEN Trim(E.NOME) = ‘’ THEN ‘’ ELSE CONCAT(’ de cor ‘, E.NOME) END,
CASE WHEN D.NOME IS NULL THEN ‘’
WHEN Trim(D.NOME) = ‘’ THEN ‘’ ELSE CONCAT(’ da ', D.NOME) END
) AS PRODUTO,

ROUND(A.VALORUNITARIO, 2) AS VLRUNIT,
ROUND(A.DESCONTO, 2) AS DESCONTO, ROUND(A.VALORTOTAL, 2) AS VLTOTAL,
B.COMISSAO, B.FRETE, C.SIGLA AS UND, D.NOME AS MARCA, E.NOME AS COR,
F.NOME AS CATEGORIA, G.NOME AS SUBCATEGORIA, P.NOME AS TRANSPORTADORA,
(SELECT COUNT(*) FROM G002_CONDICOESPAGTO X WHERE X.IDFORMAPAGTO = H.IDFORMAPAGTO) AS PARCELAS

FROM G004_DETALHEPEDIDO A
INNER JOIN E005_PRODUTOS B ON (B.ID = A.IDPRODUTO)
INNER JOIN E004_UNIDADE C ON (C.ID = B.IDUNIDADE)
INNER JOIN E003_MARCA D ON (D.ID = B.IDMARCA)
INNER JOIN E002_CORES E ON (E.ID = B.IDCOR)
INNER JOIN E001_CATEGORIAS F ON (F.ID = B.IDCATEGORIA)
INNER JOIN E001_CATEGORIAS G ON (G.ID = B.IDSUBCATEGORIA)
INNER JOIN G003_PEDIDO H ON (H.ID = A.IDPEDIDO)
INNER JOIN C001_PESSOAS I ON (I.ID = H.IDCLIENTE)
INNER JOIN G001_FORMAPAGTO J ON (J.ID = H.IDFORMAPAGTO)
INNER JOIN A003_CIDADES K ON (K.ID = I.IDCIDADE AND K.IDESTADO = I.IDESTADO AND K.IDPAIS = I.IDPAIS)
INNER JOIN A002_ESTADOS L ON (L.ID = K.IDESTADO AND L.IDPAIS = K.IDPAIS)
INNER JOIN A006_COLIGADA M ON (M.ID = A.IDCOLIGADA)
INNER JOIN A003_CIDADES N ON (N.ID = M.IDCIDADE AND N.IDESTADO = M.IDESTADO AND N.IDPAIS = M.IDPAIS)
INNER JOIN A002_ESTADOS O ON (O.ID = M.IDESTADO AND O.IDPAIS = M.IDPAIS)
LEFT JOIN C001_PESSOAS P ON (P.ID = H.IDTRANSPORTE)

Se eu executar apenas a sentença, sem a linha de criação da view ele retorna vários erros:

Static analysis:
236 errors were found during analysis.

Unrecognized keyword. (near “AS” at position 2124)
Unexpected token. (near “SATUSDAVENDA” at position 2127)
Unexpected token. (near “,” at position 2139)
Unexpected token. (near “H” at position 2141)
Unexpected token. (near “.” at position 2142)
Unexpected token. (near “OBSERVACAO” at position 2143)
Unexpected token. (near “,” at position 2153)
Unexpected token. (near “A” at position 2156)
Unexpected token. (near “.” at position 2157)
Unexpected token. (near “IDPRODUTO” at position 2158)
Unexpected token. (near “,” at position 2167)
Unexpected token. (near “A” at position 2169)
Unexpected token. (near “.” at position 2170)
Unexpected token. (near “QUANTIDADE” at position 2171)
Unexpected token. (near “,” at position 2181)
Unexpected token. (near “B” at position 2183)
Unexpected token. (near “.” at position 2184)
Unexpected token. (near “CODIGO” at position 2185)
Unexpected token. (near “,” at position 2191)
Unrecognized keyword. (near “CONCAT” at position 2198)
Unexpected token. (near “(” at position 2204)
Unexpected token. (near “B” at position 2207)
Unexpected token. (near “.” at position 2208)
Unexpected token. (near “NOME” at position 2209)
Unexpected token. (near “,” at position 2213)
Unrecognized keyword. (near “CASE” at position 2216)
Unrecognized keyword. (near “WHEN” at position 2221)
Unexpected token. (near “B” at position 2226)
Unexpected token. (near “.” at position 2227)
Unexpected token. (near “COMPLEMENTO” at position 2228)
Unrecognized keyword. (near “IS” at position 2240)
Unrecognized keyword. (near “NULL” at position 2243)
Unrecognized keyword. (near “THEN” at position 2248)
Unexpected token. (near “’’” at position 2253)
Unrecognized keyword. (near “WHEN” at position 2257)
Unrecognized keyword. (near “Trim” at position 2262)
Unexpected token. (near “(” at position 2266)
Unexpected token. (near “B” at position 2267)
Unexpected token. (near “.” at position 2268)
Unexpected token. (near “COMPLEMENTO” at position 2269)
Unexpected token. (near “)” at position 2280)
Unexpected token. (near “=” at position 2282)
Unexpected token. (near “’’” at position 2284)
Unrecognized keyword. (near “THEN” at position 2287)
Unexpected token. (near “’’” at position 2292)
Unrecognized keyword. (near “ELSE” at position 2295)
Unrecognized keyword. (near “CONCAT” at position 2300)
Unexpected token. (near “(” at position 2306)
Unexpected token. (near “’ '” at position 2307)
Unexpected token. (near “,” at position 2310)
Unexpected token. (near “B” at position 2312)
Unexpected token. (near “.” at position 2313)
Unexpected token. (near “COMPLEMENTO” at position 2314)
Unexpected token. (near “)” at position 2325)
Unrecognized keyword. (near “END” at position 2327)
Unexpected token. (near “,” at position 2330)
Unrecognized keyword. (near “CASE” at position 2333)
Unrecognized keyword. (near “WHEN” at position 2338)
Unexpected token. (near “B” at position 2343)
Unexpected token. (near “.” at position 2344)
Unexpected token. (near “TAMANHO” at position 2345)
Unrecognized keyword. (near “IS” at position 2353)
Unrecognized keyword. (near “NULL” at position 2356)
Unrecognized keyword. (near “THEN” at position 2361)
Unexpected token. (near “’’” at position 2366)
Unrecognized keyword. (near “WHEN” at position 2370)
Unrecognized keyword. (near “Trim” at position 2375)
Unexpected token. (near “(” at position 2379)
Unexpected token. (near “B” at position 2380)
Unexpected token. (near “.” at position 2381)
Unexpected token. (near “TAMANHO” at position 2382)
Unexpected token. (near “)” at position 2389)
Unexpected token. (near “=” at position 2391)
Unexpected token. (near “’’” at position 2393)
Unrecognized keyword. (near “THEN” at position 2396)
Unexpected token. (near “’’” at position 2401)
Unrecognized keyword. (near “ELSE” at position 2404)
Unrecognized keyword. (near “CONCAT” at position 2409)
Unexpected token. (near “(” at position 2415)
Unexpected token. (near “’ tam. '” at position 2416)
Unexpected token. (near “,” at position 2424)
Unexpected token. (near “B” at position 2426)
Unexpected token. (near “.” at position 2427)
Unexpected token. (near “TAMANHO” at position 2428)
Unexpected token. (near “)” at position 2435)
Unrecognized keyword. (near “END” at position 2437)
Unexpected token. (near “,” at position 2440)
Unrecognized keyword. (near “CASE” at position 2443)
Unrecognized keyword. (near “WHEN” at position 2448)
Unexpected token. (near “E” at position 2453)
Unexpected token. (near “.” at position 2454)
Unexpected token. (near “NOME” at position 2455)
Unrecognized keyword. (near “IS” at position 2460)
Unrecognized keyword. (near “NULL” at position 2463)
Unrecognized keyword. (near “THEN” at position 2468)
Unexpected token. (near “’’” at position 2473)
Unrecognized keyword. (near “WHEN” at position 2477)
Unrecognized keyword. (near “Trim” at position 2482)
Unexpected token. (near “(” at position 2486)
Unexpected token. (near “E” at position 2487)
Unexpected token. (near “.” at position 2488)
Unexpected token. (near “NOME” at position 2489)
Unexpected token. (near “)” at position 2493)
Unexpected token. (near “=” at position 2495)
Unexpected token. (near “’’” at position 2497)
Unrecognized keyword. (near “THEN” at position 2500)
Unexpected token. (near “’’” at position 2505)
Unrecognized keyword. (near “ELSE” at position 2508)
Unrecognized keyword. (near “CONCAT” at position 2513)
Unexpected token. (near “(” at position 2519)
Unexpected token. (near “’ de cor '” at position 2520)
Unexpected token. (near “,” at position 2530)
Unexpected token. (near “E” at position 2532)
Unexpected token. (near “.” at position 2533)
Unexpected token. (near “NOME” at position 2534)
Unexpected token. (near “)” at position 2538)
Unrecognized keyword. (near “END” at position 2540)
Unexpected token. (near “,” at position 2543)
Unrecognized keyword. (near “CASE” at position 2546)
Unrecognized keyword. (near “WHEN” at position 2551)
Unexpected token. (near “D” at position 2556)
Unexpected token. (near “.” at position 2557)
Unexpected token. (near “NOME” at position 2558)
Unrecognized keyword. (near “IS” at position 2563)
Unrecognized keyword. (near “NULL” at position 2566)
Unrecognized keyword. (near “THEN” at position 2571)
Unexpected token. (near “’’” at position 2576)
Unrecognized keyword. (near “WHEN” at position 2580)
Unrecognized keyword. (near “Trim” at position 2585)
Unexpected token. (near “(” at position 2589)
Unexpected token. (near “D” at position 2590)
Unexpected token. (near “.” at position 2591)
Unexpected token. (near “NOME” at position 2592)
Unexpected token. (near “)” at position 2596)
Unexpected token. (near “=” at position 2598)
Unexpected token. (near “’’” at position 2600)
Unrecognized keyword. (near “THEN” at position 2603)
Unexpected token. (near “’’” at position 2608)
Unrecognized keyword. (near “ELSE” at position 2611)
Unrecognized keyword. (near “CONCAT” at position 2616)
Unexpected token. (near “(” at position 2622)
Unexpected token. (near “’ da '” at position 2623)
Unexpected token. (near “,” at position 2629)
Unexpected token. (near “D” at position 2631)
Unexpected token. (near “.” at position 2632)
Unexpected token. (near “NOME” at position 2633)
Unexpected token. (near “)” at position 2637)
Unrecognized keyword. (near “END” at position 2639)
Unexpected token. (near “)” at position 2644)
Unrecognized keyword. (near “AS” at position 2646)
Unexpected token. (near “PRODUTO” at position 2649)
Unexpected token. (near “,” at position 2656)
Unrecognized keyword. (near “ROUND” at position 2663)
Unexpected token. (near “(” at position 2668)
Unexpected token. (near “A” at position 2669)
Unexpected token. (near “.” at position 2670)
Unexpected token. (near “VALORUNITARIO” at position 2671)
Unexpected token. (near “,” at position 2684)
Unexpected token. (near “2” at position 2686)
Unexpected token. (near “)” at position 2687)
Unrecognized keyword. (near “AS” at position 2689)
Unexpected token. (near “VLRUNIT” at position 2692)
Unexpected token. (near “,” at position 2699)
Unrecognized keyword. (near “ROUND” at position 2702)
Unexpected token. (near “(” at position 2707)
Unexpected token. (near “A” at position 2708)
Unexpected token. (near “.” at position 2709)
Unexpected token. (near “DESCONTO” at position 2710)
Unexpected token. (near “,” at position 2718)
Unexpected token. (near “2” at position 2720)
Unexpected token. (near “)” at position 2721)
Unrecognized keyword. (near “AS” at position 2723)
Unexpected token. (near “DESCONTO” at position 2726)
Unexpected token. (near “,” at position 2734)
Unrecognized keyword. (near “ROUND” at position 2736)
Unexpected token. (near “(” at position 2741)
Unexpected token. (near “A” at position 2742)
Unexpected token. (near “.” at position 2743)
Unexpected token. (near “VALORTOTAL” at position 2744)
Unexpected token. (near “,” at position 2754)
Unexpected token. (near “2” at position 2756)
Unexpected token. (near “)” at position 2757)
Unrecognized keyword. (near “AS” at position 2759)
Unexpected token. (near “VLTOTAL” at position 2762)
Unexpected token. (near “,” at position 2769)
Unexpected token. (near “B” at position 2772)
Unexpected token. (near “.” at position 2773)
Unexpected token. (near “COMISSAO” at position 2774)
Unexpected token. (near “,” at position 2782)
Unexpected token. (near “B” at position 2784)
Unexpected token. (near “.” at position 2785)
Unexpected token. (near “FRETE” at position 2786)
Unexpected token. (near “,” at position 2791)
Unexpected token. (near “C” at position 2793)
Unexpected token. (near “.” at position 2794)
Unexpected token. (near “SIGLA” at position 2795)
Unrecognized keyword. (near “AS” at position 2801)
Unexpected token. (near “UND” at position 2804)
Unexpected token. (near “,” at position 2807)
Unexpected token. (near “D” at position 2809)
Unexpected token. (near “.” at position 2810)
Unexpected token. (near “NOME” at position 2811)
Unrecognized keyword. (near “AS” at position 2816)
Unexpected token. (near “MARCA” at position 2819)
Unexpected token. (near “,” at position 2824)
Unexpected token. (near “E” at position 2826)
Unexpected token. (near “.” at position 2827)
Unexpected token. (near “NOME” at position 2828)
Unrecognized keyword. (near “AS” at position 2833)
Unexpected token. (near “COR” at position 2836)
Unexpected token. (near “,” at position 2839)
Unexpected token. (near “F” at position 2842)
Unexpected token. (near “.” at position 2843)
Unexpected token. (near “NOME” at position 2844)
Unrecognized keyword. (near “AS” at position 2849)
Unexpected token. (near “CATEGORIA” at position 2852)
Unexpected token. (near “,” at position 2861)
Unexpected token. (near “G” at position 2863)
Unexpected token. (near “.” at position 2864)
Unexpected token. (near “NOME” at position 2865)
Unrecognized keyword. (near “AS” at position 2870)
Unexpected token. (near “SUBCATEGORIA” at position 2873)
Unexpected token. (near “,” at position 2885)
Unexpected token. (near “P” at position 2887)
Unexpected token. (near “.” at position 2888)
Unexpected token. (near “NOME” at position 2889)
Unrecognized keyword. (near “AS” at position 2894)
Unexpected token. (near “TRANSPORTADORA” at position 2897)
Unexpected token. (near “,” at position 2911)
Unexpected token. (near “(” at position 2914)
This type of clause was previously parsed. (near “SELECT” at position 2915)
Unexpected token. (near “)” at position 2995)
Unrecognized keyword. (near “AS” at position 2997)
Unexpected token. (near “PARCELAS” at position 3000)
This type of clause was previously parsed. (near “FROM” at position 3014)
Unrecognized statement type. (near “FROM” at position 3014)

Como posso resolver isso?

Grato,

Ilano

InfinitusWeb · Abril 16, 2019, 1:23am

Porque montar mascaras no SQL?

O SC já faz isso para você.

azeredo · Julho 17, 2024, 12:56am

Eu criei uma materialized view para ser usada em aplicação de relatório, coloquei a query no SQL e o SC não está aceitando a view como uma tabela valida e nem relaciona entre as tabelas do sistema.
Alguém sabe porque?