The conversion of the varchar value '2447624475' overflowed an int column. Maximum integer value exceeded

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23104174.html
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23103770.html

Hi experts I am quite confused as to how to fix this error,

I can't find the column which holds the value 2447624475.

i have looked at table columns but can't find the value. How do i increase the value range?



but i know its from this

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MACL'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] = 'ML'

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title

FROM

BM10MACELV.dbo.Projects proj LEFT JOIN C31MACL.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MACL.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

thanks
SirReadAlotAsked:
Who is Participating?
 
ptjcbConnect With a Mentor Commented:
I understand. We all work with legacy code.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
use this sp to ind out the colummn containinfg the value "2447624475 " once you find out the column, probably you can change the datatype to BIGINT


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE  PROC SearchAllTables
      @SearchStr nvarchar(100)
AS
BEGIN


      CREATE TABLE #Results (i int identity, TableName varchar(450), ColumnName nvarchar(370), ColumnValue nvarchar(3630))

      SET NOCOUNT ON

      DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
      SET  @TableName = ''
      SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

      WHILE @TableName IS NOT NULL
      BEGIN
            SET @ColumnName = ''
            SET @TableName =
            (
                  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                  FROM       INFORMATION_SCHEMA.TABLES
                  WHERE             TABLE_TYPE = 'BASE TABLE'
                        AND      QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                        AND      OBJECTPROPERTY(
                                    OBJECT_ID(
                                          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                                           ), 'IsMSShipped'
                                           ) = 0
            )

            WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            BEGIN
                  SET @ColumnName =
                  (
                        SELECT MIN(QUOTENAME(COLUMN_NAME))
                        FROM       INFORMATION_SCHEMA.COLUMNS
                        WHERE             TABLE_SCHEMA      = PARSENAME(@TableName, 2)
                              AND      TABLE_NAME      = PARSENAME(@TableName, 1)
                              AND      DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'uniqueidentifier')
                              AND      QUOTENAME(COLUMN_NAME) > @ColumnName
                  )
      
                  IF @ColumnName IS NOT NULL
                  BEGIN
                        INSERT INTO #Results
                        EXEC
                        (
                              'SELECT ''' + @TableName + ''',''' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                              FROM ' + @TableName + ' (NOLOCK) ' +
                              ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                        )
                        IF @@ROWCOUNT = 0
                              DELETE FROM #results where i = SCOPE_IDENTITY()-1
                  END
            END      
      END

      SELECT DISTINCT TableName, ColumnName, ColumnValue FROM #Results
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
 
SirReadAlotAuthor Commented:
ok.  will run that now
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
SirReadAlotAuthor Commented:
hi how do i run it,

i right mouse clicked on it, chose debug and in value i inserted
2447624475 and i got this answer @RETURN_VALUE = 0

i also tried execute but i got loads of errors

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'LIKE'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'LIKE'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'LIKE'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'LIKE'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'LIKE'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'LIKE'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'LIKE'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'LIKE'.

probably doing something wrong
0
 
SirReadAlotAuthor Commented:
can i not just change all the col types with code?
0
 
SirReadAlotAuthor Commented:
where is the #results created
0
 
SirReadAlotAuthor Commented:
the stored procedure works fine. it seems that a calculation is occurring on the fly which generates the 2447624475
value
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Did u get the column name ?
0
 
ptjcbCommented:
You are adding or equaling a varchar column to an int column. SQL Server is making an implicit conversion for you, but that overflows the int size.

For example, if you have '2447624475' in column_a. Column_b is an integer column.

SELECT *
FROM tableA
WHERE column_a = column_b

To test, SQL has to convert one column to the same data type as the other column. It works fine until it reaches the row with 2447624475. That number is larger than the integer size and the comparison fails.

SELECT *
FROM tableA
WHERE CAST(column_a as big_int) = CAST(column_b as big_int)

Explicitly converts the numbers to a larger data size.
0
 
SirReadAlotAuthor Commented:
Did u get the column name ?

no
0
 
SirReadAlotAuthor Commented:
how would i cast this sql

ALTER     PROCEDURE dbo.MP_CODA_CheckProjects

@Company varchar (500)

AS

SELECT * FROM

(

/*---------------------------------
MACE LIMITED - PAYROLL ALLOC
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MACT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] = 'ML'

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title

FROM

BM10MACELV.dbo.Projects proj LEFT JOIN C31MACT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,12)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,12)) = 1

LEFT JOIN C31MACT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,12)  = RIGHT(acc.ACCOUNT,12)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------

----------------------------------- */

0
 
ptjcbCommented:
What are the data types?
0
 
ptjcbCommented:
What are the data types for:

proj.[business unit id]
busin.[business unit id]
busin.[company id]
comp.[company id]
proj.[project nr]
acc.ACCOUNT
ent.entitykey

0
 
SirReadAlotAuthor Commented:
proj.[project nr], == int 4
proj.[project title],== varchar 100
comp.[company name],==varchar 100
comp.[associate code],== varchar 50
tab.ACCOUNT,==varchar 12
tab.title,==varchar80
tab.entitykey,==
0
 
SirReadAlotAuthor Commented:
hang on
0
 
SirReadAlotAuthor Commented:
proj.[business unit id] int 4
busin.[business unit id] int 4
busin.[company id] int 4
comp.[company id] int 4
proj.[project nr]==int Null
acc.ACCOUNT== varchar12
ent.entitykey === can't locate this
0
 
ptjcbCommented:
I believe it is the acc.account.

SELECT ISNUMERIC(max(len(acc.account))) FROM C31MACT.SQSDBA.v_Account

entity key is in
C31MACT.SQSDBA.D_ENTITY ent
0
 
SirReadAlotAuthor Commented:
ent.entitykey === varchar 25
0
 
ptjcbCommented:
ALTER     PROCEDURE dbo.MP_CODA_CheckProjects

@Company varchar (500)

AS

SELECT * FROM

(

/*---------------------------------
MACE LIMITED - PAYROLL ALLOC
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MACT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] = 'ML'

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title

FROM

BM10MACELV.dbo.Projects proj LEFT JOIN C31MACT.SQSDBA.v_Account acc ON CAST(proj.[project nr] as bigint) =  ISNUMERIC(CAST(RIGHT(acc.ACCOUNT,12)as bigint))
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,12)) = 1

LEFT JOIN C31MACT.SQSDBA.D_ENTITY ent ON CAST(RIGHT(ent.entitykey,12)as bigint)  = CAST(RIGHT(acc.ACCOUNT,12)as bigint)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]
0
 
SirReadAlotAuthor Commented:
so r u saying all the numeric lines have to be amended

AND ISNUMERIC (RIGHT(acc.ACCOUNT,12)) = 1
0
 
SirReadAlotAuthor Commented:
i will try your code
0
 
ptjcbCommented:
Depends on your data.

If your account column is all numbers then you do not need to test ISNUMERIC(). For example, an American zip code column would be 9 numbers (as long as you do not allow the dash) but if you add the Canadian postal code then you will have a mixture of letters and numbers.

I took the ISNUMERIC (RIGHT(acc.ACCOUNT,12)) = 1 that you already had and made sure that it was used in other comparisons.

By the way, I'm not sure why you are using the RIGHT(acc.account, 12) since the column is varchar(12). Usually, you would use that function to return only a portion of the column's data.

RIGHT(acc.account, 12) is the same as acc.account.



0
 
SirReadAlotAuthor Commented:
hi mate,
i will get back to u asap as i am trying to cast all these:

CREATE    PROCEDURE dbo.MP_CODA_CheckProjects

@Company varchar (500)

AS

SELECT * FROM

(

/*---------------------------------
Ml
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MACT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] = 'ML'

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title

FROM

BM10MACELV.dbo.Projects proj LEFT JOIN C31MACT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,12)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,12)) = 1

LEFT JOIN C31MACT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,12)  = RIGHT(acc.ACCOUNT,12)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
 PLUS
----------------------------------- */


SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MPLT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('MI','MP')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.Title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31MPLT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MPLT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
MCRO
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MCRT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('MC')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31MCRT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MCRT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)
WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]


-----------------------
UNION
-----------------------

