I need to omit rows of following query, if there are no sales and Purchases for a date. I tried where Concat(Sales,Purchases) <> ” and Is not null functions. But it omit the rows even one column has a null value. How to do this. Here my query for your reference,
Do Begin Declare FromD Date; Declare ToD Date; FromD := '20200201'; ToD := '20200228'; TBLSALES = Select "OINV"."DocDate",Sum("OINV"."DocTotal") as "Sales" From "TNP_AGE_LIVE"."OINV" where "OINV"."DocDate" between :FromD and :ToD Group by "OINV"."DocDate" Order by "OINV"."DocDate"; TBLPURCHASE = Select "OPCH"."DocDate",Sum("OPCH"."DocTotal")as "Purchase" From "TNP_AGE_LIVE"."OPCH" where "OPCH"."DocDate" between :FromD and :ToD Group by "OPCH"."DocDate" Order by "OPCH"."DocDate"; SELECT to_date(GENERATED_PERIOD_START) as "Date",:TBLSALES."Sales",:TBLPURCHASE."Purchase" FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 1 DAY', :FromD, :ToD ) Left Join :TBLSALES on :TBLSALES."DocDate"=to_date(GENERATED_PERIOD_START) Left Join :TBLPURCHASE on :TBLPURCHASE."DocDate" = to_date(GENERATED_PERIOD_START) /*where concat(:TBLSALES."Sales",:TBLPURCHASE."Purchase")<>''*/; End
Advertisement
Answer
Ok, so you use the SERIES_GENERATE_TIMESTAMP
to get a list of all dates between your FromD
date and ToD
date. To avoid unnecessary data type conversion, one could use the SERIES_GENERATE_DATE
function instead.
Then you use LEFT OUTER JOIN
s to join the purchases TBLPURCHASES
and sales TBLSALES
. Using table variables is a good option, but the ORDER BY
s are not necessary for the remaining processing.
Whenever there is no match for a date in other of those tables, the columns that are projected from them (:TBLSALES."Sales"
and :TBLPURCHASE."Purchase"
) will be NULL
(not just empty string ''
).
Testing multiple columns for NULL
can easily be done with the COALESCE
function.
Taking this together, we can write the code like this:
do begin declare FromD Date := '20200201'; declare ToD Date := '20200228'; daily_sales = select "DocDate" as "SalesDate" , SUM("DocTotal") as "Sales_SUM" from "TNP_AGE_LIVE"."OINV" where "DocDate" between :FromD and :ToD group by "DocDate"; daily_purchases = select "DocDate" as "PurchaseDate" , Sum("DocTotal") as "Purchases_SUM" from "TNP_AGE_LIVE"."OPCH" where "DocDate" between :FromD and :ToD group by "DocDate"; select sgd.GENERATED_PERIOD_START as "Date" , s."Sales_SUM" , p."Purchases_SUM" from SERIES_GENERATE_DATE('INTERVAL 1 DAY', :FromD, :ToD ) sgd left outer join :daily_sales s on s."SalesDate" = sgd.GENERATED_PERIOD_START left Join :daily_purchases p on p."PurchasesDate" = sgd.GENERATED_PERIOD_START where coalesce(p."SalesDate", p."PurchasesDate") IS NOT NULL; end;