I am using the following code to insert data into a Temp table in SQL:

SELECT bartt_code, bartt_code_description, mele_port_name, expiry_date, trade_price, trade_qty_in_lots, trade_qty, (trade_price*trade_qty) AS 'Price_x_Quantity'
INTO #Table1
FROM kst_exchange_trade
WHERE bartt_code IS NOT NULL
GROUP BY bartt_code, bartt_code_description, mele_port_name, expiry_date, trade_price, trade_qty_in_lots, trade_qty

I am running into an issue where trade_qty should be negative when trade_qty_in_lots is negative. How could I build this in to my query to make this change?

CodePudding user response：

If you only want to align the sign, you could always turn the trade_qty field into an expression, such as:

IIF (
    trade_qty_in_lots >= 0, 
            ABS(trade_qty), 
       -1 * ABS(trade_qty)
) AS trade_qty

Note, I've added in an ABS to cover all four cases:

• Both signs are positive
• Both signs are negative (not shown in question)
• trade_qty is positive, trade_qty_in_lots is negative
• trade_qty is negative, trade_qty_in_lots is positive (not shown in question)

CodePudding user response：

The sign() function will return a value that can be used to match negative/positive.

sign(trade_qty_in_lots) * abs(trade_qty)

If you can guarantee that trade_qty is always positive then naturally the absolute value is unnecessary and can be removed.

CodePudding user response：

SELECT bartt_code, bartt_code_description, mele_port_name, expiry_date, trade_price, trade_qty_in_lots, (trade_qty * (trade_qty_in_lots/ABS(trade_qty_in_lots))) trade_qty, (trade_price*trade_qty) AS 'Price_x_Quantity'
INTO #Table1
FROM kst_exchange_trade
WHERE bartt_code IS NOT NULL
GROUP BY bartt_code, bartt_code_description, mele_port_name, expiry_date, trade_price, trade_qty_in_lots, trade_qty

(trade_qty * (trade_qty_in_lots/ABS(trade_qty_in_lots))) trade_qty will multiply trade_qty by either 1 or -1, depending on if trade_qty_in_lots is positive or negative. The ABS will guarantee that the negatives don't divide into a positive.

However, this will not solve a divide by zero error.

You could also use a case statement:

select case when trade_qty_in_lots < 0 then trade_qty * -1 else trade_qty end trade_qty

That would get you around the divide by zero error.