My SQl query is not working properly

Question

1.00/5 (3 votes)

See more:

SQL

Create PROCEDURE [dbo].[usp_get_Inv_Part_MasterNew]     
(        
@Company_Code int,
@Part_No NVARCHAR(100)='',            
@Part_Description NVARCHAR(200)='',   
@INV_Type_Class_Code NVARCHAR(max) ='',       
@Ven_Code NVARCHAR(50)='',      
@Hazardous_Part NVARCHAR(10)='2',        
@Is_Tool NVARCHAR(20)='2',        
@Manufacturer_Code NVARCHAR(20) = '',        
@Part_Status_Code NVARCHAR(50) = '0',    
@IBilling_Type_Code int=0,  
@Inv_Type_Code int=0,
@Is_Catalog_item int=2,
@Base_Equipment_Code NVARCHAR(20) = ''
--@startIndex  int,      
--@pageSize  int,      
--@sortBy  nvarchar(300),      
--@totalrowcount int OUTPUT    
)      
      
AS       
BEGIN        
        
--For setting sql error code      
DECLARE @error INT,      
@Inv_Precision INT,    
@Currency Nvarchar(10),
@upperBound int, 
@sqlStatement nvarchar(max)   
     
--IF @startIndex  < 1 SET @startIndex = 1          
--IF @pageSize < 1 SET @pageSize = 1000          
--SET @upperBound = @startIndex + @pageSize    
    
select @Inv_Precision=Inv_set_Inventory_Precision        
from cd_General_parameter         
where Company_Code=@Company_Code      
     
