Help resolve "Error converting data type nvarchar to BIGINT"
Application Type
Traditional Web

Kindly help me resolve the SQL error presented below:

I wrote an SQL statement in my application,

the SQL statement involves entity attribute with  identifier data  type,

it is returning the error stated below:

"Error converting data type varchar to BIGINT"

please guide me on how to resolve this issue.


The SQL is presently stated as:

SELECT SUM ({Sale}.[Quantity])
FROM {Sale}
WHERE {Sale}.[Itemid] = 'A4 BW Photostat

Note that itemid is identifier data type


Help please correct the statement above and any other thing i need to include to make it work. Thanks



 

mvp_badge
MVP
Solution

Hi Harrison,


The attribute Itemid from entity Sale is an integer or identifier and therefore you cannot compare it to a text.

You need to compare it to an integer or identifier.


Besides, you can also use an aggregate for this query instead of an Advanced SQL:



Kind Regards,
João

It is not working for me. I need more explanation please. Thanks

Champion

Hi Harrison,

It's weird. João's answer should be work. 

Could share your Oml file?

Kind regards,


The Oml file is larger than the 4mb maximum limit accepted in this forum. I can send the oml file to your email. Please help me resolve this issue. Thanks

I just sent the oml file to you via your email address. Thank you so much

Champion

Thank you, I received the oml file.

For the first issue,

I still think João's answer is right. You cant compare the Identifier(Long Integer) type to text type.

WHERE {Sale}.[Itemid] = 'A4 BW Photostat'

You have to get the itemid of  'A4 BW Photostat', or join Item Entity use Name property instead.


For the second  issue,

I don't know where your unit price defined,  if the unit price in the Item entity, then when you select the item you can retrieve the unit price from the entity.

Kind regards,

Hi, could you kindly help me correct issue in the oml file i sent to you and email back to me so that i can proceed. Help me do the SQL statement in the oml file please. Thank alot

Champion

Hi Harrison,

Because I am using a personal environment, I can't publish your OML file. 

If I am not misunderstanding your issue The Sql should be like this.  

SELECT SUM ({Sale}.[Quantity])
FROM {Sale}
WHERE {Sale}.[Name] = 'A4 BW Photostat'

Kind regards

Thanks Tom, Yes. This works but i wanted to use the drop down list (combo box), which is identifier data type to make selection and analysis much easier for the user. Thanks for your efforts. 

mvp_badge
MVP

Hello.

João already explained why you are receiving the error. 

In your image, you compare the attribute ItemId with a text; the ItemId is an Item Identifier. 

So, what I will ask you is:

a) If the value you are comparing is "fixed", you can use a subquery (this is just an example, I don't know your entities or attributes):

SELECT SUM ({Sale}.[Quantity])
FROM {Sale}
WHERE {Sale}.[Itemid] = (SELECT TOP 1 Id FROM Item WHERE Desription = 'A4 BW Photostat');

b) The value will come from outside the SQL Tool:
- Add a query parameter to the query tool, of type, for example, ItemId, and change your query to:

SELECT SUM ({Sale}.[Quantity])
FROM {Sale}
WHERE {Sale}.[Itemid] = @ItemId

Then you set the value you want to pass to the query from outside.
Anyway, if your query is simple as you show, I recommend using an aggregate instead.

I hope this helps.


Thanks to everyone that responded to my question. Your inputs are appreciated. For the solution, I used aggregates to achieve the desired result as suggested. Thanks again
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.