Skip to Content
avatar image
Former Member

Unir dos consultas SQL en diferentes columnas

Hola como están, tengo dos consultas y quisiera unirlas para que se muestren en dos columnas diferentes, la base es hana, podrian ayudarme?

Consulta 1:

SELECT T2."TrnspName" as "Transportista", COUNT (T1."ItemCode" ) as "Lineas Fact", sum (case when T1."U_HORMAS" IS NULL OR T1."U_HORMAS"=0 Then T1."Quantity" else T1."U_HORMAS" END) AS "Bultos Fact" FROM OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OSHP T2 ON T0."TrnspCode" = T2."TrnspCode" WHERE T0."DocDate" between [%0] and [%1] GROUP BY T2."TrnspName"

Consulta 2:

SELECT T2."TrnspName" as "Transportista", -COUNT (T1."ItemCode" ) as "Lineas Nc", - sum (case when T1."U_HORMAS" IS NULL OR T1."U_HORMAS"=0 Then T1."Quantity" else T1."U_HORMAS" END) AS "Bultos Nc" FROM ORIN T0 INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OSHP T2 ON T0."TrnspCode" = T2."TrnspCode" WHERE T0."DocDate" between [%0] and [%1] AND T1."U_MOTIVO_DEVOLUCION"=9 GROUP BY T2."TrnspName"

La consulta final que quiero realizar es:

Transportista, Lineas Fact, Bultos Fact, Lineas Nc, Bultos Nc

Gracias

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Oct 11, 2017 at 04:15 PM

    Hola

    entre las dos sentencias SQL debes poner UNION ALL:

    Sentencia SQL1

    UNION ALL

    Sentencia SQL2

    Recuerda que el número de campos de ambas sentencias debe ser el mismo, el tipo de campo también, y los alias.

    Un saludo

    Agustín Marcos Cividanes

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 12, 2017 at 12:44 PM

    Gracias Agustin,

    Lo que sucede es que con union all me quedan tres columnas, y yo quiero que sean 5

    Con union all, aa columna Lineas Fact se suma con Lineas Nc y Bultos Fact se suma con Bultos Nc

    Quiero que aparezcan separadas Me explico?

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 17, 2017 at 03:09 PM

    Hola

    yo lo plantearía así:

    SELECT lainformaciónquenecesites FROM

    (

    SELECT loscamposdeconsulta FROM

    (tuprimeraconsulta S0 LEFT JOIN tusegundaconsulta S1 ON S0.transportista = S1.transportista)

    UNION ALL

    SELECT loscamposdeconsulta FROM

    (tuprimeraconsulta S2 RIGHT JOIN tusegundaconsulta S3 ON S2.transportista = S3.transportista)) R0

    group by R0.transportista

    Un saludo

    Agustín Marcos Cividanes

    Add comment
    10|10000 characters needed characters exceeded