Select @Currency = Currency_Symbol From CD_Currency C inner join CD_General_Parameter G      
On G.Gen_country_Code = C.Country_Code    
And G.Company_Code = C.Company_Code      
Where C.Company_Code = @Company_Code     
    
    
if @Ven_Code=''    
Begin    
SELECT      
IPM.Company_Code,      
ISNULL(IPM.IPart_No,0) as IPart_No,       
ISNULL(IPM.Part_No,'') as Part_No,       
ISNULL(IPM.Part_Description,'') as Part_Description,      
isnull(IPM.Part_Description,'') AS HPart_Description,      
@Currency +' '+ LTRIM(STR(ISNULL(IPM.Rate,0),30,@Inv_Precision)) AS Rate,
isnull(IPM.Rate,0) AS HRate,
ISNULL(IPM.IAccount_No,0) as IAccount_No,      
ISNULL(Acct.Account_Code,'') as Account_Code,      
ISNULL(Acct.Account_Description ,'') as Account_Desc,      
isnull(Acct.Account_Description ,'') AS HAccount_Desc,      
ISNULL(IPM.IUOM_Code,0) as IUOM_Code,      
ISNULL(UOM.UOM_Code,'') as UOM_Code,      
ISNULL(UOM.UOM_Description,'') as UOM_Description,      
isnull(UOM.UOM_Description,'') AS HUOM_Desc,      
ISNULL(IPM.Category_Reference_Code,'') as Category_Reference_Code,      
ISNULL(IPM.IINV_Type_Class_Code,0) as IINV_Type_Class_Code,      
ISNULL(Type.INV_Type_Class_Code,'') as INV_Type_Class_Code,      
ISNULL(Type.Type_Class_Description,'') as INV_Type_Class_Description,      
ISNULL(IPM.User_Code,'') as User_Code,      
ISNULL(V.Vendor_Code,'') as Manufacturer_Code,      
isNull(ipm.Part_Status_Code,0)AS IPart_Status_Code,      
isNull(ps.Part_Status_Code,'')AS Part_Status_Code,      
isNull(ps.Part_Status_Description,'')AS Part_Status_Description,      
ISNULL(IPM.Last_PO_Vendor,0) as Last_PO_Vendor,        
ISNULL(IPM.Last_PO_Cost,0) as Last_PO_Cost,        
ISNULL(IPM.Last_PO_Rate,0) as Last_PO_Rate,        
ISNULL(IPM.Last_PO_UOM,0) as Last_PO_UOM,        
CASE Hazardous_Part      
WHEN 1 THEn 'Yes'      
WHEn 0 THEN  'No'      
WHEN 2 THEn 'All'      
END as Hazardous_Part,        
ISNULL(IPM.Hazardous_Information,'') as Hazardous_Information,        
ISNULL(IPM.Lead_Days,0) as Lead_Days,      
--LTRIM(STR(ISNULL(IPM.Annual_Demand_Quantity,0),30,@Inv_Precision)) AS Annual_Demand_Quantity,        
ISNULL(IPM.Annual_Demand_Quantity,0) as Annual_Demand_Quantity,        
ISNULL(IPM.Ordering_Cost_Per_Order,0) as Ordering_Cost_Per_Order,        
ISNULL(IPM.No_of_Orders_in_Year,0) as No_of_Orders_in_Year,        
ISNULL(IPM.Carrying_Cost_Per_Unit,0) as Carrying_Cost_Per_Unit,        
ISNULL(IPM.EOQ,0) as EOQ,        
ISNULL(IPM.EOQ_Cost,0) as EOQ_Cost,        
ISNULL(IPM.Auto_PO,0) as Auto_PO,      
CASE Is_Tool      
WHEN 0 THEN 'No'      
WHEN 1 THEN 'Yes'      
WHEN 2 THEN 'All'      
END AS Is_Tool,       
ISNULL(IPM.Is_Consignment_Part,0) as Is_Consignment_Part,        
ISNULL(IPM.Part_Criticality_Code,0) as Part_Criticality_Code,        
ISNULL(PCC.Part_Criticality_Description,'') as Part_Criticality_Description,        
ISNULL(IPM.CF_1,'') as CF_1,        
ISNULL(IPM.CF_2,'') as CF_2,        
ISNULL(IPM.CF_3,'') as CF_3,        
ISNULL(IPM.CF_4,'') as CF_4,        
ISNULL(IPM.CF_5,'') as CF_5,        
ISNULL(IPM.CF_6,'') as CF_6,        
ISNULL(IPM.CF_7,'') as CF_7,        
ISNULL(IPM.CF_8,'') as CF_8,        
ISNULL(IPM.CF_9,'') as CF_9,        
ISNULL(IPM.CF_10,'') as CF_10,        
--ISNULL(CONVERT(NVARCHAR(20),IPM.Date_Added,101),0) AS Date_Added,        
Date_Added,      
IPM.Create_Date_time,        
--p.Create_Date_time,        
IPM.Create_User_ID,        
IPM.Last_Update_User_ID  ,      
ven.Vendor_Code,      
ven.Vendor_Name,    
BT.Billing_Type_Code,    
IPM.IBilling_Type_Code,  
Inv_Type_Description,
ISNULL(IPM.Days_to_Expire,0) as Days_to_Expire,
ISNULL(MPI.product_id,0) as product_id,
CASE ISNULL(Is_Catalog_Item,0) WHEN 1 THEN 'Yes' ELSE 'No' End AS Is_Catalog_Item,
ISNULL(eq.Eq_Code,'') as Base_Equipment_Code,
LTRIM(STR(ISNULL(IPM.Stck_Room_Maximum_Quantity,0),30,@Inv_Precision)) AS Minimum_Quantity,   
LTRIM(STR(ISNULL(IPM.Stck_Room_Minimum_Quantity,0),30,@Inv_Precision)) AS Maximum_Quantity,  
ISNULL(IPM.No_of_Stock_Outs,0) AS No_of_Stock_Outs,  
LTRIM(STR(ISNULL(IPM.Stck_Room_Reorder_quantity,0),30,@Inv_Precision)) AS Reorder_quantity,
ISNULL(IPM.UPC,'') AS UPC,
--isnull(ipm.StockRoom,'') as StockRoom,
CASE ISNULL(PrimaryStockRoom,0) WHEN 1 THEN 'Yes' ELSE 'No' End AS PrimaryStockRoom,
ISNULL(IFacilityCode,0) AS IFacilityCode,
ISNULL(IRoomCode,0) AS IRoomCode

