我的SQl查询无法正常工作 [英] My SQl query is not working properly
本文介绍了我的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
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
推荐答案
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:
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:
-- 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_QuantityFinal output will be from #Temp_PMMaster
in that select clause... you have not use order by
see.. below query
Final output will be from #Temp_PMMaster
in that select clause... you have not use order by
see.. below query
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!
:)
Happy Coding!
:)
这篇关于我的SQl查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
