Skip to main content
Expert
Jul 12, 2023 7:42 PM UTC
Solved

Create View using Extender rule

Hi All - I am trying to create a View using a Extender BR and It is compainling without any issue but when I try to execute it am getting the below error. Any help would be appreciated.

 

 

 Dim vSQL As New Text.StringBuilder()
			   vSQL.AppendLine("CREATE View dbo.v_stageData AS
SELECT
WFP.ProfileName As WorkFLowProfileName,
S.Sn AS SrcScenario,
T.SnT AS TgtScenario,
S.Tm AS SrcTime,
T.TmT AS TgtTime, 
S.Et AS SrcEntity,
T.EtT AS TgtEntity,
S.Ac AS SrcAccount,
T.AcT AS TgtAccount,
S.Fw AS SrcFlow,
T.FwT AS TgtFlow,
S.Og AS SrcOrgin,
T.OgT AS TgtOrgin,
S.Cn AS SrcConsol,
T.CnT AS TgtConsol,
S.Ic AS SrcICP,
T.IcT AS TgtICP,
S.U1 AS SrcU1,
T.U1T AS TgtU1,
S.U2 AS SrcU2,
T.U2T AS TgtU2,
S.U3 AS SrcU3,
T.U3T AS TgtU3,
CAST(S.Am AS DECIMAL(18,2)) AS Amount
FROM StageSourceData S  
INNER JOIN StageTargetData T ON  S.Wfk=T.Wfk AND S.WtK = T.WtK AND S.Ri = T.Ri
INNER JOIN WorkflowProfileHierarchy WFP ON WFP.ProfileKey = S.Wfk
WHERE T.SnT = 'Actual'
AND T.TmT = '2023M1'
AND WFP.ProfileName = 'USA_DataLoad'")
			   
			   Using aDbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
			   Brapi.Database.ExecuteSql(aDbConn,aDbConn.ToString,False)
			   
		   End Using

 

 

 

Kris_0-1689190843133.png

 

HenningAnswer
Employee - Advisor
Jul 13, 2023 9:54 AM UTC

I believe you have an error in your execution command:

 Brapi.Database.ExecuteSql(aDbConn,aDbConn.ToString,False)

 

This should be the SQL command you wish to execute I believe

Henning_0-1689241858470.png

I.e. you need to use the (in your case) vSQL in the second position:

Brapi.Database.ExecuteSql(aDbConn, vSQL, False)

 

If that does not work, write your vSQL into the error log and analyze it there in order to see the final string that is supposed to be executed:
brapi.ErrorLog.LogMessage(si,"vSQL script: See details below.", vSQL)

5 replies

Newcomer
Jul 13, 2023 4:54 AM UTC

Double check your SQL connection string. may be there is any syntax error.

Employee - Sage
Jul 13, 2023 8:59 AM UTC

I suspect the issue is not in this SQL, but somewhere else in your code - possibly the Imports section. Did you compile it successfully ?

HenningEmployee - AdvisorAnswer
Employee - Advisor
Jul 13, 2023 9:54 AM UTC

I believe you have an error in your execution command:

 Brapi.Database.ExecuteSql(aDbConn,aDbConn.ToString,False)

 

This should be the SQL command you wish to execute I believe

Henning_0-1689241858470.png

I.e. you need to use the (in your case) vSQL in the second position:

Brapi.Database.ExecuteSql(aDbConn, vSQL, False)

 

If that does not work, write your vSQL into the error log and analyze it there in order to see the final string that is supposed to be executed:
brapi.ErrorLog.LogMessage(si,"vSQL script: See details below.", vSQL)

KrishnaAuthor
Expert
Jul 13, 2023 12:36 PM UTC

Thanks Henning. It was a good Catch and Now it is working.

Appreciate your quick help.

KrishnaAuthor
Expert
Jul 14, 2023 12:05 AM UTC

There is an another way to create view using the  Table Data Manager..FYI...