/* TRATAMENTO UTILZADO NO CONTROLE DE FILIAIS QUANDO IMPORTA OS CADASTROS PRO

BANCO NOVO */

/* PODE SER QUE PRECISE USAR NAS TABELAS PRODUTOS, FORNECEDORES, GRUPOS E
SUBGRUPOS, ALÉM DE CLIENTES */

obs: utilizei este select :

update fornecedores x set [Link] = replace(substring([Link] from 1 for
27),'*','')||''||' (FILIAL 1)'
WHERE [Link] like '*%'

fez toda alteração que eu precisava.

update clientes x set [Link] = substring([Link] from 1 for 27)||''||' (FILIAL 1)'
WHERE [Link] like '*%'

UPDATE TABELA
SET COLUNA = REPLACE(REPLACE(COLUNA, '//', '/'), 'http:/', '[Link]

update clientes d set [Link] = replace([Link], '*','');

select * fRom clientes p

where [Link] like '%(FILIAL 1)'

update clientes x set [Link] = replace([Link],'*',''), [Link] = '

(FILIAL 1)', [Link] = ' (FILIAL 1)' /*PODE SER ESTE AQUI */
WHERE [Link] like '*%'

_______________________________________________________________
RESTAURAR BACKUP NO POSTGRES

psql -U "postgres" -d "dump" -1 -f "C:\Dados\Origem\[Link]"

exportar dados de uma tabela do postgres para o excel

COPY
(
SELECT [Link] AS coodigo,
[Link] AS codbarras,
[Link] AS descricao
from produto p
WHERE [Link] <>''
)
TO 'C:/temp/[Link]'
with DELIMITER ';'
CSV HEADER

____________________________________________________
select count([Link]), from cli t
group by [Link]
Todos que forem >= 2 no count estão duplicados…

SELECT coluna, Count(*) FROM nome_tabela

GROUP BY DCB
HAVING Count(*) > 1

________________________________________________
select
[Link],
count ([Link])
from estoque e
group by [Link]
having count([Link]) > 1

VERIFIQUEI CÓDIGOS DE PRODUTOS DUPLICADOS

____________________________________________________
CONTAR REGISTROS
SELECT COUNT(id ou codigo)from <table_name>
________________________________________________
OUTRO USADO NO MYSQL

SELECT codigo, Count(*) FROM clientes

GROUP BY codigo
HAVING Count(*) > 1
________________________________________________
/*SELECT cod, nom, count(*) as quantidade
FROM est
GROUP BY cod, nom */
/*HAVING count(*) > 1;*/

select count([Link]), from est e

group by [Link]

select COD, NOM, count(*)

from EST
group by COD, NOM
having count(*) >1

select COD, count(*)

from EST
group by COD
having count(*) >1

select * from est where cod= '003234'

_______________________________________________________

UPDATE est
SET COD='013186'
WHERE NOM='BRONZINA BIELA ACL 0,50 GOL 1.5/1.8'

_______________________________________________________

Select

Sum (valor) from crduplicatas

USEI NUM BANCO MYSQL:

SELECT [Link], SUM([Link]) AS TOTAL

FROM treceber T
GROUP BY [Link]

_________________________________

Sintaxe de LEFT JOIN

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
____________________________________________________________
UPDATE
produtos
SET
UNIDADE = 'UN'
WHERE
UNIDADE IS NULL

________________________________________________________________
TRATAMENTO EXCEL

select
cast (p.a as integer) CODIGO,
substring(p.b from 1 for 49) DESCRICAO,
cast( replace(p.c,',','.') as numeric(15,3)) PRECOCUSTO,
cast( replace(p.d,',','.') as numeric(15,3)) PRECOVENDA,
cast( replace(p.e,',','.') as numeric(15,3)) QUANTIDADE,
p.f UNIDADE,
p.g CEST,
p.h CODIGONCM

from prod1 p
___________________________________________________________________________________
_____________

LPAD SQL

QUANDO VC QUER ACRESCENTAR UM VALOR QUE NESTE CASO SERIA DO LADO ESQUERDO DO
PRODUTO
EX:

SELCECT
[Link],
LPAD([Link], 7, '0') AS CEST

FROM PRODUTO P

NESTE CASO, EU DISSE QUE QUERIA 7 O TAMANHO E QUE ESTIVESSE MENOR, ACRESCENTE 0 A
ESQUERDA.

___________________________________________________________________________________
______________

CRIAR COLUNA VRTUAL

select
[Link],
[Link],
'teste' as newfield

FROM clientes C
___________________________________________________________________________________
__________
UPDATE COM SELECT
update contasapagar ca
set [Link] = 'N' where [Link] in (select [Link]
from contasapagarbaixado cb where [Link] between :data and :data2)

__________________________________________________________________________________
function SoNumeros(Const Texto:String):String;
//
// Remove caracteres de uma string deixando apenas numeros
//
var
I: integer;
S: string;
begin
S := '';
for I := 1 To Length(Texto) Do
begin
if (Texto[I] in ['0'..'9']) then
begin
S := S + Copy(Texto, I, 1);
end;
end;
result := S;
end;

Assim você pode evitar erros ao converter ou manipular dados, além de poder alertar
o usuário.

Marcos Elias
Códigos fornecidos por Mep Sites
[Link]
______-
___________________________________________________________________________________
___

SELECT CAST(SEUCAMPODATAHORA AS DATE) FROM SUATABELA;

_________________________________________________________________

select
[Link], substring([Link] from 1 for 5) as show
from clientes c */
___________________________________________________________
SELECT
CAST([Link] AS INTEGER) AS CODIGO,
[Link],
[Link],
[Link],
SUBSTRING([Link] from 1 for 16) AS COMPLEMENTO,
[Link],
[Link],
[Link],
[Link],
[Link],
SUBSTRING([Link] FROM 1 FOR 70) AS OBSERVACAO,
c.cnpj_cpf,
c.incricaoestadual_rg,
('RG: '||[Link]) as FANTASIA,
[Link]
FROM cricri C

___________________________________________________________________________________
_________________________

SELECT
CAST([Link] AS INTEGER) AS CODIGO,
[Link],
'CNH '|| [Link]||' CAT: ' ||[Link] as FANTASIA,
[Link], SUBSTRING([Link] FROM 1 for 39) AS ENDERECO,
[Link], [Link], cdc.cnpj_cpf,
REPLACE(REPLACE(cdc.inscricaoestadual_rg, '.',''), '-','') AS
INCRICAOESTADUAL_RG,
SUBSTRING([Link] FROM 1 FOR 6) AS ORGAOEXPEDIDORRG,
SUBSTRING([Link] FROM 1 FOR 13) AS TELEFONE,
SUBSTRING([Link] FROM 1 FOR 13 ) AS TELTRABALHO, [Link],
[Link],
[Link], [Link],
[Link], [Link], [Link], [Link], [Link], [Link]

FROM CLI CDC

_________________________________________________________________

REPLACE(SUBSTRING([Link] FROM 1 FOR 14), '-','') AS TEL_COM,

____________________________________________________________________
verifiquei se tem codbarras duplicados

select codigo, codbarras, descricao, quantidade

from produtos
where codbarras IN (
select codbarras from produtos
 group by codbarras
having
count(codbarras) > 1
)

____________________________________________________________________

select
[Link], '*' || [Link]

from produtos f

_________________________________________________________________________
extrair caracter

select
[Link],
case SUBSTRING([Link], 1, CHARINDEX(' ', [Link], CHARINDEX(' ', [Link])+1))
when ' ' then [Link]
else
SUBSTRING([Link], 1, CHARINDEX(' ', [Link], CHARINDEX(' ', [Link])+1))
end as NOVO_NOME
from Pessoa p

_______________________________________________________________

select [Link], [Link], [Link] from clientes c

where not exists (select * from crduplicatas cr
where [Link] = [Link])

UPDATE clientes c SET [Link] = 'S'

where not exists (select * from crduplicatas cr
where [Link] = [Link]

UPDATE clientes C SET [Link] = 'S'

where [Link] BETWEEN 38 AND 41
____________________________________________________________________________

select
[Link], [Link] , [Link]
from codbarraproduto ca

where character_length([Link]) >= 12 neste select, eu trouxe todos os

produtos que tem codigos de barras iguais ou maiores que 12
____________________________________________________________________________

SELECT
[Link],
SUBSTRING([Link] FROM 1 FOR 39), SUBSTRING([Link] FROM 1 FOR 40) AS FANTASIA,
iif ([Link] = [Link], 'F', 'J')AS PESSOA, --se o campo cpf possui um cpf,
coloquei 'F', se não, coloquei 'J' E RENOMEI CRIANDO COLUNA COM O NOME PESSOA
case
WHEN [Link] = ([Link]) then [Link]
else [Link]
end as cnpj_cpf,
case
WHEN [Link] = ([Link]) then [Link]
else [Link]
end as INSCRICAOESTADUAL_RG,
[Link], [Link] AS CLINUMEROEND,
SUBSTRING([Link] FROM 1 FOR 20) AS BAIRRO,
SUBSTRING([Link] FROM 1 FOR 19) AS COMPLEMENTO, [Link] AS CODCIDADE,
[Link],
SUBSTRING(REPLACE(REPLACE([Link],'(',''),')','') FROM 1 FOR 13) AS TELEFONE,
SUBSTRING(REPLACE(REPLACE([Link],'(',''),')','') FROM 1 FOR 13) AS CELULAR,
SUBSTRING([Link] FROM 1 FOR 40) AS EMAIL

FROM CLIENTE C

___________________________________________________________________________________
___________

Como listar colunas de uma tabela em um Banco Firebird

SELECT RDB$RELATION_NAME FROM RDB$RELATION_FIELDS

WHERE RDB$FIELD_NAME=:NOME_CAMPO;

___________________________________________________________________________________
___________

BUSQUEI OS MAIS VENDIDOS

select
[Link],
[Link],
sum([Link])
from
(
Select
[Link],
[Link],
sum([Link]) quantidade
from
faturamentos f
inner join faturamentositens fi on [Link] = [Link]
where
[Link] = 1
group by
1,2
 union all

select
[Link],
[Link],
sum([Link]) quantidade
from
talaomanual t
inner join talaomanualitens ti on [Link] = [Link]
where
[Link] = 'N'
and [Link] = 'S'
group by
1,2
) v
group by
1,2

____________________________________________________________

ACERTAR ESTOQUE

-- UPDATE TALÃO MANUAL

UPDATE
produtos p
SET
[Link] = ([Link] -

coalesce((
select
Sum(coalesce([Link],0))
from
faturamentositens fi
inner join faturamentos f on [Link] = [Link]
where
[Link] = 1
and [Link] = [Link]
and [Link] <= 39093
),0))

-- UPDATE NF-E / NFC-E

UPDATE
produtos p
SET
[Link] = ([Link] -

coalesce((
select
Sum(coalesce([Link],0))
from
talaomanualitens ti
left join talaomanual t on [Link] = [Link]
where
[Link] = 'N'
and [Link] = 'S'
and [Link] = [Link]
and [Link] <= 39093
 ),0))

___________________________________________________________________________________
____

select
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]

from

(
select
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]

from faturamentos f
left join faturamentositens fi on [Link] = [Link]
where [Link] = 1 and [Link] = 'S'
union all
select
[Link],
[Link],
[Link],
[Link],
[Link],
[Link] dtemissao,
[Link]

from talaomanual t
left join talaomanualitens ti on [Link] = [Link]
where [Link] = 'N' and [Link]= 'S'
) c
left join usuarios u on [Link] = [Link]
where [Link] >='01.05.2022' and [Link] <='25.08.2022'

