Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- with
- dados_geral as (
- select
- idfilial,
- idfilialsaldo,
- filialfantasia,
- filialsaldofantasia,
- idgrupopreco,
- idcodigonbm,
- idcodigonbm,
- idproduto,
- idgradex,
- gradex,
- idgradey,
- gradey,
- iddepartamento,
- descricao,
- codigofabricante,
- customedio,
- coalesce(idpromocao, 0) as idpromocao,
- precovenda,
- precosistema,
- percentualdesconto,
- idmarca,
- marca,
- lpad(cast(idproduto as varchar),6,'0') || lpad(cast(idgradex as varchar),4,'0') || '01180.jpg' as imagem,
- idmenu,
- idsubmenu,
- idfiltro,
- menu,
- submenu,
- filtro,
- thumbs,
- datahoramovimentothumbs,
- prazofinal,
- bnds as bndes,
- rank() over (partition by idproduto, idgradex order by rank)
- from
- b2b.pwsearch_cache(:uf, :search, :idmenu, :idsubmenu, :idfiltro, :idmarca, :idproduto, :idgradex , :idtipocontribuinte, :idtipopessoa, :tipoinfo, 75, :idcnpj_cpf, :idpromocao)
- --b2b.pwsearch_cache('PR', '', 0,0,0, 0,0,1,2)
- where
- 1=1
- and idproduto not in (920019)
- -- order by está sendo feito dentro da pwsearch
- ),
- marcas as (
- select '['||string_agg(row_to_json(tb)::varchar,',')||']' as marcas
- from (
- select
- distinct on (1,2)
- idmarca,
- marca
- from dados_geral
- ) tb
- ),
- filiais as (
- select '['||string_agg(row_to_json(tb)::varchar,',')||']' as filiais
- from (
- select
- distinct on (1,2)
- idfilial,
- idfilialsaldo,
- filialfantasia,
- filialsaldofantasia,
- idfilial::varchar||idfilialsaldo::varchar as keyfilial
- from dados_geral
- ) tb
- ),
- menus as (
- select '['||string_agg(row_to_json(tb)::varchar,',')||']' as menus
- from (
- select
- distinct on (1,2)
- idmenu,
- menu
- from dados_geral
- ) tb
- ),
- submenus as (
- select '['||string_agg(row_to_json(tb)::varchar,',')||']' as submenus
- from (
- select
- distinct on (1,2)
- idsubmenu,
- submenu
- from dados_geral
- ) tb
- ),
- dados as (
- select
- distinct on (idproduto, idgradex)
- *
- from dados_geral dg
- join marcas m on true
- join filiais f on true
- join menus me on true
- join submenus sm on true
- where
- 1=1
- --and dg.idfilial = 10222
- ),
- count_dados as (
- select
- d.*,
- count (d.*) over () as totalderegistros
- from dados d
- )
- select
- cd.*
- from count_dados cd
- order by cd.precovenda asc
- offset 0 LIMIT 16
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement