none
mssql query about case when

    Allgemeine Diskussion

  • Hello,

    i have a query like this;

    SELECT        order_product.order_product_id, order_product.order_id, order_product.product_id, order_product.name, order_product.model, order_product.quantity, 
                             order_product.price, order_product.total, order_product.tax, product.tax_class_id AS TaxClass_ID, tax_rule.tax_rate_id, tax_rate.rate, case when tax_rate.rate = 10 then order_product.tax as tax1
    FROM            order_product, product, tax_rule, tax_rate
    WHERE        (product.tax_class_id = tax_rule.tax_class_id) AND (order_product.product_id = product.product_id) AND (tax_rule.tax_rate_id = tax_rate.tax_rate_id)
    GROUP BY order_product_id
    ORDER BY order_id DESC

    i have 2 different tax  rate in tax_rate.rate column , i want to check if tax rate is 10 than put the value of order_product.tax value   as  tax1 and and if tax_rate.rate is 2 than put the value of order_product.tax value   as  tax2 

    i have tried case when but i dont know how to make correct it.

    thank you

    b.regards

    Freitag, 25. Januar 2013 09:28

Alle Antworten

  • E.g. using INNER JOIN and table alias names for better readability:

    SELECT  OP.order_product_id ,
            OP.order_id ,
            OP.product_id ,
            OP.name ,
            OP.model ,
            OP.quantity ,
            OP.price ,
            OP.total ,
            OP.tax ,
            P.tax_class_id AS TaxClass_ID ,
            TRL.tax_rate_id ,
            TRT.rate ,
            CASE WHEN TRT.rate = 10 THEN OP.tax
                 ELSE NULL
            END AS tax1 ,
            CASE WHEN TRT.rate = 2 THEN OP.tax
                 ELSE NULL
            END AS tax2
    FROM    order_product OP
            INNER JOIN product P ON OP.product_id = P.product_id
            INNER JOIN tax_rule TRL ON P.tax_class_id = TRL.tax_class_id
            INNER JOIN tax_rate TRT ON TRL.tax_rate_id = TRT.tax_rate_id
    ORDER BY OP.order_id DESC;

    btw, change the type of this thread to question, please.

    Freitag, 25. Januar 2013 09:47
  • Thank you

    B.Regards

    Freitag, 25. Januar 2013 10:21