Can translate, clickhouse cannot execute

[ysq5202121]

SELECT "t"."Sku", "t"."StockRegionCode", "t"."ShipmentDate0", COALESCE(SUM("t"."SalesQty"), 0), COALESCE(SUM("t"."NetSalesPrice" * "t"."ExchangeRate"), 0.0), COALESCE(SUM("t"."NetSalesPrice" * "t"."ExchangeRate"), 0.0) / CAST(COALESCE(SUM("t"."SalesQty"), 0) AS Decimal(32, 9))
FROM (
    SELECT "g"."NetSalesPrice", "g"."SalesQty", "g"."Sku", "s"."StockRegionCode", "o"."ExchangeRate", "toYYYYMM"("g"."ShipmentDate") AS "ShipmentDate0"
    FROM "GP_Order_View" AS "g"
    LEFT JOIN "Site" AS "s" ON "g"."SiteID" = "s"."SiteID"
    LEFT JOIN "ODS_Base_CurrencyMonth" AS "o" ON "g"."Period" = "o"."DayPeriod" AND "g"."Currency" = "o"."CurrencyName"
    WHERE "g"."OrderTypeID" = 10 AND "g"."SourceTalbe" = 'GP_OrderDetail' AND "g"."ShipmentDate" >= '2024-04-01' AND "g"."ShipmentDate" < '2024-04-30'
) AS "t"
GROUP BY "t"."Sku", "t"."StockRegionCode", "t"."ShipmentDate0"

The outer layer cannot access the inner layer, and "As" needs to be added inside. I'm not sure if there is a good solution Nested syntax is prone to this problem and is quite common

Error [47] [07000]: Code: 47. DB::Exception: There's no column 't.Sku' in table 't': While processing t.Sku. (UNKNOWN_IDENTIFIER) (version 23.6.2.18 (official build))

[ysq5202121]

@denis-ivanov expect a reply

[denis-ivanov]

@ysq5202121, provide full example with db schema, LINQ models, etc.

[ysq5202121]

 var List = (from d in OrderQuery
             join s in SiteQuery on d.SiteID equals s.SiteID into ds
             from s in ds.DefaultIfEmpty()
             join c in CurrencyQuery on new { d.Period, d.Currency }
             equals new { Period = c.DayPeriod, Currency = c.CurrencyName } into dc
             from c in dc.DefaultIfEmpty()
             where d.OrderTypeID == 10 && d.SourceTalbe == "GP_OrderDetail" && d.ShipmentDate >= StartDate && d.ShipmentDate < EndDate
             group new { d, s, c } by new { d.Sku, s.StockRegionCode, ShipmentDate = ClickQueryFunctions.toYYYYMM(d.ShipmentDate) }
             into g
             select new SkuMonthSalesByShipmentDate()
             {
                 Sku = g.Key.Sku,
                 StockRegionCode = g.Key.StockRegionCode,
                 Period = g.Key.ShipmentDate.ToString(),
                 TotalSalesQty = g.Sum(x => x.d.SalesQty),
                 TotalSalesAmt = g.Sum(x => x.d.NetSalesPrice * x.c.ExchangeRate),
                 AvgSalesPrice = g.Sum(x => x.d.NetSalesPrice * x.c.ExchangeRate) / g.Sum(x => x.d.SalesQty)
             }).ToList();

If you don't add custom functions, you won't be able to generate subqueries More examples can be provided @denis-ivanov

[denis-ivanov]

@ysq5202121, I don't see db schema and models.

