To homepage To homepage
Solved

SQL Snippet Error

  • 15 January 2024
  • 8 replies
  • 111 views
K
Rank
Badge

CREATE VIEW [Demo].[INVC_T] AS
    SELECT
        R.[ACCT_DT],
        R.[SPLIT_FROM_INVC_NO],
        R.[REMIT_TO_ADDR_NO],
        R.[USER_INVC_NO],
        R.[UPDT_PERS_NO],
        R.[UPDT_DT_TIME],
        R.[TOTALS_UPDT_DT_TIME],
        R.[SHIP_TO_ADDR_NO],
        R.[SEG_NO],
        R.[RECUR_INVC_NO],
        R.[PRINT_INVC_STATUS],
        R.[PMT_TERMS_CD],
        R.[JE_NO],
        R.[INVC_TYPE_CD],
        R.[INVC_STATUS_CD],
        R.[INVC_SRC_CD],
        R.[INVC_NO],
        R.[INVC_DUE_DT],
        CAST( ([INVC_DT]) >= DATEADD(week, -10, GETDATE()) AS datetime) AS [INVC_DT],
        R.[INSUR_AMT],
        R.[FRGHT_AMT],
        R.[CUST_TYPE_CD],
        R.[CURR_RATE],
        R.[CURR_CD],
        R.[CLOSE_DT],
        R.[BILL_TO_CUST_NO],
        R.[ACCT_YR],
        R.[ACCT_PER],
        R.[PMT_METH_CD],
        R.[CREDIT_CARD_FLAG],
        [DW_Id],
        [DW_Batch],
        [DW_SourceCode],
        [DW_TimeStamp],
        [DW_ODXBatchNumber],
        [DW_ODXBatchId]
    FROM
        [Demo].[INVC_R] R

GO


above script error is what im getting please let me know what is wrong or where im getting it incorrect,
below is snippet which im using,
 (FIELD) >= DATEADD(week, -10, GETDATE())

icon

Best answer by rory.smith 15 January 2024, 14:28

View original

8 replies

rory.smith
Rank
Userlevel 5
Badge +7

Hi @Karankadam ,

if you experience an error it is probably useful to print the error...

K
Rank
Badge

Hi @rory.smith 
Here is the error message
 

An error occurred during create view. See exception details for the failing object: Create failed for View 'Demo.INVC_T'.
An exception occurred while executing a Transact-SQL statement or batch.
Incorrect syntax near the keyword 'AS'.

Details:

SQL Server: 'localhost'
SQL Procedure: 'INVC_T'
SQL Line Number: 20
SQL Error Number: 156

Incorrect syntax near the keyword 'AS'.
Module: .Net SqlClient Data Provider
System.Data.SqlClient.SqlException
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

An exception occurred while executing a Transact-SQL statement or batch.
Module: Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Common.ExecutionFailureException
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext, Boolean executeForAlter)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

Create failed for View 'Demo.INVC_T'.
Module: Microsoft.SqlServer.Smo
Microsoft.SqlServer.Management.Smo.FailedOperationException
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
   at TimeXtender.Sql.Engine.SqlEngineBase.CreateView(SqlView sqlView)

An error occurred during create view. See exception details for the failing object: Create failed for View 'Demo.INVC_T'.
Module: TimeXtender.Sql.Engine.2022
TimeXtender.Sql.Engine.SqlEngineException
   at TimeXtender.Sql.Engine.SqlEngineBase.CreateView(SqlView sqlView)
   at TimeXtender.DataManager.StepTransformationViewDeploy.DeployTransformationView(StringBuilder script, FieldTransformationHelper helper, DataManTable dataManTable, IDeploymentEngine sqlEngine, VariableResolveObject dynamicResolveObject, Boolean ignoreDeploymentOfCustomCode, TXCheckSumCache checkSumCache)
   at TimeXtender.DataManager.StepTransformationViewDeploy.DoAbstractStep(IStepSetup stepSetup)
 

rory.smith
Rank
Userlevel 5
Badge +7

Your SQL syntax is off - you are trying to cast a boolean expression into a datetime. Either you should be using a conditional transformation or you should be using a DATEDIFF between the passed date parameter and a DATEADDed date.

K
Rank
Badge

@rory.smith 

while im running the same query in Query tool it is giving me result, although im not trying the boolean actual column also it is type of date

 

 

rory.smith
Rank
Userlevel 5
Badge +7

doesn't work for me….

 

I don't know what you are trying to do here, so I cannot suggest something that would work. Your expression is being cast to datetime because that is probably the field type of [INVC_DT]. If you are trying to get some kind of date as a result you should be using fixed transformations with conditions. If you are trying to determine whether the invoice data is more recent than in the last 10 weeks, you should be using DATEDIFF between the invoice date and the current date and basing your condtion on that. 

K
Rank
Badge

@rory.smith i think there is confusion, i have attached the screenshot please refer to that, i have created SQL Snippet, Timextender it self it creates the CAST statement if you see, please refer below screentshot

1 Attachment
rory.smith
Rank
Userlevel 5
Badge +7

There is no confusion: your snippet gets used in the context of a field transformation. A field transformation will be cast to the type of the field. If you run your snippet syntax directly it still doesn't make sense:

Your snippet is a boolean expression: “Tell me whether Field is greater than or equal to the current date minus 10 weeks”. The (logical) answer would be NULL, true or false. However you cannot run a boolean expression like that in T-SQL, you need to use a CASE expression. Boolean expressions cannot be used directly like that:

”Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.” and "Expressions with Boolean data types are used in the WHERE clause to filter the rows that qualify for the search conditions and in control-of-flow language statements such as IF and WHILE” (see: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/comparison-operators-transact-sql?view=sql-server-ver16)

I.e you would need to rewrite your snippet like one of the following :

  • IIF(Field > DATEADD(week, -10, GETDATE()), 1, 0)
  • CASE WHEN Field > DATEADD(week, -10, GETDATE()) THEN 1  ELSE 0  END

Depending on what you are actually trying to do, something else might be even better.

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @Karankadam 

Were you able to test rewriting the snippet as suggested by Rory above? Please let us know if this resolved the issue

Reply


Terms and ConditionsCookie settings