Query create decimal with division Teradata
I am getting an error "Corresponding select-list expressions are incompatible.
Can anyone tel by looking at this?
Can anyone tel by looking at this?
CASE
WHEN CAST(ZEROIFNULL(bill.shp_disc_amt + bill.bill_drop_off + bill.bill_to_natl_disc + bill.bill_to_mem_disc) * bill.exch_usd * -1 AS DECIMAL(12,2)) = 0 THEN 0
ELSE CAST( CAST(ZEROIFNULL(bill.shp_disc_amt + bill.bill_drop_off + bill.bill_to_natl_disc + bill.bill_to_mem_disc) * bill.exch_usd * -1 AS DECIMAL(12,2)) /CAST(ZEROIFNULL(bill.shp_fgt_chrg_amt * bill.exch_usd) AS DECIMAL(12,2)) as DECIMAL(12,2))
END AS Effective_USD_Discount,ASKER
lcohan - the query is very large and worked until I added this line.
Do you have any suggestions?
Do you have any suggestions?
As mentioned above - one of the CASE values is not valid as they are not of the expected type and you must check that against each and any of those underlying columns to make sure the all have data converging to you DECIMAL(12,2) desired cast data-type.
ASKER
lcohan - ah! Ok right...I will try that, great suggestion
So you will need to try validate the fact that each of those columns have numeric data and that data aggregated as per your query can fit the CAST-ed value
ASKER
Icohan, I broke it down to sections and am working through. I had success running the first instance, but this returns only 1.00 or 0.00, not the .63, .55, etc that I was expecting.
Can you by chance tell where I am going wrong?
Can you by chance tell where I am going wrong?
CASE
WHEN CAST(ZEROIFNULL(bill.shp_disc_amt + bill.bill_drop_off + bill.bill_to_natl_disc + bill.bill_to_mem_disc) * bill.exch_usd * -1 AS DECIMAL(12,2)) = 0 THEN 0
ELSE CAST((CAST(ZEROIFNULL(bill.shp_disc_amt + bill.bill_drop_off + bill.bill_to_natl_disc + bill.bill_to_mem_disc) * bill.exch_usd * -1 AS DECIMAL(12,2)) /CAST(ZEROIFNULL(bill.shp_disc_amt + bill.bill_drop_off + bill.bill_to_natl_disc + bill.bill_to_mem_disc) * bill.exch_usd * -1 AS DECIMAL(12,2))) as decimal(12,2))
END AS Effective_Discount,ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found it by using your guidance. Thank you!!
If you are 100000% certain that's where the error is then for sure one of the CASE values
is not valid as they are not of the expected type and may need to get CAST-ed. or maybe is a divide by 0 error but I almost bet is in one of the columns used in additions.