Forum Discussion

HelenH2300's avatar
HelenH2300
Copper Contributor
Oct 14, 2024

Data selection formula

I am trying to write an SQL query to select / change a value based on a formula Below is the query I have written so far - SELECT PV_Job.JobCode, PV_Customer.CustName, PV_JobLine.ItemCode, PM_I...
rodgerkong's avatar
rodgerkong
Iron Contributor
Oct 14, 2024

HelenH2300 

 

IIF(
ROUND ((PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty),0) < 0,
0,
ROUND ((PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty),0)
)AS Shortfall,

ROUND(
  IIF(
    (PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty) < 0,
    0,
    PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty
  )/PV_JobLine.OrderedQty, 2
)AS pOFshortfall --to remain precision, not rounded when calculating "Shortfall", and PV_JobLine.OrderedQty cannot be 0, or you have to use another IIF

 

HelenH2300's avatar
HelenH2300
Copper Contributor
Oct 14, 2024

rodgerkong 

 

Thanks for that, but I think I must have done something wrong

 

This is what I typed in

SELECT
PV_Job.JobCode,
PV_Customer.CustName,
PV_JobLine.ItemCode,
PM_Item.ItemShortDesc,
PV_JobLine.OrderedQty,
(PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty) AS FGQty,
IIF(
(PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty) < 0,
0,
(PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty)
)AS Shortfall,
PV_Job.ProductionStatus,
PV_Job.ProdCompDate,
PV_Job.JobCreateDate,
CASE PV_Job.ProductionStatus
WHEN 0 THEN ''
WHEN 1 THEN 'Unplanned'
WHEN 4 THEN 'Planned'
WHEN 8 THEN 'Started'
WHEN 9 THEN 'Completed'
WHEN 99 THEN 'PSA Completed'
END as 'ProdStatus'
FROM PV_Job (NOLOCK)
INNER JOIN PV_JobLine (NOLOCK) ON
((PV_Job.CompNum=PV_JobLine.CompNum) AND
(PV_Job.PlantCode=PV_JobLine.PlantCode)) AND
(PV_Job.JobCode=PV_JobLine.JobCode)
LEFT OUTER JOIN PV_Customer (NOLOCK) ON
(PV_Job.CompNum=PV_Customer.CompNum) AND
(PV_Job.CustCode=PV_Customer.CustCode)
LEFT OUTER JOIN PM_Item (NOLOCK) ON
(PV_JobLine.CompNum=PM_Item.CompNum) AND
(PV_JobLine.ItemCode=PM_Item.ItemCode)

 

When I test the query I got this error - an ideas?

 

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Oct 14, 2024

    HelenH2300 

    Can you run the script in SSMS and check the result?

    • HelenH2300's avatar
      HelenH2300
      Copper Contributor
      Oct 14, 2024
      Sorry, I'm not sure what you mean
      I'm fairly new to this & using an editor I've been provided with
      • rodgerkong's avatar
        rodgerkong
        Iron Contributor
        Oct 14, 2024
        SSMS is official management tools of SQL Server from Microsoft. It has a great SQL editer, and you can run SQL and check result direct from server with it. That means you can verify if your SQL was correct natively. Download from here https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16#download-ssms

Resources