Skip to main content
To homepage To homepage
Solved

SQL Snippet Error

K
Contributor
Forum|alt.badge.img

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())

Best answer by rory.smith

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.

View original
KaranKadam
Did this topic help you find an answer to your question?

rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • January 15, 2024

Hi @Karankadam ,

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

K
Contributor
Forum|alt.badge.img

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)
 

KaranKadam
rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • January 15, 2024

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
Contributor
Forum|alt.badge.img

@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

 

 

KaranKadam
rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • January 15, 2024

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
Contributor
Forum|alt.badge.img

@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 Attachments
KaranKadam
rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • January 15, 2024

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.

daniel
1 person likes this
  • daniel
    daniel
Christian Hauggaard
Community Manager
Forum|alt.badge.img+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


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings