October 23, 2008 at 3:15 am
I have a table with accounting posts by "job number" (workstage), taken from our accounting system. In the past, these have sometimes been alphanumeric but now, within the current "company" within the finance system, are entirely numeric. They are still stored as varchars but in other systems they are ints.
I have constructed the following view to enable someone to see invoices and credit notes against a particular job:
CREATE VIEW dbo.vw_Job_Invoices_And_Credits
AS
SELECT CAST(PCTX.Workstage AS INT) AS JobNumber, PCTX.FullDocID, (PCTX.BaseAmount * -1) AS TotalInvoiced
FROM OA_PC_Transactions PCTX
WHERE PCTX.Company = 30 AND PCTX.Workstage LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' AND PCTX.LedgerCode = 'SL' AND PCTX.FullDocID NOT LIKE 'PRRJ%'
GO
Running that on its own is fine. However, if you perform the following:
SELECT * FROM vw_Job_Invoices_And_Credits WHERE JobNumber = 503875
You get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'XXX/DIRS/XX' to a column of data type int.
I can't see what's going wrong and can't help but think I've missed something obvious. I have:
(a) restricted workstages to six-digit numeric values only, and
(b) restricted to company 30 which only contains numeric workstages anyway
If I perform this:
SELECT * FROM vw_Job_Invoices_And_Credits WHERE JobNumber LIKE 'X%'
I get no results.
Any ideas?
October 23, 2008 at 3:58 am
If jobNumber is a var char then this code will not work at all
SELECT * FROM vw_Job_Invoices_And_Credits WHERE JobNumber = 503875
You will need to add quotes to the Jobnumber
SELECT * FROM vw_Job_Invoices_And_Credits WHERE JobNumber = '503875'
October 23, 2008 at 4:01 am
steveb (10/23/2008)
If jobNumber is a var char then this code will not work at all
SELECT * FROM vw_Job_Invoices_And_Credits WHERE JobNumber = 503875
You will need to add quotes to the Jobnumber
SELECT * FROM vw_Job_Invoices_And_Credits WHERE JobNumber = '503875'
The JobNumber is CAST as an INT in the view, so when selecting from a view, you're seeing an INT, not a VARCHAR. Adding quotes to the JobNumber returns the same error (just tried it).
October 27, 2008 at 3:52 am
Does anyone else have any thoughts on this? Could it be a bug or something in the nuances of how the query is constructed for execution?
October 27, 2008 at 6:38 am
I had this problem before when pulling data from another system
If I remember correctly..
This is a Nuance of how the view is executed when you call it in a from clause....
I am pretty sure if you execute
SELECT CAST(PCTX.Workstage AS INT) AS JobNumber, PCTX.FullDocID, (PCTX.BaseAmount * -1) AS TotalInvoiced
FROM OA_PC_Transactions PCTX
WHERE
/*
PCTX.Company = 30 AND PCTX.Workstage LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' AND PCTX.LedgerCode = 'SL' AND PCTX.FullDocID NOT LIKE 'PRRJ%'
*/
PCTX.Workstage like 'X%
you will find the record that causes the problem.
When the query against the view executes SQL tries to convert all Workstage numbers to int as in your view then only limit the results with the wher clause......
None is this is fact, just off the top of my head based on my observations.....
October 27, 2008 at 6:52 am
Okay so, finding the system and code was easier than I thought.
To solve the problem, I used a derived table in my view that limits the result set, And then do the convertions..i.e
SELECT
CAST(Workstage AS INT) AS JobNumber,
FullDocID,
TotalInvoiced
From(
SELECT
PCTX.Workstage,
PCTX.FullDocID,
(PCTX.BaseAmount * -1) AS TotalInvoiced
FROM OA_PC_Transactions PCTX
WHERE PCTX.Company = 30
AND PCTX.Workstage LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'
AND PCTX.LedgerCode = 'SL'
AND PCTX.FullDocID NOT LIKE 'PRRJ%'
) as Src
That was the solution for me.
October 28, 2008 at 5:11 am
Thanks. I thought that might work, but it didn't! I get exactly the same error. :crazy:
I know where the row is that causes the problem - it only exists where Company = 10. Which is why I can't see why it's causing a problem here and think it must be something to do with how the query is executed.
If I SELECT * from the view, I get a full result set with no errors and it's all numeric; that row from Company = 10 doesn't come through. It's only when I put a restriction on JobNumber that I get the error.
Here is the new SQL, based on your model:
CREATE VIEW dbo.vw_Job_Invoices_And_Credits
AS
SELECT CAST(Workstage AS INT) AS JobNumber, FullDocID, TotalInvoiced
FROM (
SELECT PCTX.Workstage, PCTX.FullDocID, (PCTX.BaseAmount * -1) AS TotalInvoiced
FROM OA_PC_Transactions PCTX
WHERE PCTX.Company = 30 AND PCTX.Workstage LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' AND PCTX.LedgerCode = 'SL' AND PCTX.FullDocID NOT LIKE 'PRRJ%'
) AS SUB
October 29, 2008 at 6:12 am
First , My apologies for the late response...
The only thing I could suggest is to Add a case statement to your Select i.e
Case IsNumeric( Colname) When 1 Then Cast( colName as int) Else Null End
That was the other thing I added to my solution.
Note the recent post about IsNumeric returning true if the value can be converted to Any one of the numeric data type
October 31, 2008 at 4:03 am
Thanks. Modified it to this and it worked. 🙂
SELECT CAST(COALESCE(Workstage,0) AS INT) AS JobNumber, FullDocID, TotalInvoiced
FROM (
SELECT CASE WHEN PCTX.Workstage LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' THEN PCTX.Workstage ELSE NULL END AS Workstage, PCTX.FullDocID, (PCTX.BaseAmount * -1) AS TotalInvoiced
FROM OA_PC_Transactions PCTX
WHERE PCTX.Company = 30 AND PCTX.Workstage LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' AND PCTX.LedgerCode = 'SL' AND PCTX.FullDocID NOT LIKE 'PRRJ%'
) AS SUB
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply