6 Replies Latest reply on Oct 4, 2013 10:29 AM by Michel Caissie

    Custom SQL functions

    Sameer Mittal

      hey guys,

       

      I'm writing this custom sql and facing some error but I'm unable to fix this. any help would be appreciated. Also please can you direct me to some pages where I could read on what functions could be use while writing custom SQL. Especially I'm struggling with date function i'm looking for something similar to trunc in oracle SQL where i could transform a time stamp to just a date value.

       

      Thanks.

      Sameer

       

      SELECT

      [Before Promotion Data Sep 23 (2)#txt].[ASIN] AS [ASIN],

      [Before Promotion Data Sep 23 (2)#txt].[MarketPlace Id] AS [MarketPlace Id],

      [Before Promotion Data Sep 23 (2)#txt].[Region Id] AS [Region Id],

      [Before Promotion Data Sep 23 (2)#txt].[activity_id] AS [activity_id],

      [Before Promotion Data Sep 23 (2)#txt].[activity_status] AS [activity_status],

      [Before Promotion Data Sep 23 (2)#txt].[activity_type] AS [activity_type],

      [Before Promotion Data Sep 23 (2)#txt].[description] AS [description],

      [Before Promotion Data Sep 23 (2)#txt].[order_placed_datetime] AS [order_placed_datetime],

      SUM([Before Promotion Data Sep 23 (2)#txt].[sales_discount_amount])AS [sales_discount_amount],

      SUM([Before Promotion Data Sep 23 (2)#txt].[shipped_ops])AS [shipped_ops],

      SUM([Before Promotion Data Sep 23 (2)#txt].[shipped_quantity]) AS [shipped_quantity],

      SUM([Before Promotion Data Sep 23 (2)#txt].[total_quantity]) AS [total_quantity],

      SUM([Before Promotion Data Sep 23 (2)#txt].[order_ops])AS [order_ops],

      SUM([Before Promotion Data Sep 23 (2)#txt].[discount_amount])AS [discount_amount],

      SUM([Before Promotion Data Sep 23 (2)#txt].[cp])AS [cp],

      'Before Promo' as [TimePeriod]

      FROM [Before Promotion Data Sep 23 (2)#txt]

      Group by

      [ASIN],

      [MarketPlace Id],

      [Region Id],

      [activity_id],

      [activity_status],

      [activity_type],

      [description],

      [order_placed_datetime],

      [TimePeriod]

      Union all

      SELECT [Promotion Data Sep 23#txt].[ASIN] AS [ASIN],

      [Promotion Data Sep 23#txt].[MarketPlace Id] AS [MarketPlace Id],

      [Promotion Data Sep 23#txt].[Region Id] AS [Region Id],

      [Promotion Data Sep 23#txt].[activity_id] AS [activity_id],

      [Promotion Data Sep 23#txt].[activity_status] AS [activity_status],

      [Promotion Data Sep 23#txt].[activity_type] AS [activity_type],

      [Promotion Data Sep 23#txt].[description] AS [description],

      [Promotion Data Sep 23#txt].[order_placed_datetime] AS [order_placed_datetime],

      SUM([Promotion Data Sep 23#txt].[order_ops])AS [order_ops],

      SUM([Promotion Data Sep 23#txt].[sales_discount_amount]) AS [sales_discount_amount],

      SUM([Promotion Data Sep 23#txt].[shipped_ops])AS [shipped_ops],

      SUM([Promotion Data Sep 23#txt].[shipped_quantity]) AS [shipped_quantity],

      SUM([Promotion Data Sep 23#txt].[total_quantity])AS [total_quantity],

      SUM([Promotion Data Sep 23#txt].[cp])AS [cp],

      SUM([Promotion Data Sep 23#txt].[discount_amount])AS [discount_amount],

      'During Promo' as [TimePeriod]

      FROM [Promotion Data Sep 23#txt]

      Group by

      [ASIN],

      [MarketPlace Id],

      [Region Id],

      [activity_id],

      [activity_status],

      [activity_type],

      [description],

      [order_placed_datetime],

      [TimePeriod]

       

      error I'm receiving is :

       

      Unable to open the text file. Please check that you have specified the correct field delimiter and that you have access privileges for the file.

      Microsoft JET database error 0x80040E10: No value given for one or more required parameters.