Varchar Error in SQL Query

General PHP Help
#1

I am attempting to sum a field from a MS SQL table:-

$BudgetedHours = array();

foreach($WorkPackCharVarList as $ThisSubJob)
{	
echo "<p>$ThisSubJob</p>";
$sql="
		SELECT SUM(Quantity)
		AS BudgetHours
		FROM [Workbench].[dbo].[EstimateLines]
		WHERE  $ThisSubJob = [EstimateLines].[JobCode]";
		$stmt = sqlsrv_query( $conn, $sql);
		if( $stmt === false)
			{
				die( print_r( sqlsrv_errors(), true) );
			}
		$row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC);
		$BudgetedHours[] = $row[BudgetHours];
		echo "<p>BudgetHours = $row[BudgetHours]</p>";
		sqlsrv_free_stmt( $stmt);

This gives a SQL error:-
Array ( [0] => Array ( [0] => 22003 [SQLSTATE] => 22003 [1] => 248 [code] => 248 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The conversion of the varchar value ‘01910002003001’ overflowed an int column. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The conversion of the varchar value ‘01910002003001’ overflowed an int column. ) )

The array $WorkPackCharVarList has been filled using a strval() of some integers, all shorter than 20 characters.
The field EstimateLines.JobCode is varchar(20)
The SQL query works fine when pasted into MSSQL. with $ThisSubJob replaced by ‘01910002003001’ .

#2

Never heard that a computer cannot calculate with string (text) variables?
That is why you cannot SUM() a varchar field. You should change the fieldtype to an int, float or decimal.

#3

No, the Quantity field is a number. As I said, the SQL query works fine in the Management Studio.

#4

i think i misunderstood your question. What happens if you make it a BIGINT type?

#5

The database I am selecting from is a proprietary one and I can’t change the structure. I’m wondering what the difference is between a SQL SELECT which works fine in Management Studio, and then gives this error in a php file. The only change is the replacement of $ThisSubJob with ‘01910002003001’.

#6

Hmm for sure that $ThisSubJob is converted to an int but the number 01910002003001 it to big for an integer. But i am not sure how you want to compare: as numbers or as strings?

It looks like you have to add quotes around ThisSubJob

WHERE '$ThisSubJob' = [EstimateLines].[JobCode]
#7

Thank you, quite right, the quotes did the trick.

#8

Appreciated and many thanks.

#9

gladd it works :grinning:

#10

This looks like a bad query to begin with.

SELECT 
    SUM(Quantity) AS BudgetHours 
FROM 
    [Workbench].[dbo].[EstimateLines] 
WHERE 
    01910002003001 = [EstimateLines].[JobCode]

That is kurfluffled! What are you actually trying to limit the question to?

Sponsor our Newsletter | Privacy Policy | Terms of Service