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
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
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
Ok, My email address is yingzhe.zhao@bridgeworld.co.jp
Kind regards
I just sent the oml file to you via your email address. Thank you so much
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.
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
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'
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.
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.