Skip to content
Advertisement

How to exclude rows with null values in all columns in HANA?

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 JOINs to join the purchases TBLPURCHASES and sales TBLSALES. Using table variables is a good option, but the ORDER BYs 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;
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement