View : Error converting data type nvarchar to bigint.

(0) Share

Report

Posted on by Tony zhong

901

Hi,

During View creation, while Build and Synchronized has no error, when I try to run directly from SSMS, I get this error:

"Error converting data type nvarchar to bigint"

This view is consist of another 2 views which I just added 1 new field.

1. In the 1st View, I added new method that searching for Inventtrans. physical reserved qty like below:

public static server str PhysicalReserved()
    {
        DictView    dictView      = new DictView(tableNum(MyCurrentPhysicalFact));
        str         Qty           = dictView.computedColumnString('InventTrans', 'Qty', FieldNameGenerationMode::FieldList);
        str         StatusIssue = dictView.computedColumnString('InventTrans', 'StatusIssue', FieldNameGenerationMode::FieldList);

        return strFmt('CASE WHEN %1 = 4 THEN ABS(%2) ELSE 0 END', StatusIssue, Qty);
    }

.Then add this Viewmethod to a new field : ViewComputedColumnReal

2. In the 2nd View actually there is no value, since the same method I only put '0' like below:

 public static server str PhysicalReserved()
    {
        return strFmt('0');
    }

Same as no1, added new field which is ViewComputedColumnReal.

If I run this two view independently, there is no error, but when run query of the top view which is the merge of this two, it will produce that error.

Anyone know what is the problem ? and how I can resolve this ?

Thanks

I have the same question (0)

All responses (15)

Answers (1)

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi Tony zhong,

Are the field EDTs the same in both the views?

Was this reply helpful? Yes No
Tony zhong 901 on at

Like (0)

Report

Hi Gunjan,

Is the EDT the problem ? Because as mentioned, the calculated column, one has logic in it and another is just strfmt('0') and both is 'str'. When added into the field, both are ViewComputedColumnReal.

And after restore (since it is an existing view) and the field listed in the topmost View, I just drag the new field PhysicalReserved from the 1st datasource as usual.

Unless there is other thing I don't know, what EDT actually you are referring to ?

Thanks,

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi Tony,

Yes. That's what I was asking - if both the fields are ViewComputedColumnReal. Are you trying to select all the records from the view in SSMS when you get the error?

Was this reply helpful? Yes No
Tony zhong 901 on at

Like (0)

Report

Yes, select all from the "topmost" view which is the merge to the two sub view. I tried to reduced it, it seems error after TOP 1900, TOP 1901 already returns error, has it related on something somehow ?

Another thing is, this View is meant for Aggr. Measure, it's a FACT table. So I'm using it in Data Entity and SUM this field. While there is no error, Build and Sync succeeded, also Refresh entity store, I noticed the field value became RecId. But again, the underlying view which is the base of it (the two view), ran it in SSMS independently is fine, the value is correct.

But I think this will be my next investigation because even the View already having problem.

Thanks,

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

Like (0)

Report

You seem to be talking about three views. Which ones of them are failing? Are the other two relevant to the problem, if your computed column is using just InventTrans table and nothing else?

Look at T-SQL definition of the problematic view in SSMS. If you don't see any problem there, please share the code with us.

Was this reply helpful? Yes No
Tony zhong 901 on at

Like (0)

Report

Hi Martin,

Yes, as mentioned, the one that failing is the topmost View whereby it is the one that merge the other two.

I think I know the cause now, but I don't know how to resolve it.

The cause is because the two views (or shall we say "subview") having different structure. But this cannot help because it is coming from different query and in one of it (I called it ViewA) because it is join and group by some fields, some more fields has to appear in that view, while the other one (ViewB) there is only coming from single table with no group, so there are fewer fields.

So to overcome this, the one with more fields, I created a new view (ViewC) that only consist of the fields that exactly the same as as ViewB.

Now the topmost View, which is the union, the data source is ViewC and ViewB, which has exactly the same field. And I have arranged it like this :

But even after this, after Build and Sync. when I look at the underlying query that create the View (the topmost view), the 2nd view (ViewB) , the order field is messed up, the datasource field is not with the correct field in the target view.

Is there a way to fix this ?

Thanks,

Was this reply helpful? Yes No
Tony zhong 901 on at

Like (0)

Report

It looks like the "misplaced" field on the 2nd 'subview' stays on, even after I delete the fields (not working) and then after, delete the View it self (the topmost view / the merging view).

Any idea would be appreciated.

Thanks

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

Like (0)

Report

How did you create a view with two root data sources? I don't seem to be able to do that. I would have to use an AOT query.

By the way, that's what I would recommend to you anyway. Create an AOT view with UNION and test it. When it works, use it as a data source of a view.

Was this reply helpful? Yes No
Tony zhong 901 on at

Like (0)

Report

Hi Martin,

Yes, it is coming from AOT query. So I have the AOT Query that combine the two view, then just specify the query name in my topmost view's property.

And for the fields, I only drag all the fields from 1st datasource to the node : Fields. I guess this is the usual way of doing it.

Do you think the AOT query is the cause ? I think I did tried to delete, not the AOT query, but the 2 datasource in it and add it again, and it didn't work. But for the topmost view, I did delete and re-create again.

I'm quite confuse of what do you mean by " Create an AOT view with UNION and test it. When it works, use it as a data source of a view.", since this is all AOT view and the topmost is a UNION. Do you mean to create a new one with different name? It's worth to try because I cannot find anything false here.

Thanks,

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

Like (0)

Report

It's strange - if I use a query as a source of a view, I don't see the Data Sources node. I see the name of the query there.

Regarding the other part, I meant testing the query sepately, instead of testing it throught the view. If you're testing multiple things at once, you don't know which one is causing the problem

Was this reply helpful? Yes No