Returning a specific value in computed column if expression returns null

(0) Share

Report

Posted on by DELDYN

484

Hi,

I've created a computed method called Field1.
As you can see it contains isNull. What I want is that if isNull was true -- then I want to return the current Field1 value in table1 a word 'Dummy value'

So for example if the return value is null from the select statement and current Field1 value is equal to "A11"
then I want to return this string "A11Dummy value"

I tried to achieve this by doing using this in the method: SysComputedColumn::comparisonLiteral(View1::CurrentField1()) -- where I created a method called CurrentField1 but it didn't work

    public static server str Field1()
    {

        return  strFmt('ISNULL((select top 1 Field1 from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.Field2 != %3), %4)'
            , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Partition))
            , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, DataAreaId))
            , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Field2))
             SysComputedColumn::comparisonLiteral(View1::CurrentField1())
             );

    }


 public static server str CurrentField1()
    {

        str x = SysComputedColumn::returnField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Field1));
        x = x 'DummyValue';

        return  x;

    }

Can someone please help on how should I amend this code?

I have the same question (0)

All responses (20)

Answers (0)

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

Please give us more information about your problem than mere . Start by showing us SQL code returned from your method and explaining what result you get when you execute this code.

Was this reply helpful? Yes No
DELDYN 484 on at

Like (0)

Report

Hi Martin,

I'm getting "T1.Field1Dummy value"

Instead of the actual field value..which is "A11Dummy value" in this case

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

You make your life very difficult by ignoring SQL code generated by your method. It's actually the key thing; it's the code that defines what will the database server produce.

To see it, you can execute your method and check the return value, or you can look at the view definition in SQL Server Management Studio. The latter is better, because it includes the cast to the computed column type (that will causes if it's not set up correctly) and you can also easily test your code there.

What you're getting is indeed what you code does. You take the field identifier (T1.Field1) and add DummyValue string to it (at line 18). Adding strings together isn't the same thing as generating SQL code for adding strings together.

You'd need something like strFmt('%1 + "DummyValue"', x).

Design your SQL code first and then make sure that your X++ code generates right SQL code.

Was this reply helpful? Yes No
DELDYN 484 on at

Like (0)

Report
Hi Martin,

I tried this

public static server str Field1Value() { return strFmt('ISNULL((select top 1 Field1 from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.Field2 != %3), %4)' , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Partition)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, DataAreaId)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Field2)) SysComputedColumn::comparisonLiteral(View1::CurrentField1()) ); } public static server str CurrentField1() { str x = strFmt('%1 "DummyValue"',SysComputedColumn::returnField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Field1))); return x; }

and this is the sql i got

SELECT Field1, Field2, Field3, Field4, DATAAREAID, PARTITION, RECID , CAST(ISNULL ((SELECT TOP (1) Field1 FROM dbo.TABLE1 AS table1 WHERE (PARTITION = T1.PARTITION) AND (DATAAREAID = T1.DATAAREAID) AND (Field2 <> T1.Field2)) , N'T1.Field1 "DummyValue"') AS NVARCHAR(20)) AS Field1Value FROM dbo.TABLE1 AS T1

However, this is the sql that i want -- how can i amend x to get this result??

SELECT Field1, Field2, Field3, Field4, DATAAREAID, PARTITION, RECID ,CAST(ISNULL ( ( SELECT TOP (1) Field1 FROM dbo.TABLE1 AS table1 WHERE (PARTITION = T1.PARTITION) AND (DATAAREAID = T1.DATAAREAID) AND (Field2 <> T1.Field2) ), ( ( SELECT TOP (1) Field1 'Dummyvalue' FROM dbo.TABLE1 AS table1 WHERE (PARTITION = T1.PARTITION) AND (DATAAREAID = T1.DATAAREAID) AND (Field1 = T1.Field1) ) ) ) AS NVARCHAR(20) ) AS Field1Value FROM dbo.TABLE1 AS T1

I tried to replace currentField1 method with this, but i'm getting invalid unicode strings error
public static server str CurrentField1() { return strFmt('select top 1 Field1 "DummyValue" from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.Field1 = %3' , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Partition)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, DataAreaId)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Field1)) ); }

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

The bug in Field1Value() is at line 8, where you say that value will be used a string literal. You shouldn't use comparisonLiteral() if you don't want a fixed string value.

Was this reply helpful? Yes No
DELDYN 484 on at

Like (0)

Report

Hi Martin,

What shall I use instead of comparison literal? -- is this why I'm getting error in unicode string?

And in my last comment, I showed you two codes for method CurrentField1..which one is the correct one?

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

No, of course that you don't want a comparison literal. That would be still the same bug. A literal means returning fixed text, not returning an expression for getting a field value.

Sorry, I don't know what you mean by the last paragraph.

Was this reply helpful? Yes No
DELDYN 484 on at

Like (0)

Report
Hi Martin,

Can you please let me know what shall i use instead of this comparison literal?
SysComputedColumn::comparisonLiteral(View1::CurrentField1())

Regarding last paragraph, I meant I tried two way for method CurrentField1, as i mentioned in a previous comment

one was that
public static server str CurrentField1() { str x = strFmt('%1 "DummyValue"',SysComputedColumn::returnField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Field1))); return x; }

and then i thought that this might be better to get me closer to the required SQL -- but I'm getting unicode string error -- if this is better than the first way , how can i fix it?
public static server str CurrentField1() { return strFmt('select top 1 Field1 "DummyValue" from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.Field1 = %3' , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Partition)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, DataAreaId)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Field1)) ); }

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

Just remove the call of SysComputedColumn::comparisonLiteral() and use CurrentField1() directly. comparisonLiteral() is the reason why you're gettring N'T1.Field1 + "DummyValue"' instead of T1.Field1 + "DummyValue". In general. you need think about what SQL code you want to get and adjust your code to do it.

Taking Partition and DataAreaId into account is the right approach, but won't fix the problem caused by your usage of comparisonLiteral().

Was this reply helpful? Yes No
DELDYN 484 on at

Like (0)

Report
Hi Martin,

I already know what's the SQL, but i'm unable represent in x .

This is the required SQL
SELECT Field1, Field2, Field3, Field4, DATAAREAID, PARTITION, RECID ,CAST(ISNULL ( ( SELECT TOP (1) Field1 FROM dbo.TABLE1 AS table1 WHERE (PARTITION = T1.PARTITION) AND (DATAAREAID = T1.DATAAREAID) AND (Field2 <> T1.Field2) ), ( ( SELECT TOP (1) Field1 'Dummyvalue' FROM dbo.TABLE1 AS table1 WHERE (PARTITION = T1.PARTITION) AND (DATAAREAID = T1.DATAAREAID) AND (Field1 = T1.Field1) ) ) )

I tried to use CurrentField1 directly as you suggested but i'm getting this error when syncing

If the view/entity has a computed column, validate the T-SQL generated by the computed column's method.' Exception: System.InvalidOperationException: Database execution failed: Incorrect syntax near the keyword 'select'.

goshoom Here's my x

public static server str Field1Value() { return strFmt('ISNULL((select top 1 Field1 from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.Field2 != %3), %4)' , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Partition)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, DataAreaId)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Field2)) , View1::CurrentField1() ); } public static server str CurrentField1() { return strFmt('select top 1 Field1 "DummyValue" from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.Field1 = %3' , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Partition)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, DataAreaId)) , SysComputedColumn::comparisonField(tableStr(View1), identifierStr(Table1), fieldStr(Table1, Field1)) ); }

Was this reply helpful? Yes No