Hi Everyone,
We would like to create a view with schemabinding option. Anybody Knows if it is possible to do with the .cfg file?
Create View xxxxxx with schemabinding As
SELECT ....
Release: 2022R2
Thanks!
Hi Everyone,
We would like to create a view with schemabinding option. Anybody Knows if it is possible to do with the .cfg file?
Create View xxxxxx with schemabinding As
SELECT ....
Release: 2022R2
Thanks!
Hello,
You might want to try posting your request on the sage developer site. https://developer.sage.com/
You might have better luck asking developer people.
Hi Chris,
There is no option to do it with sage, sage is not creating views Indexes. I did my own solution, runing SQL scripts to create the views.
I'm really busy with many projects and I forgot to update this feed. Here is my code:
#######################################################################################################
Subprog DROP_VIEW(PVIEW)
Value Char PVIEW
#
Local Clbfile WSQL(5)
#
If !clalev([AVB]) : Local File AVIEWB [AVB] : Endif
Raz [AVB] : Read [AVB]AVB0=PVIEW;2
If fstat : Errbox "View doesn't Exist" :End : Endif
#
WSQL ="If OBJECT_ID('"+nomap+"."+PVIEW+"', 'V') IS not null DROP VIEW "+ nomap+"."+PVIEW
Execsql From "S" Sql WSQL
End
######################################################################################################
Subprog CREATE_VIEW_SCHEMABINDING(PVIEW)
Value Char PVIEW
#
Local Clbfile WSQL(5)
#
If !clalev([AVB]) : Local File AVIEWB [AVB] : Endif
Raz [AVB] : Read [AVB]AVB0=PVIEW;2
If fstat : Errbox "View doesn't Exist" :End : Endif
#
WSQL ="If OBJECT_ID('"+nomap+"."+PVIEW+"', 'V') IS not null DROP VIEW "+ nomap+"."+PVIEW
Execsql From "S" Sql WSQL
#
WSQL=" CREATE VIEW "+nomap+"."+PVIEW + " with schemabinding As "+[AVB]TEXTE
Execsql From "S" Sql WSQL
#
Call CREATE_VIEW_INDEXES(PVIEW)
End
#########################################################################################################
Subprog CREATE_VIEW_INDEXES(PVIEW)
Value Char PVIEW
#
Local Clbfile WSQL(5)
Local Integer WCOUNT : WCOUNT=0
#
If !clalev([AVC]) : Local File AVIEWC [AVC] : Endif
#
For [AVC] Where CODVUE=PVIEW and KEYDUP=1
If WCOUNT=0
WSQL ="CREATE UNIQUE CLUSTERED INDEX "+PVIEW+"_"+[AVC]CODCLE + " ON " + nomap+"."+PVIEW
WSQL+= " ("+ func REPALCE_CLE([AVC]DESCLE)+")"
Else
WSQL ="CREATE UNIQUE NONCLUSTERED INDEX "+PVIEW+"_"+[AVC]CODCLE + " ON " + nomap+"."+PVIEW
WSQL+= " ("+ func REPALCE_CLE([AVC]DESCLE)+")"
Endif
Execsql From "S" Sql WSQL
WCOUNT+=1
Next
#
For [AVC] Where CODVUE=PVIEW and KEYDUP=2
WSQL ="CREATE NONCLUSTERED INDEX "+PVIEW+"_"+[AVC]CODCLE + " ON " + nomap+"."+PVIEW
WSQL+= " ("+ func REPALCE_CLE([AVC]DESCLE)+")"
Execsql From "S" Sql WSQL
Next
End
#########################################################################################################
Funprog REPALCE_CLE(PCLE)
Value Char PCLE
Local Char WCLE(250)
#
For I=1 To len(PCLE)
If mid$(PCLE,I,1)= '+' or mid$(PCLE,I,1)= '-'
WCLE+='_0,'
Else
WCLE+=mid$(PCLE,I,1)
Endif
Next
End WCLE+"_0"
Hi Chris,
There is no option to do it with sage, sage is not creating views Indexes. I did my own solution, runing SQL scripts to create the views.
I'm really busy with many projects and I forgot to update this feed. Here is my code:
#######################################################################################################
Subprog DROP_VIEW(PVIEW)
Value Char PVIEW
#
Local Clbfile WSQL(5)
#
If !clalev([AVB]) : Local File AVIEWB [AVB] : Endif
Raz [AVB] : Read [AVB]AVB0=PVIEW;2
If fstat : Errbox "View doesn't Exist" :End : Endif
#
WSQL ="If OBJECT_ID('"+nomap+"."+PVIEW+"', 'V') IS not null DROP VIEW "+ nomap+"."+PVIEW
Execsql From "S" Sql WSQL
End
######################################################################################################
Subprog CREATE_VIEW_SCHEMABINDING(PVIEW)
Value Char PVIEW
#
Local Clbfile WSQL(5)
#
If !clalev([AVB]) : Local File AVIEWB [AVB] : Endif
Raz [AVB] : Read [AVB]AVB0=PVIEW;2
If fstat : Errbox "View doesn't Exist" :End : Endif
#
WSQL ="If OBJECT_ID('"+nomap+"."+PVIEW+"', 'V') IS not null DROP VIEW "+ nomap+"."+PVIEW
Execsql From "S" Sql WSQL
#
WSQL=" CREATE VIEW "+nomap+"."+PVIEW + " with schemabinding As "+[AVB]TEXTE
Execsql From "S" Sql WSQL
#
Call CREATE_VIEW_INDEXES(PVIEW)
End
#########################################################################################################
Subprog CREATE_VIEW_INDEXES(PVIEW)
Value Char PVIEW
#
Local Clbfile WSQL(5)
Local Integer WCOUNT : WCOUNT=0
#
If !clalev([AVC]) : Local File AVIEWC [AVC] : Endif
#
For [AVC] Where CODVUE=PVIEW and KEYDUP=1
If WCOUNT=0
WSQL ="CREATE UNIQUE CLUSTERED INDEX "+PVIEW+"_"+[AVC]CODCLE + " ON " + nomap+"."+PVIEW
WSQL+= " ("+ func REPALCE_CLE([AVC]DESCLE)+")"
Else
WSQL ="CREATE UNIQUE NONCLUSTERED INDEX "+PVIEW+"_"+[AVC]CODCLE + " ON " + nomap+"."+PVIEW
WSQL+= " ("+ func REPALCE_CLE([AVC]DESCLE)+")"
Endif
Execsql From "S" Sql WSQL
WCOUNT+=1
Next
#
For [AVC] Where CODVUE=PVIEW and KEYDUP=2
WSQL ="CREATE NONCLUSTERED INDEX "+PVIEW+"_"+[AVC]CODCLE + " ON " + nomap+"."+PVIEW
WSQL+= " ("+ func REPALCE_CLE([AVC]DESCLE)+")"
Execsql From "S" Sql WSQL
Next
End
#########################################################################################################
Funprog REPALCE_CLE(PCLE)
Value Char PCLE
Local Char WCLE(250)
#
For I=1 To len(PCLE)
If mid$(PCLE,I,1)= '+' or mid$(PCLE,I,1)= '-'
WCLE+='_0,'
Else
WCLE+=mid$(PCLE,I,1)
Endif
Next
End WCLE+"_0"
Take care about:
Regards!
Thanks for posting the script!
...that's the idea of this site, help & share our knowledge with everybody!!
Best regards!
*Community Hub is the new name for Sage City