Join the results of two SQL commands in H2

Navigation
0

I created the following Query : 

SELECT s 
FROM SuprimentosPedidos s 
WHERE s.codigoModeloImpressora = :codigoModeloImpressora
AND s.codigoEmpresa = :codigoEmpresa and s.prioridadeSaida = 1 
ORDER BY s.prioridadeSaida, s.suprimento  ASC union
SELECT s 
FROM SuprimentosPedidos s 
WHERE s.codigoModeloImpressora = :codigoModeloImpressora
AND s.codigoEmpresa = :codigoEmpresa and s.prioridadeSaida = 4 and NOT EXISTS (SELECT s 
FROM SuprimentosPedidos s 
WHERE s.codigoModeloImpressora = :codigoModeloImpressora
AND s.codigoEmpresa = :codigoEmpresa and s.prioridadeSaida = 3)
ORDER BY s.prioridadeSaida, s.suprimento  ASC

In order to select everything you have as a priority priority and select priority 4 only when there is nothing priority 3 but it always returns me only what I select in my first Select , that is, if I put it that way, it returns only those of priority 1 if I reverse it I get the priority 4 returns only if there is nothing in the priority 3 bank. My need is to merge these two results, I tried to use Union but it did not work, would you have any idea how to do this?

Script Create table: 

    CREATE TABLE suprimentospedidos (
    capacidade character varying(255),
    codigomodeloimpressora integer,
    codigomodelosuprimento integer,
    codigotiposuprimento integer,
    descricao character varying(255),
    statussuprimento character varying(255),
    suprimento character varying(255),
    quantidadesuprimento integer,
    quantidadesuprimentoret integer,
    codigoempresa integer,
    prioridadesaida integer
);

Script Insert: 

insert into public.suprimentospedidos (codigomodeloimpressora, codigomodelosuprimento, suprimento, descricao, capacidade, codigotiposuprimento, statussuprimento, quantidadesuprimento, quantidadesuprimentoret, codigoempresa, prioridadesaida) values
(64,123,'SE260X22G','CILINDRO FOTOCONDUTOR E26X/E36X/E46X 30K (SERVICO)',30000,4,'Ativo',7,0,606,5),
(64,169,'E460X11B','TONER LEXMARK E460 15K',15000,1,'Ativo',17,1,606,2),
(64,172,'X463X11B','TONER LEXMARK X464 15K',15000,1,'Ativo',12,3,606,2),
(64,184,'SE460X11B','TONER LEX E460/X463/X466 15K',15000,2,'Ativo',16,0,606,1),
(64,195,'E260X22G','CILINDRO FOTOCONDUTOR E26X/E36X/E46X 30K',30000,3,'Ativo',2,0,606,6),
(66,123,'SE260X22G','CILINDRO FOTOCONDUTOR E26X/E36X/E46X 30K (SERVICO)',30000,4,'Ativo',7,0,606,5),
(66,169,'E460X11B','TONER LEXMARK E460 15K',15000,1,'Ativo',17,1,606,2),
(66,184,'SE460X11B','TONER LEX E460/X463/X466 15K',15000,2,'Ativo',16,0,606,1),
(66,195,'E260X22G','CILINDRO FOTOCONDUTOR E26X/E36X/E46X 30K',30000,3,'Ativo',2,0,606,6),
(82,155,'50F0Z00','CILINDRO FOTOCONDUTOR LEXMARK 50X 60K',60000,3,'Ativo',58,0,606,6),
(82,163,'60FBX00','TONER LEXMARK 60BX 20K',20000,1,'Ativo',7,2,606,2),
(82,164,'50FBX00','TONER LEXMARK 50BX 10K - MS610,410',10000,1,'Ativo',5,0,606,2),
(82,186,'S60FBX00','TONER LEXMARK 604X 20K',20000,2,'Ativo',17,9,606,1),
(82,193,'S60F4H00','TONER LEXMARK 604H 10K - MX310 / 410 / 511 / 611',10000,2,'Ativo',1,0,606,1),
(82,200,'60FBX0E','TONER LEXMARK 60BXE - SOMENTE SERVIÇOS',20000,1,'Ativo',14,0,606,2),
(83,155,'50F0Z00','CILINDRO FOTOCONDUTOR LEXMARK 50X 60K',60000,3,'Ativo',58,0,606,6),
(83,163,'60FBX00','TONER LEXMARK 60BX 20K',20000,1,'Ativo',7,2,606,2),
(83,164,'50FBX00','TONER LEXMARK 50BX 10K - MS610,410',10000,1,'Ativo',5,0,606,2),
(83,186,'S60FBX00','TONER LEXMARK 604X 20K',20000,2,'Ativo',17,9,606,1),
(83,193,'S60F4H00','TONER LEXMARK 604H 10K - MX310 / 410 / 511 / 611',10000,2,'Ativo',1,0,606,1),
(83,200,'60FBX0E','TONER LEXMARK 60BXE - SOMENTE SERVIÇOS',20000,1,'Ativo',14,0,606,2),
(78,68,'C53034X','KIT FOTOCONDUTOR LEXMARK C532/C534',20000,3,'Ativo',1,0,606,6),
(78,136,'C746H1KG','TONER LEXMARK C748 PRETO 10K',10000,5,'Ativo',4,0,606,4),
(78,137,'C748H1CG','TONER LEXMARK C748 CIANO 10K',10000,5,'Ativo',0,1,606,4),
(78,138,'C748H1MG','TONER LEXMARK C748 MAGENTA 10K',10000,5,'Ativo',3,3,606,4),
(78,139,'C748H1YG','TONER LEXMARK C748 AMARELO 10K',10000,5,'Ativo',0,1,606,4),
(78,140,'C734X77G','CAIXA DE RESIDUOS P/ TONER C736/C748/X738 25K',25000,9,'Ativo',8,0,606,7)