/*---------------------------------
 FIXED
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MFPT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('CO')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31MFPT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MFPT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
SENSE
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31SENT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('SE')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31SENT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31SENT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
 PEOPLE
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31PEPT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('PE')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31PEPT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1



LEFT JOIN C31PEPT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
 Logistics
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32MLMD'

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MLMT.SQSDBA.V_Account acc ON proj.[project nr] = acc.account

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1


-----------------------
UNION
-----------------------

/*---------------------------------
Limited
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32MMLT'

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MMLT.SQSDBA.V_Account acc ON proj.[project nr] = acc.account

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1

-----------------------
UNION
-----------------------

/*---------------------------------
M&E
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32MMET'

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MMET.SQSDBA.V_Account acc ON proj.[project nr] = acc.account

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1

-----------------------
UNION
-----------------------

/*---------------------------------
- DUBAI
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32IAET'

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32IAET.SQSDBA.V_Account acc ON proj.[project nr] =acc.account

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1

) q

WHERE Dbase = @Company AND
NOT LEFT(q.[project nr],1) = '9'






GO
0
 
SirReadAlotAuthor Commented:

By the way, I'm not sure why you are using the RIGHT(acc.account, 12) since the column is varchar(12). Usually, you would use that function to return only a portion of the column's data.

RIGHT(acc.account, 12) is the same as acc.account.

code was written a long time ago well b/4 i jouned. just trying to fix it.
0
 
ptjcbCommented:
You do not need to cast varchar columns when they are compared to another varchar column. Look for the comparisons between varchar columns and integer columns.

That should save you time.
0
 
SirReadAlotAuthor Commented:
this is prone to mistakes,

i am fed up!! can i just change these  to bigint

proj.[business unit id] int 4
busin.[business unit id] int 4
busin.[company id] int 4
comp.[company id] int 4
proj.[project nr]==int Null
acc.ACCOUNT== varchar12
ent.entitykey === can't locate this
0
 
SirReadAlotAuthor Commented:
like this



Server: Msg 8114, Level 16, State 5, Procedure MP_CODA_CheckProjects, Line 36
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to bigint.
0
 
ptjcbCommented:
Slow down and breathe.

It depends on your data. Do not change every varchar column to bigint. That won't work because the varchar column will have letters in it.

Use CAST(column as bigint) if the column holds only numbers.

We have two areas to check.

Any column that compares a varchar value to an int value (ON statements or WHERE statements).

Because this is a UNION that means that all of the column data types MUST be the same. You can only UNION the same type of column.

Plan of attack - you know the data types.

Look at each of the SELECT .... FROM sections. Start at the first set

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MACT'
FROM

Know each of the data types for the columns.

Then look at each of the other SELECT ... FROM statements. Be sure that every column matches the data type in the first set.

When you are sure of that, then look at the ON statements and the WHERE clauses.

Look for any time that a varchar column is compared to an integer column. If there is a comparison, then add the CAST(column as bigint) to both sides of the equation.

Then to test this - instead of creating a stored procedure, just make it a query for right now.

DECLARE @Company varchar (500)
SET @Company = 'some company name'

Then add each SELECT section.

Take it piece by piece.





0
 
SirReadAlotAuthor Commented:
successful casted all , but got this error

Server: Msg 8114, Level 16, State 5, Procedure MP_CODA_CheckProjects, Line 36
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to bigint.
0
 
Snarf0001Commented:
You said at the top "but i know its from this", and listed the query.  Are you absolutely positive that it's from this one?
On every join you're doing, it's either matching int datatypes, or you've casted the varchar columns down to 5 characters, which is well under the limit for an in.

In your original post however, that lists the full queries, the bottom 4 (Logistics, Limited, M&E and INTL), you're not trimming the Account column down to 5 characters:

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32xxxx.SQSDBA.V_Account acc ON proj.[project nr] = acc.account

Which could quite possibly be throwing the error.

0
 
SirReadAlotAuthor Commented:
so how do i trim
0
 
SirReadAlotAuthor Commented:
ignore that
0
 
SirReadAlotAuthor Commented:
do u mean trim this

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT,5) = 1
0
 
Snarf0001Connect With a Mentor Commented:
no, as in:

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32xxxx.SQSDBA.V_Account acc ON proj.[project nr] = RIGHT(acc.account, 5)

as you're doing in the rest.  If you don't limit the characters in "account", then the join will try to cast the full 12 characters of account into an int for the comparison, which can lead to the overflow.

Probably not a bad idea to add the isnumeric check in there as well though.
0
 
SirReadAlotAuthor Commented:
i wonder what i left out!!!!

                       

Alter    PROCEDURE dbo.MP_CODA_CheckProjects

@Company varchar (50)

AS

SELECT * FROM

(

/*---------------------------------
ML- PAYROLL ALLOC
----------------------------------- */
SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MACT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] = 'ML'

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title

FROM

BM10MACELV.dbo.Projects proj LEFT JOIN C31MACT.SQSDBA.v_Account acc ON CAST(proj.[project nr] as bigint) =  ISNUMERIC(CAST(RIGHT(acc.ACCOUNT,5)as bigint))
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MACT.SQSDBA.D_ENTITY ent ON CAST(RIGHT(ent.entitykey,5)as bigint)  = CAST(RIGHT(acc.ACCOUNT,5)as bigint)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]
-----------------------
UNION
-----------------------

/*---------------------------------
PLUS
----------------------------------- */


SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MPLT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('MI','MP')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.Title
FROM
--BM10MACELV.dbo.Projects proj LEFT JOIN C31MPLT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
--AND acc.Ledger = 'P_INTPROJECT'
--AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1
--LEFT JOIN C31MPLT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)


BM10MACELV.dbo.Projects proj LEFT JOIN C31MPLT.SQSDBA.v_Account acc ON CAST(proj.[project nr] as bigint) =  ISNUMERIC(CAST(RIGHT(acc.ACCOUNT,5)as bigint))
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1
LEFT JOIN C31MPLT.SQSDBA.D_ENTITY ent ON CAST(RIGHT(ent.entitykey,5)as bigint)  = CAST(RIGHT(acc.ACCOUNT,5)as bigint)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
MACE MACRO
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MCRT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('MC')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
--BM10MACELV.dbo.Projects proj LEFT JOIN C31MCRT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
--AND acc.Ledger = 'P_INTPROJECT'
--AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

--LEFT JOIN C31MCRT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)
--WHERE acc.account IS NULL OR ent.EntityKey IS NULL


BM10MACELV.dbo.Projects proj LEFT JOIN C31MCRT.SQSDBA.v_Account acc ON CAST(proj.[project nr] as bigint) =  ISNUMERIC(CAST(RIGHT(acc.ACCOUNT,5)as bigint))
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MCRT.SQSDBA.D_ENTITY ent ON CAST(RIGHT(ent.entitykey,5)as bigint)  = CAST(RIGHT(acc.ACCOUNT,5)as bigint)



) TAB

ON proj.[project nr] = TAB.[Project Nr]


-----------------------
UNION
-----------------------

/*---------------------------------
 FIXED PRICE
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MFPT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('CO')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
--BM10MACELV.dbo.Projects proj LEFT JOIN C31MFPT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
--AND acc.Ledger = 'P_INTPROJECT'
--AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1
--LEFT JOIN C31MFPT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

BM10MACELV.dbo.Projects proj LEFT JOIN C31MFPT.SQSDBA.v_Account acc ON CAST(proj.[project nr] as bigint) =  ISNUMERIC(CAST(RIGHT(acc.ACCOUNT,5)as bigint))
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MFPT.SQSDBA.D_ENTITY ent ON CAST(RIGHT(ent.entitykey,5)as bigint)  = CAST(RIGHT(acc.ACCOUNT,5)as bigint)



WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
 SENSE
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31SENT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('SE')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
--BM10MACELV.dbo.Projects proj LEFT JOIN C31SENT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
--AND acc.Ledger = 'P_INTPROJECT'
--AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1
--LEFT JOIN C31SENT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)


BM10MACELV.dbo.Projects proj LEFT JOIN C31SENT.SQSDBA.v_Account acc ON CAST(proj.[project nr] as bigint) =  ISNUMERIC(CAST(RIGHT(acc.ACCOUNT,5)as bigint))
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31SENT.SQSDBA.D_ENTITY ent ON CAST(RIGHT(ent.entitykey,5)as bigint)  = CAST(RIGHT(acc.ACCOUNT,5)as bigint)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
 PEOPLE
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31PEPT'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('PE')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
--BM10MACELV.dbo.Projects proj LEFT JOIN C31PEPT.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
--AND acc.Ledger = 'P_INTPROJECT'
--AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1
--LEFT JOIN C31PEPT.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

BM10MACELV.dbo.Projects proj LEFT JOIN C31PEPT.SQSDBA.v_Account acc ON CAST(proj.[project nr] as bigint) =  ISNUMERIC(CAST(RIGHT(acc.ACCOUNT,5)as bigint))
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31PEPT.SQSDBA.D_ENTITY ent ON CAST(RIGHT(ent.entitykey,5)as bigint)  = CAST(RIGHT(acc.ACCOUNT,5)as bigint)


WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
Logistics
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32MLMD'

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MLMT.SQSDBA.V_Account acc ON proj.[project nr] = acc.account

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1


-----------------------
UNION
-----------------------

/*---------------------------------
 Limited
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32MMLT'

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MMLT.SQSDBA.V_Account acc ON proj.[project nr] = acc.account

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1

-----------------------
UNION
-----------------------

/*---------------------------------
 M&E
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32MMET'

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MMET.SQSDBA.V_Account acc ON proj.[project nr] = acc.account

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1

-----------------------
UNION
-----------------------

/*---------------------------------
 - DUBAI
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32IAET'

FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32IAET.SQSDBA.V_Account acc ON proj.[project nr] =acc.account

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1

) q

WHERE Dbase = @Company AND
NOT LEFT(q.[project nr],1) = '9'






GO
0
 
Snarf0001Commented:
This is taken from your original post, not the one above, as you did a few things unintentionally that would cause problems when you added the bigint casts in.
Also, the cast to "bigint" instead of int should not be necessary, as you're already cutting the charaters down to 5.

I only made changes to the bottom 4 select statements, commenting out the original from clause, and adding my changes


alter PROCEDURE dbo.MP_Test

@Company varchar (50)

AS

SELECT * FROM

(

/*---------------------------------
 ALLOC
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MACL'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] = 'ML'

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title

FROM

BM10MACELV.dbo.Projects proj LEFT JOIN C31MACL.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MACL.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
MACE PLUS
----------------------------------- */


SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MPLL'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('MI','MP')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.Title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31MPLL.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MPLL.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
ACRO
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MCRL'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('MC')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31MCRL.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MCRL.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)
WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]


-----------------------
UNION
-----------------------

/*---------------------------------
 FIXED PRICE
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31MFPL'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('CO')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31MFPL.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31MFPL.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
 SENSE
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31SENL'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('SE')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31SENL.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31SENL.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
 PEOPLE
----------------------------------- */

SELECT
proj.[project nr],
proj.[project title],
comp.[company name],
comp.[associate code],
tab.ACCOUNT,
tab.title,
tab.entitykey,
Dbase = 'C31PEPL'
FROM BM10MACELV.dbo.Projects proj INNER JOIN BM10MACELV.dbo.[Business units] busin ON proj.[business unit id] = busin.[business unit id]

INNER JOIN BM10MACELV.dbo.Companies comp ON busin.[company id] = comp.[company id] AND  comp.[associate code] IN ('PE')

INNER JOIN

(

SELECT DISTINCT
proj.[Project Nr],
ent.entitykey,
acc.ACCOUNT,
acc.title
FROM
BM10MACELV.dbo.Projects proj LEFT JOIN C31PEPL.SQSDBA.v_Account acc ON proj.[project nr] =  RIGHT(acc.ACCOUNT,5)
AND acc.Ledger = 'P_INTPROJECT'
AND ISNUMERIC (RIGHT(acc.ACCOUNT,5)) = 1

LEFT JOIN C31PEPL.SQSDBA.D_ENTITY ent ON RIGHT(ent.entitykey,5)  = RIGHT(acc.ACCOUNT,5)

WHERE acc.account IS NULL OR ent.EntityKey IS NULL

) TAB

ON proj.[project nr] = TAB.[Project Nr]

-----------------------
UNION
-----------------------

/*---------------------------------
 Logistics
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32MLML'

--FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MLML.SQSDBA.V_Account acc ON proj.[project nr] = acc.account
FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MLML.SQSDBA.V_Account acc ON proj.[project nr] = RIGHT(acc.account, 5)

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1


-----------------------
UNION
-----------------------

/*---------------------------------
 Limited
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32MMLL'

--FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MMLL.SQSDBA.V_Account acc ON proj.[project nr] = acc.account
FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MMLL.SQSDBA.V_Account acc ON proj.[project nr] = RIGHT(acc.account, 5)

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1

-----------------------
UNION
-----------------------

/*---------------------------------
 M&E
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32MMEL'

--FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MMEL.SQSDBA.V_Account acc ON proj.[project nr] = acc.account
FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32MMEL.SQSDBA.V_Account acc ON proj.[project nr] = RIGHT(acc.account, 5)

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1

-----------------------
UNION
-----------------------

/*---------------------------------
 INTL -
----------------------------------- */

SELECT
proj.[project nr],
proj.[Project title],
CompanyName = 'N/A' ,
[associate code] = 'N/A',
acc.account [CODA Project No],
acc.title As [CODA Project Name],
EntityKey = 'N/A',
dbase = 'C32IAEL'

--FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32IAEL.SQSDBA.V_Account acc ON proj.[project nr] =acc.account
FROM BM10MACELV.dbo.Projects proj LEFT JOIN C32IAEL.SQSDBA.V_Account acc ON proj.[project nr] = RIGHT(acc.account, 5)

WHERE acc.ledger = 'P_INTPROJECT' AND ISNUMERIC (acc.ACCOUNT) = 1

) q

WHERE Dbase = @Company AND
NOT LEFT(q.[project nr],1) = '9'

0
 
SirReadAlotAuthor Commented:
guys,

thanks for all you help
0
All Courses

From novice to tech pro — start learning today.