Doubt to mount Query with values that are repeated

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 01 Mar 2006 9:24 pm

Guys,: wink: Hi, I have the following Query, where I'm having a little problem:
Code: Select all
select DU7.DU7_FILDOC FILIAL,
       DU7.DU7_DOC CTRC,
       TO_DATE(DT6.DT6_DATEMI,'YYYYMMDD') EMISSAO,
       DTC.DTC_NUMNFC NF,
       DTC.DTC_VALOR VR_MERC,
       (select Z04.Z04_VLRCTN from Z04010 Z04
                where DTC.DTC_CODEMB = Z04.Z04_CODEMB
                  AND Z04.Z04_FILIAL= '  '
                  AND Z04.D_E_L_E_T_ = ' ') VR_CTN
  from DU7010 DU7,
       DT6010 DT6,
       DTC010 DTC
where DU7.DU7_FILIAL = DT6.DT6_FILIAL
   AND DU7.DU7_FILDOC = DT6.DT6_FILDOC
   AND DU7.DU7_DOC    = DT6.DT6_DOC
   AND DU7.DU7_SERIE  = DT6.DT6_SERIE
   AND DT6.DT6_FILIAL = DTC.DTC_FILIAL
   AND DT6.DT6_FILORI = DTC.DTC_FILORI
   AND DT6.DT6_LOTNFC = DTC.DTC_LOTNFC
   AND DU7.D_E_L_E_T_ = '  '
   AND DT6.D_E_L_E_T_ = '  '
   AND DTC.D_E_L_E_T_ = '  '
   AND DT6.DT6_FILIAL = '  '
   AND DU7.DU7_FILIAL = '  '
   AND DTC.DTC_FILIAL = '  '   
   AND DT6.DT6_DOC = '150699'
   AND DT6.DT6_DATEMI >= '20060201' AND DT6.DT6_DATEMI <='20060228'
   AND DU7_COMSEG='01' --01=RCTR-C 02=RCF-DC
   AND DU7_CLIAVB<>'      ' --seguro cliente
   ORDER BY DU7_DOC
when I run she brings the following data:
Code: Select all
FILIAL   CTRC   EMISSAO   NF   VR_MERC   VR_CTN
54   150699   22/2/2006   1735   10212,51   12000
54   150699   22/2/2006   1736   9915,78   12000
54   150699   22/2/2006   1737   2259,97   12000
54   150699   22/2/2006   1738   3085,23   12000
54   150699   22/2/2006   1739   2782,92   12000
54   150699   22/2/2006   1740   1775,77   12000
54   150699   22/2/2006   1741   1242,47   12000
54   150699   22/2/2006   1742   1048,58   12000
54   150699   22/2/2006   1743   1603,92   12000
54   150699   22/2/2006   1744   20917,32   12000
54   150699   22/2/2006   1745   4398,11   12000
54   150699   22/2/2006   1746   34509,24   12000
the problem is that the column CTRC has 12 invoices with values of different Merchandise, but the VR_MERC is 12000 to the CTRC 150699.
I would like to bring the 12000 only the first NF, or last NF, but I can't, because the value is unique to the 12 invoices that 150699 CTRC.
Detail, there are documents that do not have this value, and why did a subselect refers to Z04010, because the table has no, returns as white.

Does anyone know how to do this?
Thank You.
Fernanda.
fefisiqueira
Location: Guarulhos - SP

Poston Tue, 16 Oct 2012 10:59 am

Uses " group by group function " in the fields that you want to join as for example in the case of the CTRC. But as you have other value fields and date among others will have to declare in the group by function or function value MAX (date), MAX (value), SUM (value) that to all the columns you want to display in your query.
heraldoaraujo

Poston Tue, 16 Oct 2012 11:25 am

Brother, Maybe if you also use the Oracle analytic functions.

But if that need, I made a sample here only so that other foristas can help us:
Code: Select all

create table teste (filial number(07), ctrc number(07), emissao date, nf varchar2(10),
                    vr_merc number(10,2), vr_ctn number (10,2)  );
                   

insert into teste values (54, 150699,   '22/2/2006',   '1735',   10212.51,  12000);
insert into teste values (54, 150699,   '22/2/2006',   '1736',   9915.78,   12000);
insert into teste values (54, 150699,   '22/2/2006',   '1737',   2259.97,   12000);
insert into teste values (54, 150699,   '22/2/2006',   '1738',   3085.23,   12000);
insert into teste values (54, 150699,   '22/2/2006',   '1739',   2782.92,   12000);
insert into teste values (54, 150699,   '22/2/2006',   '1740',   1775.77,   12000);

insert into teste values (54, 150700,   '22/2/2006',   '1741',   1775.77,   11000);
insert into teste values (54, 150700,   '22/2/2006',   '1742',   1776.77,   11000);
insert into teste values (54, 150700,   '22/2/2006',   '1743',   1777.77,   11000);
insert into teste values (54, 150700,   '22/2/2006',   '1744',   1778.77,   11000);

insert into teste values (54, 150701,   '22/2/2006',   '1745',   1775.77,   1000);
insert into teste values (54, 150701,   '22/2/2006',   '1746',   1776.77,   1000);
insert into teste values (54, 150701,   '22/2/2006',   '1747',   1777.77,   1000);
insert into teste values (54, 150701,   '22/2/2006',   '1748',   1778.77,   1000);


select t2.filial,
       t2.ctrc,
       t2.emissao,
       t2.nf,
       t2.vr_merc,
       (select t1.vr_ctn
          from teste t1
         where t1.ctrc = t2.ctrc
           and t1.nf = t2.nf
           and t1.nf = (select min(t3.nf)
                          from teste t3
                         where t3.ctrc = t2.ctrc))
  from teste t2;
anything, send it to us.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Fri, 14 Jun 2013 9:19 am

heraldoaraujo, you can obtain the result that you are looking for through the LISTAGG analytic function (only 11 g).

[] s
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 3 guests