[ysq5202121]

 /// <summary>
 /// 订单预报
 /// </summary>
 [Table("GP_Order_View")]
 [Comment("订单预报")]
 public class GP_Order_View : IEntity<ClickHouseDBContextLocator>, IEntity<MasterDbContextLocator>, IEntityTypeBuilder<GP_Order_View>
 {

     public void Configure(EntityTypeBuilder<GP_Order_View> entityBuilder, DbContext dbContext, Type dbContextLocator)
     {
         entityBuilder.HasKey(u => u.Id);
     }

     /// <summary>
     /// Id
     /// </summary>
     [Key, Column(Order = 1)]
     public Int32 Id { get; set; }

     /// <summary>
     /// 期间
     /// </summary>
     public String Period { get; set; }

     /// <summary>
     /// 结算时间
     /// </summary> 
     public DateTime SettlementTime { get; set; }
	 
	 /// <summary>
     /// 发货时间
    /// </summary>
    public DateTime ShipmentDate { get; set; }

     /// <summary>
     /// 订单明细ID
     /// </summary>
     public Int32 OrderDetailId { get; set; }
	 
	/// <summary>
    /// 销售数量
    /// </summary>
    public Int32 SalesQty { get; set; }

     /// <summary>
     /// 实际销售金额
     /// </summary>
     public Decimal NetSalesPrice { get; set; }


     /// <summary>
     /// 原表
     /// </summary>
     public string SourceTalbe { get; set; }

 }

/// <summary>
/// ODS_Base_CurrencyMonth
///</summary>
[Table("ODS_Base_CurrencyMonth")]
[SugarTable("ODS_Base_CurrencyMonth")]
public class ODSBaseCurrencyMonth : IEntity<ClickHouseDBContextLocator>, IEntity<MasterDbContextLocator>, IEntityTypeBuilder<ODSBaseCurrencyMonth>
{

    public void Configure(EntityTypeBuilder<ODSBaseCurrencyMonth> entityBuilder, DbContext dbContext, Type dbContextLocator)
    {
        entityBuilder.HasKey(u => u.CurrencyMonthID);
        entityBuilder.Property(p => p.DayPeriod).IsRequired();
        entityBuilder.Property(p => p.CurrencyName).IsRequired();
    }

    /// <summary>
    /// CurrencyMonthID
    /// </summary>
    [Key]
    public int CurrencyMonthID { get; set; }
    /// <summary>
    /// 货币
    /// </summary>
    public string CurrencyName { get; set; }
    /// <summary>
    /// 货币值
    /// </summary>
    public string CurrencyValue { get; set; }
    /// <summary>
    /// 汇率
    /// </summary>
    public decimal ExchangeRate { get; set; }
    /// <summary>
    /// 汇率（新）
    /// </summary>
    public decimal? ExchangeRateNew { get; set; }
    /// <summary>
    /// 排序
    /// </summary>
    public int? SortIndex { get; set; }
    /// <summary>
    /// 转换损失
    /// </summary>
    public decimal? ConversionLossRate { get; set; }
    /// <summary>
    /// IsState
    /// </summary>
    public bool? IsState { get; set; }
    /// <summary>
    /// 期间
    /// </summary>
    public string DayPeriod { get; set; }
}
 

 var List = (from d in OrderQuery
             join c in CurrencyQuery on new { d.Period, d.Currency }
             equals new { Period = c.DayPeriod, Currency = c.CurrencyName } into dc
             from c in dc.DefaultIfEmpty()
             where d.SourceTalbe == "GP_OrderDetail" && d.ShipmentDate >= StartDate && d.ShipmentDate < EndDate
             group new { d, s, c } by new { d.Sku, ShipmentDate = ClickQueryFunctions.toYYYYMM(d.ShipmentDate) }
             into g
             select new SkuMonthSalesByShipmentDate()
             {
                 Sku = g.Key.Sku,
                 Period = g.Key.ShipmentDate.ToString(),
                 TotalSalesQty = g.Sum(x => x.d.SalesQty),
                 TotalSalesAmt = g.Sum(x => x.d.NetSalesPrice * x.c.ExchangeRate),
                 AvgSalesPrice = g.Sum(x => x.d.NetSalesPrice * x.c.ExchangeRate) / g.Sum(x => x.d.SalesQty)
             }).ToList();

clickhouse :23.6.2.18 If necessary, I can write a dome, which is a common problem

[denis-ivanov]

https://en.wikipedia.org/wiki/Minimal_reproducible_example

ClickHouseDBContextLocator, MasterDbContextLocator, etc - what is it?

[ysq5202121]

ClickHouseTest.zip @denis-ivanov Dome

[ysq5202121]

@denis-ivanov

[denis-ivanov]

@ysq5202121, minimal working example is not Zip file.