Merge the results of two SQL’s commands into H2

Asked

Viewed 82 times

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

With the intention of selecting from the bank everything as priority 1 and select what you have priority 4 only when there is nothing with priority 3 in that bank, but it always returns to me only what I select in my first Select, that is if I put so returns me only those of priority 1 if I invert it returns me those of priority 4 only if there is nothing in the bank of priority 3. My need is to unite these two results, I tried to use the Union but it didn’t work, would have any idea how to do that?

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 session of union

sql select h2database

  • When you rotated the second select isolated it returned something?

  • @Caiqueromero, yes if I run any of the separate select’s works

  • Sure I made some changes to the query and created a schema to test, it worked correctly. If my answer doesn’t solve your problem, you’ll need to post create table and Insert’s to be more accurate.

  • The Company code column does not exist in your table.

  • @Caiqueromero sorry this table is already in production today, just a moment I change the question with the correct information

1 answer

2

Separating select’s between parentheses works for Mysql and Postgresql.

After discovering that it is H2 Database and that the Union sytax does not work the same I tried to get 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 parentheses-separated.

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
)

  • I tried to add the parentheses in Query but when I tried to save the changes made in Query returned that there was an error. I’ll change my question

  • Which error? you saw the example sent link?

  • I edited my question with the Create Table and Inserts scripts

  • I changed the question again now with the correct information, I apologize for the mistake

  • Caique austei the Insert now, is to be correct now

  • Right, you can tell what are the values that come in the variables :code ?

  • :codigoModeloImpressora = 83 and :codigoEmpresa = 606

  • I tried to adjust the query to make it simpler but even so it keeps returning me only the result of the first select, I will change my question for you to have an idea

  • The result depends on the data you have, on the Insert you showed There are no priority values 4 for model and company 83, 606 respectively

  • Let’s go continue this discussion in chat.

Show 5 more comments

Browser other questions tagged sql select h2database

You are not signed in. Login or sign up in order to post.