Join 2 Oracle SELECTS and as a result 2 columns with different values

Question

Join 2 Oracle SELECTS and as a result 2 columns with different values

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I'm facing a problem, I have two sql selects

select *
from web.demonstrativo_processados 
where nroempresa = 1
  and data between to_date('2018/01/01' , 'yyyy/mm/dd')
  and to_date('2018/12/31' , 'yyyy/mm/dd')

That brings me the values of the year 2018 and I have this:

select*fromweb.demonstrativo_processadoswherenroempresa=1anddatabetweento_date('2017/01/01','yyyy/mm/dd')andto_date('2017/12/31','yyyy/mm/dd')

Whichreturnsthevaluesfor2017.

Iwouldlikethetwocolumnstocomeasananswer,soIneedtointegratethe2selects.

AsI'musingphpandgooglechartstogeneratecharts,itwouldmakethejobaloteasierforme.graphicIwanttodo

Thankyou

WHILEMYPHP

<html><head><scripttype="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script><scripttype="text/javascript">
      google.charts.load('current', {'packages':['corechart']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Mês', '2017', '2018'],


          <?php

            include ("./conexao_local.php");

            $sql = "select * from web.demonstrativo_processados where nroempresa = 1 and data between to_date('2017/01/01' , 'yyyy/mm/dd') and to_date('2017/12/31' , 'yyyy/mm/dd')";
            $stmt = oci_parse($conexao, $sql);

              oci_execute($stmt);

          while (($array = oci_fetch_array($stmt, OCI_BOTH)) != false) {
           $valorvenda = $array["VLRVENDA"];


           $valorvenda1 = str_replace(",",".", $valorvenda);

           ?>                       


          ['Mês 2017',  <?php echo $valorvenda1 ?>,400],
         <?php } ?>
        ]);

        var options = {
          title: 'Company Performance',
          hAxis: {title: 'Year',  titleTextStyle: {color: '#333'}},
          vAxis: {minValue: 0}
        };

        var chart = new google.visualization.AreaChart(document.getElementById('chart_div_1'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div_1" style="width: 100%; height: 500px;"></div>

  </body>
</html>

php oracle

asked by anonymous 26.10.2018 / 20:22

3 answers

How to do multiple Thread in a Java Loop V10 - AfterBusinessObject method gives error when executing in Executive

score 0 · Answer 1

Assuming you want to calculate the total for 2018 and the total for 2019, follow the code using a query for 2018 and another for 2017.

  SELECT (select SUM(valor)
           from web.demonstrativo_processados 
           where nroempresa = 1
           and data between to_date('2018/01/01' , 'yyyy/mm/dd')
           and to_date('2018/12/31' , 'yyyy/mm/dd')) as Total2018
          ,(select SUM(valor)
           from web.demonstrativo_processados 
           where nroempresa = 1
           and data between to_date('2017/01/01' , 'yyyy/mm/dd')
           and to_date('2017/12/31' , 'yyyy/mm/dd')) as Total2017

score 0 · Answer 2

Edit # 1

SELECT data, VLRVENDAS
FROM web.demonstrativo_processados 
WHERE nroempresa = 1 
AND data between to_date('2017/01/01' , 'yyyy/mm/dd') 
AND to_date('2018/12/31' , 'yyyy/mm/dd')

With UNION :

select * from (
(
select ANO = '2018', * 
from web.demonstrativo_processados 
where nroempresa = 1 
and data between to_date('2018/01/01' , 'yyyy/mm/dd') 
and to_date('2018/12/31' , 'yyyy/mm/dd')
)
union all
(
select ANO = '2017', * 
from web.demonstrativo_processados 
where nroempresa = 1 
and data between to_date('2017/01/01' , 'yyyy/mm/dd') 
and to_date('2017/12/31' , 'yyyy/mm/dd')
) as aliastab

With OR and YEAR :

select YEAR(data), * 
from web.demonstrativo_processados 
where nroempresa = 1 
and ( (data between to_date('2018/01/01' , 'yyyy/mm/dd') 
and to_date('2018/12/31' , 'yyyy/mm/dd')) 
OR (data between to_date('2017/01/01' , 'yyyy/mm/dd') 
and to_date('2017/12/31' , 'yyyy/mm/dd')) )

score 0 · Answer 3

I was able to do

select a.vlrvenda as venda2017, b.vlrvenda as venda2018
  from web.demonstrativo_processados a, web.demonstrativo_processados b
 where a.nroempresa = 1
   and a.data between to_date('2017/01/01', 'yyyy/mm/dd') and
       to_date('2017/12/31', 'yyyy/mm/dd')
   and a.nroempresa = b.nroempresa
   and b.data between to_date('2018/01/01', 'yyyy/mm/dd') and
       to_date('2018/12/31', 'yyyy/mm/dd')
   and to_char(trunc(a.data, 'MM'), 'MON') = to_char(trunc(b.data, 'MM'), 'MON')