Multiplying fields with conditions in SQL Server Management Studio
I am getting an error message "near mileage".
Any ideas? This looks like the last big problem with this procedure.
WHERE
qryFieldVoucherPersonnel.PersNum=[FDMI]
AND
tblCurrentReportStatus.PRODStatus='Report
received'
AND
tblFDMIVouchers.PaidStatus=0
Or tblFDMIVouchers.PaidStatus
Is
Null
AND
tblAutopsy.ExamType
Not
Like
'field*'
OR
qryFieldVoucherPersonnel.PersNum=[FDMI]
AND
tblCurrentReportStatus.PRODStatus='Report
received'
AND
tblFDMIVouchers.PaidStatus=0
Or tblFDMIVouchers.PaidStatus
Is
Null
AND
tblAutopsy.ExamType
Like
'field*'
AND
tblCurrentReportStatus.FieldExternalStatus='Report
received'
[mileage]*[qryMileageFee.Fee]
AS
[Mileage Paid],
Gee
August 13th, 2012 7:51pm
Hi Great !
You need to fix your query;
Your [Mileage Paid] calculation should be in you SELECT list like show below;
SELECT [mileage]*[qryMileageFee.Fee] AS [Mileage Paid]
Your WHERE clause should be something like below;
WHERE qryFieldVoucherPersonnel.PersNum=[FDMI]
AND tblCurrentReportStatus.PRODStatus='Report received'
AND tblFDMIVouchers.PaidStatus=0 Or tblFDMIVouchers.PaidStatus Is Null
AND tblAutopsy.ExamType Not Like 'field*'
OR qryFieldVoucherPersonnel.PersNum=[FDMI]
AND tblCurrentReportStatus.PRODStatus='Report received'
AND tblFDMIVouchers.PaidStatus=0 Or tblFDMIVouchers.PaidStatus Is Null
AND tblAutopsy.ExamType Like 'field*'
AND tblCurrentReportStatus.FieldExternalStatus='Report received'
Please let me know if this helps. Hopefully i have answered you correctly.
Thanks, Hasham Niaz
Free Windows Admin Tool Kit Click here and download it now
August 13th, 2012 9:28pm
I appreciate your response.
Now I am getting this message:
Msg 207, Level 16, State 1, Procedure prcFieldVouchersRD, Line 88
Invalid column name 'mileage'.
Msg 207, Level 16, State 1, Procedure prcFieldVouchersRD, Line 88
Invalid column name 'qryMileageFee.Fee'.
Gee
August 13th, 2012 9:51pm
Hi Great !
Can you post the whole query which you are trying to execute.
Thanks, Hasham Niaz
Free Windows Admin Tool Kit Click here and download it now
August 13th, 2012 10:30pm
Here it is.
I really appreciate this help!
SET
NOCOUNT ON
SELECT qryFieldVoucherPersonnel.PersNameLast,
qryFieldVoucherPersonnel.PersNum,
tblCurrentReportStatus.CaseNum,
tblCurrentReportStatus.PRODStatus,
tblFDMIVouchers.PaidStatus,
tblCase.FieldInvestigator,
tblCase.PrnByDate,
tblFDMIVouchers.Mileage,
tblCase.CaseType,
tblAutopsy.ExamType,
qryFieldVouchers.mileage,
CASE
WHEN ExamType like
'No%'
THEN NewJTFee
WHEN ExamType
Is Null
THEN NewAutoFee
ELSE ExamType
End
AS [Case Paid],
tblCase.FDMI,
tblCase.NameLast,
tblCase.NameFirst,
tblCase.NameMiddle,
qryFieldVoucherPersonnel.PersAddr,
[PersCity]
& ", "
& [PersState] & "
" & [PersZip]
AS CSZ,
tblFDMIVouchers.PaidStatusDate,
tblCurrentReportStatus.PRODStatusDate,
[PersNameLast]+[PersNum]
AS GroupFDMI,
CASE
WHEN [PrnByDate]<'10/1/2006'
THEN 54.3 ELSE 65.16
END
AS [NewJTFee],
CASE
WHEN [PrnByDate]<'10/1/2006'
THEN 76.02
WHEN [PrnByDate]
Between '10/1/2006'
And '8/1/2007' THEN 86.88
ELSE 103.17
END
AS [NewFldFee],
CASE
WHEN [PrnByDate]<'10/1/2006'
THEN 76.02 ELSE 86.88
END
AS [NewAutoFee],
tblCurrentReportStatus.FieldExternalStatus
FROM qryMileageFee,
qryFieldVouchers,
qryJTFee,
qryFieldFee,
qryFieldVoucherPersonnel,
tblCurrentReportStatus
INNER
JOIN tblFDMIVouchers
ON tblCurrentReportStatus.CaseNum
Like tblFDMIVouchers.CaseNum
INNER
JOIN tblCase
ON tblCurrentReportStatus.CaseNum
Like tblCase.CaseNum
INNER
JOIN tblAutopsy
ON tblCurrentReportStatus.CaseNum
Like tblAutopsy.CaseNum
WHERE qryFieldVoucherPersonnel.PersNum
Like [FDMI]
AND tblCurrentReportStatus.PRODStatus
Like'Report received'
AND tblFDMIVouchers.PaidStatus
Like '0'
Or tblFDMIVouchers.PaidStatus
Is Null
AND tblAutopsy.ExamType
Not Like
'field*'
OR qryFieldVoucherPersonnel.PersNum
Like [FDMI]
AND tblCurrentReportStatus.PRODStatus
Like 'Report received'
AND tblFDMIVouchers.PaidStatus
Like '0'
Or tblFDMIVouchers.PaidStatus
Is Null
AND tblAutopsy.ExamType
Like 'field*'
AND tblCurrentReportStatus.FieldExternalStatus='Report received'
SELECT [mileage]*[Fee]
AS [Mileage Paid]
Gee
August 13th, 2012 10:36pm
Hi Great !
You may get the desired output using below query;
SELECT qryfieldvoucherpersonnel.persnamelast,
qryfieldvoucherpersonnel.persnum,
tblcurrentreportstatus.casenum,
tblcurrentreportstatus.prodstatus,
tblfdmivouchers.paidstatus,
tblcase.fieldinvestigator,
tblcase.prnbydate,
tblfdmivouchers.mileage,
tblcase.casetype,
tblautopsy.examtype,
qryfieldvouchers.mileage,
CASE
WHEN examtype LIKE 'No%' THEN newjtfee
WHEN examtype IS NULL THEN newautofee
ELSE examtype
END AS [Case Paid],
tblcase.fdmi,
tblcase.namelast,
tblcase.namefirst,
tblcase.namemiddle,
qryfieldvoucherpersonnel.persaddr,
[perscity] & ",, " & [persstate] & " " & [perszip] AS CSZ,
tblfdmivouchers.paidstatusdate,
tblcurrentreportstatus.prodstatusdate,
[persnamelast] + [persnum] AS GroupFDMI,
CASE
WHEN [prnbydate] < '10/1/2006' THEN 54.3
ELSE 65.16
END AS [NewJTFee],
CASE
WHEN [prnbydate] < '10/1/2006' THEN 76.02
WHEN [prnbydate] BETWEEN '10/1/2006' AND '8/1/2007' THEN 86.88
ELSE 103.17
END AS [NewFldFee],
CASE
WHEN [prnbydate] < '10/1/2006' THEN 76.02
ELSE 86.88
END AS [NewAutoFee],
tblcurrentreportstatus.fieldexternalstatus,
qryfieldvouchers.mileage *
CASE
WHEN examtype LIKE 'No%' THEN newjtfee
WHEN examtype IS NULL THEN newautofee
ELSE examtype
END AS [Mileage Paid]
FROM qrymileagefee,
qryfieldvouchers,
qryjtfee,
qryfieldfee,
qryfieldvoucherpersonnel,
tblcurrentreportstatus
INNER JOIN tblfdmivouchers
ON tblcurrentreportstatus.casenum LIKE tblfdmivouchers.casenum
INNER JOIN tblcase
ON tblcurrentreportstatus.casenum LIKE tblcase.casenum
INNER JOIN tblautopsy
ON tblcurrentreportstatus.casenum LIKE tblautopsy.casenum
WHERE qryfieldvoucherpersonnel.persnum LIKE [fdmi]
AND tblcurrentreportstatus.prodstatus LIKE'Report received'
AND tblfdmivouchers.paidstatus LIKE '0'
OR tblfdmivouchers.paidstatus IS NULL
AND tblautopsy.examtype NOT LIKE 'field*'
OR qryfieldvoucherpersonnel.persnum LIKE [fdmi]
AND tblcurrentreportstatus.prodstatus LIKE 'Report received'
AND tblfdmivouchers.paidstatus LIKE '0'
OR tblfdmivouchers.paidstatus IS NULL
AND tblautopsy.examtype LIKE 'field*'
AND tblcurrentreportstatus.fieldexternalstatus = 'Report received'
Note : Assuming that qryfieldvouchers.mileage is 'Mileage' and [Case Paid] as 'Fee'
Please let me know if this helps. Hopefully i have answered you correctly.
Thanks, Hasham Niaz
Free Windows Admin Tool Kit Click here and download it now
August 13th, 2012 10:52pm
Yes!
It says completed successfully...now to put it into a report and see what happens.
Thank you SO much!Gee
August 13th, 2012 11:02pm
Hello,
I put the code into a report and it will not give me fields.
It says the fields are invalid.
It runs as no errors in SQL Management Studio, but when I call the procedure in a report it does not work.
Any help on this would be greatly appreciated.
Thank you,Gee
Free Windows Admin Tool Kit Click here and download it now
August 15th, 2012 10:32am
Hi Great !
The error is due to you have duplicate columns Alias in your query. SSMS allows it but SSRS would failed to parse it. So you have to fix the query like below;
SELECT qryfieldvoucherpersonnel.persnamelast,
qryfieldvoucherpersonnel.persnum,
tblcurrentreportstatus.casenum,
tblcurrentreportstatus.prodstatus,
tblfdmivouchers.paidstatus,
tblcase.fieldinvestigator,
tblcase.prnbydate,
tblfdmivouchers.mileage AS fdmvMileage,
tblcase.casetype,
tblautopsy.examtype,
qryfieldvouchers.mileage AS qfvMileage,
CASE
WHEN examtype LIKE 'No%' THEN newjtfee
WHEN examtype IS NULL THEN newautofee
ELSE examtype
END AS [CasePaid],
tblcase.fdmi,
tblcase.namelast,
tblcase.namefirst,
tblcase.namemiddle,
qryfieldvoucherpersonnel.persaddr,
[perscity] & ",, " & [persstate] & " " & [perszip] AS CSZ,
tblfdmivouchers.paidstatusdate,
tblcurrentreportstatus.prodstatusdate,
[persnamelast] + [persnum] AS GroupFDMI,
CASE
WHEN [prnbydate] < '10/1/2006' THEN 54.3
ELSE 65.16
END AS [NewJTFee],
CASE
WHEN [prnbydate] < '10/1/2006' THEN 76.02
WHEN [prnbydate] BETWEEN '10/1/2006' AND '8/1/2007' THEN 86.88
ELSE 103.17
END AS [NewFldFee],
CASE
WHEN [prnbydate] < '10/1/2006' THEN 76.02
ELSE 86.88
END AS [NewAutoFee],
tblcurrentreportstatus.fieldexternalstatus,
qryfieldvouchers.mileage *
CASE
WHEN examtype LIKE 'No%' THEN newjtfee
WHEN examtype IS NULL THEN newautofee
ELSE examtype
END AS [MileagePaid]
FROM qrymileagefee,
qryfieldvouchers,
qryjtfee,
qryfieldfee,
qryfieldvoucherpersonnel,
tblcurrentreportstatus
INNER JOIN tblfdmivouchers
ON tblcurrentreportstatus.casenum LIKE tblfdmivouchers.casenum
INNER JOIN tblcase
ON tblcurrentreportstatus.casenum LIKE tblcase.casenum
INNER JOIN tblautopsy
ON tblcurrentreportstatus.casenum LIKE tblautopsy.casenum
WHERE qryfieldvoucherpersonnel.persnum LIKE [fdmi]
AND tblcurrentreportstatus.prodstatus LIKE'Report received'
AND tblfdmivouchers.paidstatus LIKE '0'
OR tblfdmivouchers.paidstatus IS NULL
AND tblautopsy.examtype NOT LIKE 'field*'
OR qryfieldvoucherpersonnel.persnum LIKE [fdmi]
AND tblcurrentreportstatus.prodstatus LIKE 'Report received'
AND tblfdmivouchers.paidstatus LIKE '0'
OR tblfdmivouchers.paidstatus IS NULL
AND tblautopsy.examtype LIKE 'field*'
AND tblcurrentreportstatus.fieldexternalstatus = 'Report received'
Please let me know if this helps. Hopefully i have answered you correctly.
Thanks, Hasham Niaz
August 16th, 2012 3:51pm
Thank you for keeping with this!
It is still not working...this is the message I'm getting now:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Invalid object name 'qrymileagefee'.
------------------------------
ADDITIONAL INFORMATION:
Invalid object name 'qrymileagefee'. (Microsoft SQL Server, Error: 208)Gee
Free Windows Admin Tool Kit Click here and download it now
August 19th, 2012 2:14pm
Hi Gee,
As the error says, SQL Server is not able to find the table "qrymileagefee" in the database you
are running the query against. Can you please check if this table exists in your database?Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com
August 19th, 2012 3:09pm
Thank you for keeping with this!
It is still not working...this is the message I'm getting now:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Invalid object name 'qrymileagefee'.
------------------------------
ADDITIONAL INFORMATION:
Invalid object name 'qrymileagefee'. (Microsoft SQL Server, Error: 208)
Gee
Hi Gee,
Thanks for your posting.
Based on the error message, please make sure the table name is absolutely correct in case that the table name is case-sensitive.
Additionally, the Reporting Services Query Designer also supports stored procedure query type. Since the command lines run correctly in the SQL Server Management Studio, we can also create a stored procedure that includes the statements and then generate
a dataset by using the stored procedure.
Reference:
Graphical Query Designer User Interface
Hope this helps.
Regards,
Mike YinMike Yin
TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
August 19th, 2012 10:55pm
I tried re-writing with caps incase it was case sensitive and that did not solve the problem.
This is already a stored procedure...I'm sorry, I thought you understood that.
When I run it, to check for errors in the stored procedure, it runs and says "no errors".
When I call the procedure in the report builder, it gives me the error message.Gee
August 20th, 2012 1:55pm
Then most likely the data source in Report Builder is pointing to a different database. Can you check that?Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2012 2:19pm
Checked it...it's pointing to the correct db.
Ah, this is frustrating!
Gee
August 20th, 2012 8:14pm
I've been making some progress on this, but now this part doesn't work...can anyone help me? Case_Paid is not a field in a table, it is a text box where the amount to charge is held.
CASE
WHEN
tblAutopsy.ExamType
=
'Autopsy'
THEN
'86.88'
WHEN
tblAutopsy.ExamType
=
'Field*'
THEN
'103.17'
ELSE
tblAutopsy.ExamType
=
'65.16'
END
AS 'CASE_PAID'
Gee
Free Windows Admin Tool Kit Click here and download it now
September 8th, 2012 1:39pm