Update record using advanced sql

Update record using advanced sql

  


Hi,  

I am trying to update a record and finding weird behavior. all the columns have currency as datatype.  i found a strange behavior during the update it is considered as nvarchar.  assuming that it will convert to money format and update the record

for eg:  if   [BudgetLaborCost] has value of 4000 and [MatBudgetCost] has value 0. the value of [TotBudgetCost] is updated as 0

and

 if   [BudgetLaborCost] has value of 4000 and [MatBudgetCost] has value 20. the value of [TotBudgetCost] is updated as 4020

Can any of please let me know how to fix this issue.

i want the output for case 1 to be displayed as 4000 and case 2 as 4020

SyntaxEditor Code Snippet

update {Tbl_T_ProjectSummary} set 
     {Tbl_T_ProjectSummary}.[TotBudgetCost]= cast({Tbl_T_ProjectSummary}.[BudgetLaborCost] as money) + cast({Tbl_T_ProjectSummary}.[MatBudgetCost] as money)  
where 
{Tbl_T_ProjectSummary}.[ProjectID] = @ProjectID

Hi Ryoto,

Your code snippet is for your second case only, right?

According to documentation and my experience, the platform stores Currency attributes as Decimal at the Database level. You shouldn't need a cast at all. Do you have access to the database itself? can you check what is the corresponding column's data type? The platform isn't executing the UPDATE statement, the Database Engine is.


Hi Martins,

Unfortunately i do not have access to Database server.  my worry is case one

for eg: A =4

b=0

c = a + b

in my case it is displayed as 0  but it should be 4

i do not have issue for second case.

A=4, b =2 

c = A + b = 4 + 2 = 6.  This is absolutely correct.

i am not able to understand why it is displayed as 0 instead of 4


Any help would be great.


Regards

Ryoto

Hi Ryoto,

could not replicate the behaviour you describe... please find attached sample that in my personal environment works as expected...

Hi Ryoto,

I already see many times this behavior.
It happens when the value in database is NULL.
When you perform an expression and one of the fields is NULL, the entire expression will also be NULL.

Probably you are saving or dealing with fields that have NULL, and in this case, when you bring them to the OutSystems, you will see 0, but they are NULL indeed.

If you are using SQL Server, you can try the following to see if it works:

UPDATE {Tbl_T_ProjectSummary} 
SET
    {Tbl_T_ProjectSummary}.[TotBudgetCost] = 
        (CASE WHEN {Tbl_T_ProjectSummary}.[BudgetLaborCost] IS NULL THEN 0 ELSE {Tbl_T_ProjectSummary}.[BudgetLaborCost] END)
         + 
        (CASE WHEN {Tbl_T_ProjectSummary}.[MatBudgetCost] IS NULL THEN 0 ELSE {Tbl_T_ProjectSummary}.[MatBudgetCost] END) 
WHERE
    {Tbl_T_ProjectSummary}.[ProjectID] = @ProjectID

This will guarantee that if one of the fields is NULL, you will use 0 instead, and the update will work as expected.
I removed the castings as I don't think they are necessary.

Cheers,
Eduardo Jauch

Hi Ryoto,

Still problem exists..??

Please also share screenshot because I think it will work if you put this simple query also.. No need to do cast.


update {Tbl_T_ProjectSummary} set 
     {Tbl_T_ProjectSummary}.[TotBudgetCost]= (({Tbl_T_ProjectSummary}.[BudgetLaborCost])  + ({Tbl_T_ProjectSummary}.[MatBudgetCost]))  
where 
{Tbl_T_ProjectSummary}.[ProjectID] = @ProjectID


Regards,

Rajat Agrawal

tbh, why even have that column in the first place, it's a simple calculated column?


unless you work with heavy data-reporting (but then it would not be in the same entoty, but a total different one)


J. wrote:

tbh, why even have that column in the first place, it's a simple calculated column?


unless you work with heavy data-reporting (but then it would not be in the same entoty, but a total different one)


i agree with you.  its a calculated column.  initially i too didn't want to save it in database. As described in the earlier messages i too feel that it is stored as null and it is not able to sum.  if you can provide some idea to fix it that would be great help.


Thanks in advance.

Ryoto


Eduardo Jauch wrote:

Hi Ryoto,

I already see many times this behavior.
It happens when the value in database is NULL.
When you perform an expression and one of the fields is NULL, the entire expression will also be NULL.

Probably you are saving or dealing with fields that have NULL, and in this case, when you bring them to the OutSystems, you will see 0, but they are NULL indeed.

If you are using SQL Server, you can try the following to see if it works:

UPDATE {Tbl_T_ProjectSummary} 
SET
    {Tbl_T_ProjectSummary}.[TotBudgetCost] = 
        (CASE WHEN {Tbl_T_ProjectSummary}.[BudgetLaborCost] IS NULL THEN 0 ELSE {Tbl_T_ProjectSummary}.[BudgetLaborCost] END)
         + 
        (CASE WHEN {Tbl_T_ProjectSummary}.[MatBudgetCost] IS NULL THEN 0 ELSE {Tbl_T_ProjectSummary}.[MatBudgetCost] END) 
WHERE
    {Tbl_T_ProjectSummary}.[ProjectID] = @ProjectID

This will guarantee that if one of the fields is NULL, you will use 0 instead, and the update will work as expected.
I removed the castings as I don't think they are necessary.

Cheers,
Eduardo Jauch

i was able to fix the issue with your solution.  Thanx for your quick help. incase i dont want to save in database as it is calculated column can you please throw some light how to fix that issue.


Solution

Hi Ryoto,

I see your entities are actually External Entities provided by an Extension. This is relevant info to help pinpoint what the issue is (and Eduardo was quite smart identifying it).

If you just want to calculate the column, you'd do it on the Aggregates/SQl statements that read that information from the Database, using the same logic Eduardo suggested, just in a SELECT statement. The following example would return all attributes of the Tbl_T_ProjectSummary Entity plus an extra Currency attribute with the calculated value:

SELECT {Tbl_T_ProjectSummary}.*,
       (CASE WHEN {Tbl_T_ProjectSummary}.[BudgetLaborCost] IS NULL THEN 0 ELSE {Tbl_T_ProjectSummary}.[BudgetLaborCost] END) + (CASE WHEN {Tbl_T_ProjectSummary}.[MatBudgetCost] IS NULL THEN 0 ELSE {Tbl_T_ProjectSummary}.[MatBudgetCost] END) 
FROM {Tbl_T_ProjectSummary}
WHERE
    {Tbl_T_ProjectSummary}.[ProjectID] = @ProjectID

Keep in mind that you will need to make sure the Output Entities/Structures defined have the same number of attributes/data types and in the same order as the columns that are returned by the SELECT.

Solution