0

hope I can explain myself right in this question.

I have three (3) tables from which I have to build a report, this is the data selection process I need to do in order to correctly output the data to the report:

  1. Need to select data from three (3) different tables.
  2. The selected data must match a data range that is input prior reporting.
  3. *Now comes the 'tricky' part (for me):

    a. The first table contains two (2) columns, when column Table1.A is '0' Table1.B is '>0' and vice versa.

    b. The second table contains a column Table2.C which value is '1' or '2' but only when column Table1.B is '>0'.

In the report I need to output:

  1. string_a if Table1.B = 0
  2. string_b if Table2.C = 1

  3. string_c if Table2.C = 2

This is my code, but the CASE only works with Table1 data.

SELECT "SAFACT"."FechaT", "SAFACT"."FechaE", 
       "SAFACT"."NumeroD", "SAFACT"."ID3", 
       "SAFACT"."Descrip", "SAFACT"."Monto", 
       "SAFACT"."MtoTax", "SAFACT"."MtoTotal", 
       "SAFACT"."CancelE", "SAFACT"."CancelT", 
       "SAIPAVTA"."CodPago", "SAFACT"."Credito",
       CASE
            WHEN "SAFACT"."CancelT" = 0 THEN 'Efectivo' ELSE
            CASE
                  WHEN "SAIPAVTA"."CodPago" = 2 THEN 'Transferencia' ELSE 'Tarjeta'
            END
       END AS 'efe_met'
FROM "dbo"."SAFACT" "SAFACT"
      FULL OUTER JOIN "dbo"."SACLIE" "SACLIE" ON 
     ("SACLIE"."CodClie" = "SAFACT"."CodClie")
      LEFT OUTER JOIN "dbo"."SAIPAVTA" "SAIPAVTA" ON 
     ("SAIPAVTA"."NumeroD" = "SAFACT"."NumeroD")
      AND ("SAIPAVTA"."NroUnico" = "SAFACT"."NroUnico")
WHERE 
      ( "SAFACT"."FechaE" >= CONVERT(DATETIME,:FECHAED+' 00:00:00',120) )
       AND ( "SAFACT"."FechaE" <= CONVERT(DATETIME,:FECHAEH+' 23:59:59',120) )
ORDER BY "SAFACT"."FechaT", "SAFACT"."NumeroD"

When I use INNER JOIN to select data FROM tables, no data is selected, so I don't know what the problem is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.