|
tantcu
Yak Posting Veteran
USA
58 Posts |
 Posted - 06/25/2012 : 13:03:47
|
Hello,
I tried to create a new row from an existing table when my boss asked me to run a case when funtion to find out a not null value of the product category. This is my original querry.
SELECT
-- ,[p21_sales_history_report_view].[product_group_id]
COALESCE(hsc_product_group_category.product_group_catagory, 'MISC') as product_group_catefory
,ROUND(SUM([detail_price]),2) AS 'sales'
,ROUND(SUM([detail_cogs]),2) AS 'cogs'
,((SUM(detail_price))-(SUM(detail_cogs)))as 'profit'
FROM [P21].[dbo].[p21_sales_history_report_view]
LEFT OUTER JOIN hsc_p21_view_inv_mast ON [p21_sales_history_report_view].inv_mast_uid = hsc_p21_view_inv_mast.inv_mast_uid
LEFT OUTER JOIN hsc_product_group_category ON [p21_sales_history_report_view].product_group_id = hsc_product_group_category.product_group_id
WHERE period = 6 AND
year_for_period = 2012 AND
--[p21_sales_history_report_view].product_group_id LIKE 'LAB%' AND
parent_oe_line_uid = 0 AND
hsc_p21_view_inv_mast.class_id3 IS NULL AND
--oe_line_assembly = 'N' AND
(invoice_adjustment_type='C' AND [p21_sales_history_report_view].source_type_cd = 2638 OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D'))
--AND [p21_sales_history_report_view].vendor_consigned = 'N' AND projected_order = 'N' AND (detail_type IS NULL OR detail_type = 0)
-- AND (progress_bill_flag = 'N' OR progress_bill_flag IS NULL )
GROUP BY
COALESCE(hsc_product_group_category.product_group_catagory, 'MISC') WITH ROLLUP
I also attached the image from my computer. My boss wants me to add a row into a product and category columm that return a result of the not null value from p21_view_inv_mast.class_id3 by using case when function.
Please help me
![]() |
Edited by - tantcu on 06/25/2012 13:08:40
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 06/25/2012 : 13:52:10
|
I have no clue what you are asking.
The title of your post implies you want to "insert a row" to a table but your question seems to have something to do with modifying that query you posted. Actually, you never even asked a question you just made some grammatically ambiguous statements and the said, "please help me". So I can't tell what you need help with.
One thing I see is that you posted:
quote:
return a result of the not null value from p21_view_inv_mast.class_id3
Yet your statement includes this in the WHERE clause:
AND hsc_p21_view_inv_mast.class_id3 IS NULL
So assuming you do want to modify that SELECT statement you will likely need to remove that criteria.
Be One with the Optimizer
TG |
 |
|
How to insert a new row from existing table
