CODEWITHSUNDEEP
sqlsql-server
Talib
Talib

Reputation: 1164

Insert NULL if query returns nothing in sql server

I am trying to INSERT few values plus NULL if SELECT statement returns nothing. I have tried if exists but it seems not to be working. Please help me find the best solution.

Below is my query:

 INSERT INTO 
         gstl_calculated_daily_fee(business_date,fee_type,fee_total,range_id) 
     select
    @tlf_business_date,
    'SWITCH_FEE_LOCAL_CARD',
     SUM(C.settlement_fees),
     C.range_id
From
(
    select
        *
    from
        (
            -- set a row number or transaction number per transaction which resets every month
            select
                rowNumber = @previous_mada_switch_fee_volume_based_count + (ROW_NUMBER() OVER(PARTITION BY DATEPART(MONTH, x_datetime) ORDER BY x_datetime)),
                tt.x_datetime
            from gstl_trans_temp tt where (message_type_mapping = 0220) and card_type ='GEIDP1' and response_code IN(00,10,11)  and tran_amount_req >= 5000 AND merchant_type NOT IN(5542,5541,4829)
         
        ) A
        -- cross apply to calculate which range for each transaction
        CROSS APPLY
            (
                select
                     rtt.settlement_fees,
                     rtt.range_id
                     From gstl_mada_local_switch_fee_volume_based rtt
                    where A.rowNumber >= rtt.range_start
                        AND (A.rowNumber <= rtt.range_end OR rtt.range_end IS NULL)
            ) B 
) C
-- group by date to get the per date fees
group by CAST(C.x_datetime AS DATE),C.range_id

I want to insert below results if the select statement returns nothing.

  INSERT INTO 
         gstl_calculated_daily_fee(@tlf_business_date,'SWITCH_FEE_LOCAL_CARD',NULL, NULL)

Upvotes: 1

Views: 173

Answers (2)

Thom A
Thom A

Reputation: 96016

You could just check the value of @@ROWCOUNT and if it's 0, then perform the INSERT of the "default" values:

INSERT INTO gstl_calculated_daily_fee (business_date,
                                       fee_type,
                                       fee_total,
                                       range_id)
SELECT @tlf_business_date,
       'SWITCH_FEE_LOCAL_CARD',
       SUM(C.settlement_fees),
       C.range_id
FROM (SELECT *
      FROM (
           -- set a row number or transaction number per transaction which resets every month
           SELECT @previous_mada_switch_fee_volume_based_count + (ROW_NUMBER() OVER (PARTITION BY DATEPART(MONTH, x_datetime)ORDER BY x_datetime)) AS rowNumber,
                  tt.x_datetime
           FROM gstl_trans_temp tt
           WHERE (message_type_mapping = 0220)
             AND card_type = 'GEIDP1'
             AND response_code IN (00, 10, 11)
             AND tran_amount_req >= 5000
             AND merchant_type NOT IN (5542, 5541, 4829)) A
           -- cross apply to calculate which range for each transaction
           CROSS APPLY (SELECT rtt.settlement_fees,
                               rtt.range_id
                        FROM gstl_mada_local_switch_fee_volume_based rtt
                        WHERE A.rowNumber >= rtt.range_start
                          AND (A.rowNumber <= rtt.range_end
                            OR rtt.range_end IS NULL)) B ) C
-- group by date to get the per date fees
GROUP BY CAST(C.x_datetime AS date), --Seems this is in the GROUP BY and not the SELECT. Intentional?
         C.range_id;

IF @@ROWCOUNT = 0
    INSERT INTO gstl_calculated_daily_fee (business_date,
                                           fee_type,
                                           fee_total,
                                           range_id)
    VALUES (@tlf_business_date, 'SWITCH_FEE_LOCAL_CARD', NULL, NULL);

Upvotes: 1

Charlieface
Charlieface

Reputation: 72480

You need an empty driving row to left join against. Use VALUES for this.

I have removed the select * as it was pointless

 INSERT INTO 
         gstl_calculated_daily_fee(business_date,fee_type,fee_total,range_id) 
SELECT @tlf_business_date, 'SWITCH_FEE_LOCAL_CARD', C.settlement_fees, C.range_id
FROM (VALUES (1) ) AS v (dummy)
LEFT JOIN
(
    select
         SUM(C.settlement_fees) AS settlement_fees,
         C.range_id
    from
        (
            -- set a row number or transaction number per transaction which resets every month
            select
                rowNumber = @previous_mada_switch_fee_volume_based_count + (ROW_NUMBER() OVER(PARTITION BY DATEPART(MONTH, x_datetime) ORDER BY x_datetime)),
                tt.x_datetime
            from gstl_trans_temp tt where (message_type_mapping = 0220) and card_type ='GEIDP1' and response_code IN(00,10,11)  and tran_amount_req >= 5000 AND merchant_type NOT IN(5542,5541,4829)
         
        ) A
        -- cross apply to calculate which range for each transaction
        CROSS APPLY
            (
                select
                     rtt.settlement_fees,
                     rtt.range_id
                     From gstl_mada_local_switch_fee_volume_based rtt
                    where A.rowNumber >= rtt.range_start
                        AND (A.rowNumber <= rtt.range_end OR rtt.range_end IS NULL)
            ) B 
    group by CAST(A.x_datetime AS DATE),B.range_id
) C
-- group by date to get the per date fees

Upvotes: 1

Related Questions