Simplified Query: 

SELECT s 
FROM SuprimentosPedidos s
WHERE s.codigoModeloImpressora = :codigoModeloImpressora
AND s.codigoEmpresa = :codigoEmpresa and s.prioridadeSaida = 6 union all
SELECT s 
FROM SuprimentosPedidos s
WHERE s.codigoModeloImpressora = :codigoModeloImpressora
AND s.codigoEmpresa = :codigoEmpresa and s.prioridadeSaida = 1
ORDER BY s.prioridadeSaida

Following the available documentation here in the union session

    
asked by anonymous 09.10.2017 / 13:31

1 answer

2

Separating the select's in parentheses works for both MySql and PostgreSQL.

After finding out that it is H2 Database and that the Union sytax does not work anyway I tried to get to the result in another way, using a temporary table:

Example with temporary table: 

CREATE table #suprimentospedidos (
    capacidade character varying(255),
    codigomodeloimpressora integer,
    codigomodelosuprimento integer,
    codigotiposuprimento integer,
    descricao character varying(255),
    statussuprimento character varying(255),
    suprimento character varying(255),
    quantidadesuprimento integer,
    quantidadesuprimentoret integer,
    codigoempresa integer,
    prioridadesaida integer
)

INSERT INTO #suprimentospedidos
SELECT s.*
FROM SuprimentosPedidos s 
WHERE s.codigoModeloImpressora = 83
  AND s.codigoEmpresa = 606
  AND s.prioridadeSaida = 1 
ORDER BY s.prioridadeSaida, s.suprimento  ASC

INSERT INTO #suprimentospedidos
SELECT s.* 
FROM SuprimentosPedidos s
WHERE s.codigoModeloImpressora = 83
  AND s.codigoEmpresa = 606 
  AND s.prioridadeSaida = 6
  AND NOT EXISTS (
    SELECT 1
    FROM SuprimentosPedidos s 
    WHERE s.codigoModeloImpressora = 83
    AND s.codigoEmpresa = 606
    AND s.prioridadeSaida = 3)
ORDER BY s.prioridadeSaida, s.suprimento  ASC

SELECT * FROM #suprimentospedidos s ORDER BY s.prioridadeSaida, s.suprimento  ASC

DROP TABLE #suprimentospedidos

Example with union separated by parentheses.

SQLFiddle 

(
SELECT s.*
FROM SuprimentosPedidos s 
WHERE s.codigoModeloImpressora = 1
  AND s.codigoEmpresa = 1 
  AND s.prioridadeSaida = 1 
ORDER BY s.prioridadeSaida, s.suprimento  ASC
)
UNION
(
SELECT s.* 
FROM SuprimentosPedidos s
WHERE s.codigoModeloImpressora = 1
  AND s.codigoEmpresa =1 
  AND s.prioridadeSaida = 4 
  AND NOT EXISTS (
    SELECT 1
    FROM SuprimentosPedidos s 
    WHERE s.codigoModeloImpressora = 1
    AND s.codigoEmpresa = 1
    AND s.prioridadeSaida = 3)
ORDER BY s.prioridadeSaida, s.suprimento  ASC
)
    
09.10.2017 / 14:24
More than one return on a method in PHP Error displaying array in PHP