into #Temp_Master    
FROM Inv_Part_Master IPM      
      
      
LEFT OUTER JOIN CD_Account_Budget Acct      
ON (IPM.Company_Code = Acct.Company_Code      
AND IPM.IAccount_No = Acct.IAccount_No)      
      
LEFT OUTER JOIN CD_UOM UOM      
ON (IPM.Company_Code = UOM.Company_Code      
AND IPM.IUOM_Code = UOM.IUOM_Code)      
      
LEFT OUTER JOIN VEN_Vendor V      
ON 
--V.Company_code=IPM.Company_Code  AND
 V.IVendor_Code=IPM.Manufacturer_Code      
      
LEFT OUTER JOIN CD_INV_Type_Class type      
ON (Type.Company_Code = IPM.Company_Code      
AND Type.IINV_Type_Class_Code = IPM.IINV_Type_Class_Code)      
      
left outer join INV_Part_Ven ipv        
ON 
--(ipv.Company_Code = ipm.Company_Code        
ipv.IPart_No = ipm.IPart_No    
AND (Primary_Vendor=1 and @Ven_Code='')    
--or(@Ven_Code<>''and Vendor_Code=@Ven_Code))    
--)        
      
left outer join Ven_Vendor ven        
on
--(ven.Company_Code=ipv.Company_Code and
 ven.IVendor_Code=ipv.IVendor_Code
       
LEFT OUTER JOIN CD_INV_Part_Status ps      
ON (ps.Company_Code = ipm.Company_Code      
AND ps.IPart_Status_Code = ipm.Part_Status_Code)      
      
Left Outer join CD_Part_Criticality PCC        
ON(ipm.Part_Criticality_Code=PCC.Part_Criticality_Code    
And ipm.Company_Code = PCC.Company_Code)    
    
Left Outer Join CD_INV_Billing_Type BT    
ON(IPM.IBilling_Type_Code=BT.IBilling_Type_Code    
and Ipm.Company_Code=BT.Company_Code)    
  
Left Outer Join CD_INV_Type IT  
ON IPM.Company_Code = IT.Company_Code  
AND IPM.IInv_Type_Code = IT.IInv_Type_Code  
    
  
Left Outer Join [MtrackAppMagentoInterface]..[Magento_Product_Part_Interface]  MPI 
On  IPM.ipart_no=MPI.IPart_No
AND IPM.Company_Code =MPI.Company_Code 

 left outer join Eq_Equipment eq
 on (IPM.Company_Code = eq.Company_Code 
 and IPM.IEquipment_Code_Base=eq.IEq_Code)

WHERE IPM.Company_code=@Company_Code      
and (IPM.Part_No like @Part_No or (charindex('%',@Part_No)= 0  and (IPM.Part_No = @Part_No or @Part_No=''))) 
and (IPM.Part_Description like @Part_Description or (charindex('%',@Part_Description)= 0 and (IPM.Part_Description = @Part_Description or @Part_Description='')))    
and ((ipm.Part_status_Code like @Part_status_Code) or (charindex('%',@Part_status_Code)= 0 and ipm.Part_status_Code = @Part_status_Code) or @Part_status_Code='0')    
and (ipm.Hazardous_Part like @Hazardous_Part or (charindex('%',@Hazardous_Part)= 0 and (ipm.Hazardous_Part = @Hazardous_Part or @Hazardous_Part='2')))    
and (isnull(ipm.Is_Tool,0) like @Is_Tool or (charindex('%',@Is_Tool)= 0 and (isnull(ipm.Is_Tool,0) = @Is_Tool or @Is_Tool='2') ))    
and ((type.IINV_Type_Class_Code in( select * from dbo.Split(@INV_Type_Class_Code,'|')) and @INV_Type_Class_Code<>'')Or @INV_Type_Class_Code='')
and (v.Vendor_Code like @Manufacturer_Code or (charindex('%',@Manufacturer_Code)= 0 and (v.Vendor_Code = @Manufacturer_Code or @Manufacturer_Code='')))    
and (ven.Vendor_Code like @Ven_Code or (charindex('%',@Ven_Code)= 0 and (ven.Vendor_Code = @Ven_Code or @Ven_Code='')))     
and ((IPM.IBilling_Type_Code=@IBilling_Type_Code) or(@IBilling_Type_Code=0))    
and ((IPM.IInv_Type_Code=@Inv_Type_Code ) or(@Inv_Type_Code=0))     
and ((isnull(ipm.Is_Catalog_Item,0)  = @Is_Catalog_item and @Is_Catalog_item <> 2) or @Is_Catalog_item =2) 
and (eq.Eq_Code like @Base_Equipment_Code or (charindex('%',@Base_Equipment_Code)= 0 and (eq.Eq_Code = @Base_Equipment_Code or @Base_Equipment_Code='')))    
--order by ipm.Part_No   
order by ipm.Last_Update_Date_time desc
   
--Added By shubhra For cal sum of on hand qty  
--and difference of issued qty and recieved qty  
Select sum(On_Hand_Quantity) as On_Hand_Qty,Company_Code,IPart_No    
into #TemStock    
from INV_Stock     
where Company_Code = @Company_Code   
and IPart_No in (select IPart_No from #Temp_Master)  
AND Stock_Room_Status = 1  
group by IPart_No,Company_Code    
    
select ipart_no,company_Code,sum(isnull(issue_uom_order_quantity,0)) as Issued    
into #TempPO    
from PO_Item     
where Company_Code = @Company_Code    
and ipart_no is not null   
and IPart_No in (select IPart_No from #Temp_Master)  
and po_item_Status_code=1    
group by ipart_no,company_Code    
    
select     
i.ipart_no,     
i.company_code,    
sum(isnull(issue_uom_quantity_received,0)) as Recieved    
into #TempPR    
from po_receiver r,po_item i    
where  r.company_code=i.company_code    
and r.ipo_no=i.ipo_no    
and r.line_item_Sr_no=i.line_item_Sr_no    
and i.po_item_Status_code=1    
and i.ipart_no is not null    
and i.IPart_No in (select IPart_No from #Temp_Master)  
and r.returnflag = 0
and r.ipo_receiver_no not in 
(select reference_ipo_receiver_no from po_receiver where company_code = i.company_code and ipo_no = i.ipo_no and returnflag = 2)
group by i.ipart_no,i.company_code    
    
    
select P.*,LTRIM(STR(ISNULL(S.On_Hand_Qty,0),30,@Inv_Precision))as On_Hand_Quantity,
LTRIM(STR(CASE WHEN ISNULL(Issued,0)-ISNULL(Recieved,0) < 0 THEN 0 ELSE ISNULL(Issued,0)-ISNULL(Recieved,0) END,30,@Inv_Precision)) as Open_PO    
from #Temp_Master P    
Left Outer Join #TemStock S    
on(P.Company_Code = S.Company_Code    
and P.IPart_No = S.IPart_No)    
Left Outer Join #TempPO PO    
on(P.Company_Code = PO.Company_Code    
and P.IPart_NO = PO.IPart_No)    
Left Outer Join #TempPR PR    
on(P.Company_Code = PR.Company_Code    
and P.IPart_NO = PR.IPart_NO)    
    
end      

  
  
if @Ven_Code<>''    
Begin    
  
SELECT      
IPM.Company_Code,      
ISNULL(IPM.IPart_No,0) as IPart_No,       
ISNULL(IPM.Part_No,'') as Part_No,       
ISNULL(IPM.Part_Description,'') as Part_Description,      
isnull(IPM.Part_Description,'') AS HPart_Description,      
LTRIM(STR(ISNULL(IPM.Rate,0),30,@Inv_Precision)) AS Rate,        
isnull(IPM.Rate,0) AS HRate,  
ISNULL(IPM.IAccount_No,0) as IAccount_No,      
ISNULL(Acct.Account_Code,'') as Account_Code,      
ISNULL(Acct.Account_Description,'') as Account_Desc,      
isnull(Acct.Account_Description,'') AS HAccount_Desc,      
ISNULL(IPM.IUOM_Code,0) as IUOM_Code,      
ISNULL(UOM.UOM_Code,'') as UOM_Code,      
ISNULL(UOM.UOM_Description,'') as UOM_Description,      
isnull(UOM.UOM_Description ,'') AS HUOM_Desc,      
ISNULL(IPM.Category_Reference_Code,'') as Category_Reference_Code,      
ISNULL(IPM.IINV_Type_Class_Code,0) as IINV_Type_Class_Code,      
ISNULL(Type.INV_Type_Class_Code,'') as INV_Type_Class_Code,      
ISNULL(Type.Type_Class_Description,'') as INV_Type_Class_Description,      
ISNULL(IPM.User_Code,'') as User_Code,      
ISNULL(V.Vendor_Code,'') as Manufacturer_Code,      
isNull(ipm.Part_Status_Code,0)AS IPart_Status_Code,      
isNull(ps.Part_Status_Code,'')AS Part_Status_Code,      
isNull(ps.Part_Status_Description,'')AS Part_Status_Description,      
ISNULL(IPM.Last_PO_Vendor,0) as Last_PO_Vendor,        
ISNULL(IPM.Last_PO_Cost,0) as Last_PO_Cost,        
ISNULL(IPM.Last_PO_Rate,0) as Last_PO_Rate,        
ISNULL(IPM.Last_PO_UOM,0) as Last_PO_UOM,        
CASE Hazardous_Part      
WHEN 1 THEn 'Yes'      
WHEn 0 THEN  'No'      
WHEN 2 THEn 'All'      
END as Hazardous_Part,        
ISNULL(IPM.Hazardous_Information,'') as Hazardous_Information,        
ISNULL(IPM.Lead_Days,0) as Lead_Days,      
ISNULL(IPM.Annual_Demand_Quantity,0) as Annual_Demand_Quantity,        
ISNULL(IPM.Ordering_Cost_Per_Order,0) as Ordering_Cost_Per_Order,        
ISNULL(IPM.No_of_Orders_in_Year,0) as No_of_Orders_in_Year,        
ISNULL(IPM.Carrying_Cost_Per_Unit,0) as Carrying_Cost_Per_Unit,        
ISNULL(IPM.EOQ,0) as EOQ,        
ISNULL(IPM.EOQ_Cost,0) as EOQ_Cost,        
ISNULL(IPM.Auto_PO,0) as Auto_PO,      
CASE Is_Tool      
WHEN 0 THEN 'No'      
WHEN 1 THEN 'Yes'      
WHEN 2 THEN 'All'      
END AS Is_Tool,       
ISNULL(IPM.Is_Consignment_Part,0) as Is_Consignment_Part,        
ISNULL(IPM.Part_Criticality_Code,0) as Part_Criticality_Code,        
ISNULL(PCC.Part_Criticality_Description,'') as Part_Criticality_Description,        
ISNULL(IPM.CF_1,'') as CF_1,        
ISNULL(IPM.CF_2,'') as CF_2,        
ISNULL(IPM.CF_3,'') as CF_3,        
ISNULL(IPM.CF_4,'') as CF_4,        
ISNULL(IPM.CF_5,'') as CF_5,        
ISNULL(IPM.CF_6,'') as CF_6,        
ISNULL(IPM.CF_7,'') as CF_7,        
ISNULL(IPM.CF_8,'') as CF_8,        
ISNULL(IPM.CF_9,'') as CF_9,        
ISNULL(IPM.CF_10,'') as CF_10,         
Date_Added,      
IPM.Create_Date_time,        
IPM.Create_User_ID,        
IPM.Last_Update_User_ID  ,      
ven.Vendor_Code,      
ven.Vendor_Name,    
BT.Billing_Type_Code,    
IPM.IBilling_Type_Code,    
Inv_Type_Description,
ISNULL(IPM.Days_to_Expire,0) as Days_to_Expire,
ISNULL(MPI.product_id,0) as product_id ,
CASE ISNULL(Is_Catalog_Item,0) WHEN 1 THEN 'Yes' ELSE 'No' End AS Is_Catalog_Item,
ISNULL(eq.Eq_Code,'') as Base_Equipment_Code,
LTRIM(STR(ISNULL(IPM.Stck_Room_Maximum_Quantity,0),30,@Inv_Precision)) AS Minimum_Quantity,   
LTRIM(STR(ISNULL(IPM.Stck_Room_Minimum_Quantity,0),30,@Inv_Precision)) AS Maximum_Quantity,  
ISNULL(IPM.No_of_Stock_Outs,0) AS No_of_Stock_Outs,  
LTRIM(STR(ISNULL(IPM.Stck_Room_Reorder_quantity,0),30,@Inv_Precision)) AS Reorder_quantity,
ISNULL(IPM.UPC,'') AS UPC
into #Temp_PMMaster    
    
FROM Inv_Part_Master IPM      
      
      
LEFT OUTER JOIN CD_Account_Budget Acct      
ON (IPM.Company_Code = Acct.Company_Code      
AND IPM.IAccount_No = Acct.IAccount_No)      
      
LEFT OUTER JOIN CD_UOM UOM      
ON (IPM.Company_Code = UOM.Company_Code      
AND IPM.IUOM_Code = UOM.IUOM_Code)      
      
LEFT OUTER JOIN VEN_Vendor V      
ON 
--V.Company_code=IPM.Company_Code      
V.IVendor_Code=IPM.Manufacturer_Code      
      
LEFT OUTER JOIN CD_INV_Type_Class type      
ON (Type.Company_Code = IPM.Company_Code      
AND Type.IINV_Type_Class_Code = IPM.IINV_Type_Class_Code)      
      
left outer join INV_Part_Ven ipv        
ON 
--(ipv.Company_Code = ipm.Company_Code        
 ipv.IPart_No = ipm.IPart_No    
        
      
left outer join Ven_Vendor ven        
on
--(ven.Company_Code=ipv.Company_Code  and 
ven.IVendor_Code=ipv.IVendor_Code
       
LEFT OUTER JOIN CD_INV_Part_Status ps      
ON (ps.Company_Code = ipm.Company_Code      
AND ps.IPart_Status_Code = ipm.Part_Status_Code)      
      
Left Outer join CD_Part_Criticality PCC        
ON(ipm.Part_Criticality_Code=PCC.Part_Criticality_Code    
and ipm.Company_Code=PCC.Company_Code    
)        
     
Left Outer Join CD_INV_Billing_Type BT    
ON(IPM.IBilling_Type_Code=BT.IBilling_Type_Code    
and Ipm.Company_Code=BT.Company_Code)    
  
Left Outer Join CD_INV_Type IT  
ON IPM.Company_Code = IT.Company_Code  
AND IPM.IInv_Type_Code = IT.IInv_Type_Code  

  
Left Outer Join [MtrackAppMagentoInterface]..[Magento_Product_Part_Interface]  MPI 
On  IPM.ipart_no=MPI.IPart_No
AND IPM.Company_Code =MPI.Company_Code 

 left outer join Eq_Equipment eq
 on (IPM.Company_Code = eq.Company_Code 
 and IPM.IEquipment_Code_Base=eq.IEq_Code)
 
    
WHERE IPM.Company_code=@Company_Code  
and (IPM.Part_No like @Part_No or (charindex('%',@Part_No)= 0  and (IPM.Part_No = @Part_No or @Part_No=''))) 

and (IPM.Part_Description like @Part_Description or (charindex('%',@Part_Description)= 0 and 
(IPM.Part_Description = @Part_Description and  @Part_Description<>'')) or @Part_Description='')  
      
and ((ipm.Part_status_Code like @Part_status_Code) or (charindex('%',@Part_status_Code)= 0 
and ipm.Part_status_Code = @Part_status_Code) or @Part_status_Code='0')   
and (ipm.Hazardous_Part like @Hazardous_Part or (charindex('%',@Hazardous_Part)= 0 and (ipm.Hazardous_Part = @Hazardous_Part or @Hazardous_Part='2')))  
--and (ipm.Hazardous_Part = @Hazardous_Part and @Hazardous_Part <> '2')  or  @Hazardous_Part='2'

and ((type.IINV_Type_Class_Code in( select * from dbo.Split(@INV_Type_Class_Code,'|')) and @INV_Type_Class_Code<>'')Or @INV_Type_Class_Code='')

and (v.Vendor_Code like @Manufacturer_Code or (charindex('%',@Manufacturer_Code)= 0 and 
(isnull(v.Vendor_Code,'') = @Manufacturer_Code and  @Manufacturer_Code<>'')) or @Manufacturer_Code='')  

--Added by Deepika / 10 July 2013 
and (eq.Eq_Code like @Base_Equipment_Code or (charindex('%',@Base_Equipment_Code)= 0 and 
(isnull(eq.Eq_Code,'') = @Base_Equipment_Code and  @Base_Equipment_Code<>'')) or @Base_Equipment_Code='')  


and (ven.Vendor_Code like @Ven_Code or (charindex('%',@Ven_Code)= 0 
and (ven.Vendor_Code = @Ven_Code And @Ven_Code<>''))or @Ven_Code='') 
    
and ((IPM.IBilling_Type_Code=@IBilling_Type_Code and @IBilling_Type_Code<>0) or(@IBilling_Type_Code=0))    
and ((IPM.IInv_Type_Code=@Inv_Type_Code and @Inv_Type_Code<>0) or(@Inv_Type_Code=0))     
and ((isnull(ipm.Is_Catalog_Item,0)  = @Is_Catalog_item and @Is_Catalog_item <> 2) or @Is_Catalog_item =2) 
--order by ipm.Part_No   
order by ipm.Last_Update_Date_time desc
  
Select sum(On_Hand_Quantity) as On_Hand_Qty,Company_Code,IPart_No    
into #Tem_INVStock    
from INV_Stock     
where Company_Code = @Company_Code    
and IPart_No in (select IPart_No from #Temp_PMMaster)  
AND Stock_Room_Status = 1  
group by IPart_No,Company_Code    
  
select ipart_no,company_Code,sum(isnull(issue_uom_order_quantity,0)) as Issued    
into #Temp_PO    
from PO_Item     
where Company_Code = @Company_Code   
and ipart_no is not null    
and IPart_No in (select IPart_No from #Temp_PMMaster)  
and po_item_Status_code=1    
group by ipart_no,company_Code    
  
select     
i.ipart_no,     
i.company_code,    
sum(isnull(issue_uom_quantity_received,0)) as Recieved    
into #Temp_PR    
from po_receiver r,po_item i    
where  r.company_code=i.company_code   
and r.ipo_no=i.ipo_no    
and r.line_item_Sr_no=i.line_item_Sr_no    
and i.po_item_Status_code=1    
and i.ipart_no is not null    
and i.IPart_No in (select IPart_No from #Temp_PMMaster)  
group by i.ipart_no,i.company_code    
    
    
   -- select * from #Temp_PMMaster
    
select P.*,LTRIM(STR(ISNULL(S.On_Hand_Qty,0),30,@Inv_Precision))as On_Hand_Quantity,
LTRIM(STR(CASE WHEN ISNULL(Issued,0)-ISNULL(Recieved,0) < 0 THEN 0 ELSE ISNULL(Issued,0)-ISNULL(Recieved,0) END,30,@Inv_Precision)) as Open_PO    
from #Temp_PMMaster P  
Left Outer Join #Tem_INVStock S   
on(P.Company_Code = S.Company_Code    
and P.IPart_No = S.IPart_No )  
Left Outer Join #Temp_PO PO    
on(P.Company_Code = PO.Company_Code    
and P.IPart_NO = PO.IPart_No)    
Left Outer Join #Temp_PR PR    
on(P.Company_Code = PR.Company_Code    
and P.IPart_NO = PR.IPart_NO)      
  
     
end  
SELECT @error = @@error       
      
IF @error <> 0      
        RETURN @error      
END

This is my query. In this sorting for column On_Hand_Quantity is not working.Please help i didn't understand why it is not working

Posted 13-May-14 19:47pm

Member 10685464

Updated 13-May-14 22:43pm

phil.o

v4

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

maba001 · Accepted Answer · 2014-05-13T20:34:00

The only order clauses you have in here are:

"ORDER BY ipm.Last_Update_Date_time DESC" and the same again some time later
"ORDER BY ipm.Last_Update_Date_time DESC"

You can easily search for all your current ORDER BY clauses.
So to sort on "On_Hand_Quantity", you would just add the ORDER BY clause to your last select:

SQL

   -- select * from #Temp_PMMaster
    
select P.*,LTRIM(STR(ISNULL(S.On_Hand_Qty,0),30,@Inv_Precision))as On_Hand_Quantity,
LTRIM(STR(CASE WHEN ISNULL(Issued,0)-ISNULL(Recieved,0) < 0 THEN 0 ELSE ISNULL(Issued,0)-ISNULL(Recieved,0) END,30,@Inv_Precision)) as Open_PO    
from #Temp_PMMaster P  
Left Outer Join #Tem_INVStock S   
on(P.Company_Code = S.Company_Code    
and P.IPart_No = S.IPart_No )  
Left Outer Join #Temp_PO PO    
on(P.Company_Code = PO.Company_Code    
and P.IPart_NO = PO.IPart_No)    
Left Outer Join #Temp_PR PR    
on(P.Company_Code = PR.Company_Code    
and P.IPart_NO = PR.IPart_NO)      

  -- this would be the ORDER BY clause
ORDER BY On_Hand_Quantity

Aarti Meswania · Accepted Answer · 2014-05-13T20:37:00

Solution 2

Final output will be from #Temp_PMMaster

in that select clause... you have not use order by
see.. below query

SQL

select P.*,LTRIM(STR(ISNULL(S.On_Hand_Qty,0),30,@Inv_Precision))as On_Hand_Quantity,
LTRIM(STR(CASE WHEN ISNULL(Issued,0)-ISNULL(Recieved,0) < 0 THEN 0 ELSE ISNULL(Issued,0)-ISNULL(Recieved,0) END,30,@Inv_Precision)) as Open_PO    
from #Temp_PMMaster P  
Left Outer Join #Tem_INVStock S   
on(P.Company_Code = S.Company_Code    
and P.IPart_No = S.IPart_No )  
Left Outer Join #Temp_PO PO    
on(P.Company_Code = PO.Company_Code    
and P.IPart_NO = PO.IPart_No)    
Left Outer Join #Temp_PR PR    
on(P.Company_Code = PR.Company_Code    
and P.IPart_NO = PR.IPart_NO) 
order by On_Hand_Quantity

Happy Coding!
:)

Posted 13-May-14 20:37pm

Aarti Meswania

Comments

Member 10685464 14-May-14 2:39am

thank u but after using order by clause also it is not working

Aarti Meswania 14-May-14 2:45am

please provide sample output.

Member 10685464 14-May-14 3:24am

On_Hand_Quantity
9993.00
9970.00
996.00
99.00
99.00
980.00
98.00
98.00
975.00

this is output

Aarti Meswania 15-May-14 1:47am

it is because you have used str()
please do as below
order by S.On_Hand_Qty

Member 10685464 15-May-14 1:54am

thank u but i got the answer . it is because str as well as because of ltrim function also

Aarti Meswania 15-May-14 2:08am

that's good you got solution.
good day! :)