___________________________________________________________________________________
____________

select
[Link], [Link], [Link], convert(varchar, [Link], 104 ) as
dtvenda, convert(varchar,(DATEADD(day, 30,[Link])),104),

[Link],[Link], [Link], [Link], [Link]

from VendasCrediario vc
left join PessoaCliente pc on [Link] = [Link]
left join pessoa p on [Link] = [Link]
where [Link] > 0 and [Link] = 'F777A3DA3E'
order by dtvenda

Formatei a data data do jeito que o sistema trabalha (22.05.2022):

convert(varchar, [Link], 104 ) as dtvenda

Aqui, além de formatar, coloquei a data de vencimento do crediário pra 30 dias

depois da data da venda:

convert(varchar,(DATEADD(day, 30,[Link])),104)
___________________________________________________________________________________
______________________________

FORMATEI O CAMPO PRECOVENDA, CUSTO E QUANTIDADE.

select
[Link],
left(trim([Link]),50) as descricao,
CAST(lEFT(tRIM([Link]),14) AS VARCHAR (14)) AS CODBARRAS,
CAST([Link] AS dinheiro4casas) as PRECOCUSTO,
CAST([Link] AS dinheiro4casas ) AS PRECOVENDA,
CAST([Link] AS dinheiro4casas) AS QUANTIDADE,
[Link]

FROM PRODUTOS PN

2238521955
22992125306 ricardo santa tereza