error on Interbase 2020 server

[delphi159 0]

My final aim is this sql-code:

select  
      G.Goods,  
      G.Goods_id,
      coalesce(dt2.incomes,0)as incomes,
      coalesce(dt2.sales,0) as sales,
      coalesce(dt2.writeoffs,0) as writeoffs,
      coalesce(dt2.endqnt,0) as endqnts
From Goods G
Join
(
select                                                                          
      Goods_id,
      coalesce(sum(inc),0) as incomes,
      coalesce(sum(sale),0) as sales,
      coalesce(sum(writeoff),0) as writeoffs,
      sum(coalesce(inc,0)-coalesce(sale,0)-coalesce(writeoff,0)) as endqnts
    from(
        select 
            i.goods_id, 
            sum(i.qty) as inc,
            cast(0 as float) as sale,
            cast(0 as float) as writeoff
        from income i
        where cast(i.recdate as date) <= :d  
        group by i.goods_id  
     union all
        select 
            s.goods_id, 
            cast(0 as float),
            sum(s.qty) as sale,
            cast(0 as float)
         from sales s
         where cast(s.recdate as date) <= :d 
         group by s.goods_id    
     union all
        select
            w.goods_id,
            cast(0 as float), 
            cast(0 as float),
            sum(w.Qty)  as writeoff, 
        from writeoff w
        where cast(i.recdate as date) <= :d   
        group by w.goods_id) dt1 
   group by Goods_id) dt2
   on P.Goods_id=dt2.Goods_id
   order by Goods

but this dt2+dt1 derived tables code 

select                                                                          
      Goods_id,
      coalesce(sum(inc),0) as incomes,
      coalesce(sum(sale),0) as sales,
      coalesce(sum(writeoff),0) as writeoffs,
      sum(coalesce(inc,0)-coalesce(sale,0)-coalesce(writeoff,0)) as endqnts
    from(
        select 
            i.goods_id, 
            sum(i.qty) as inc,
            cast(0 as float) as sale,
            cast(0 as float) as writeoff
        from income i
        where cast(i.recdate as date) <= :d  
        group by i.goods_id  
     union all
        select 
            s.goods_id, 
            cast(0 as float),
            sum(s.qty) as sale,
            cast(0 as float)
         from sales s
         where cast(s.recdate as date) <= :d 
         group by s.goods_id    
     union all
        select
            w.goods_id,
            cast(0 as float), 
            cast(0 as float),
            sum(w.Qty)  as writeoff, 
        from writeoff w
        where cast(i.recdate as date) <= :d   
        group by w.goods_id) dt1 
   group by Goods_id

returns this error on Interbase 2020 server:

Error at line 1. Dynamic SQL Error: Error at line 1. sql error code=-206. Column unknown INC.
I tried a lot of attempts write code with liases but without success.

 How to correct this code for Interbase 2020 server?

Edited December 19, 2021 by delphi159

[Guest]

Just a quick guess, (i'm not an IB user); try sum(coalesce(dt1.field, 0)) it should produce the same result.

If IB has CTEs then i guess it would be a bit easier to map fields. Also just a guess.

[delphi159 0]

Just now, Dany Marmur said:

Just a quick guess, (i'm not an IB user); try sum(coalesce(dt1.field, 0)) it should produce the same result.

If IB has CTEs then i guess it would be a bit easier to map fields. Also just a guess.

Dany Marmur, unfortunatelley a new error appeared: 'Expression Evalution Not Supported' on IB 2020. 

Edited December 19, 2021 by delphi159

[jobo 1]

Also just guessing, I don't use IB, try another Alias but "inc", maybe it's a reservered word.

And not so guessing, but not tested either, try more explicit grouping here:

from(
        select 
            i.goods_id, 
            sum(i.qty) as inc,
            cast(0 as float) as sale,
            cast(0 as float) as writeoff
        from income i
        where cast(i.recdate as date) <= :d  
        group by i.goods_id  
     union all
        select 
            s.goods_id, 
            cast(0 as float),
            sum(s.qty) as sale,
            cast(0 as float)
         from sales s
         where cast(s.recdate as date) <= :d 
         group by s.goods_id    
     union all
        select
            w.goods_id,
            cast(0 as float), 
            cast(0 as float),
            sum(w.Qty)  as writeoff, 
        from writeoff w
        where cast(i.recdate as date) <= :d   
        group by w.goods_id) dt1

"Save" rule for grouping is group everything, which is not aggregated. You are only grouping one column out off 3 unaggregates ones. Exact rules for grouping depend on database and even datamodel (most of all PK Definition). At the moment I know only MYSSQL and Maria beeing very problematic when it comes to grouping. Which leads to wrong results or missleading error messages.

Finally, run the union part stand alone to dig down the error.

[Joseph MItzen 251]

On 12/19/2021 at 3:31 AM, Dany Marmur said:

If IB has CTEs

It doesn't, unbelievably.

[Frickler 11]

On 12/30/2021 at 4:17 AM, Joseph MItzen said:

It doesn't, unbelievably.

With Update 2, Interbase 2020 has now CTEs.

[Serge_G 87]

18 hours ago, Frickler said:

With Update 2, Interbase 2020 has now CTEs.

Well, in fact Interbase 2017 had non recursive CTE possiblities (named derived table)

Good news to have now recursive ones, and can we expect in a near future, windows functions?

Edited January 5, 2022 by Serge_G

[Frickler 11]

On 1/5/2022 at 2:09 PM, Serge_G said:

Good news to have now recursive ones, and can we expect in a near future, windows functions?

I don't think so.

 

I think InterBase is designed to be very slim and fast. For example, for everything except the most essential internal functions you have to use UDFs. Furthermore, it looks to me that the main use case is still embedded SQL like in ye olden times of InterBase on UNIX.

[Guest]

13 minutes ago, Frickler said:

I think InterBase is designed to be very slim and fast. For example, for everything except the most essential internal functions you have to use UDFs. Furthermore, it looks to me that the main use case is still embedded SQL like in ye olden times of InterBase on UNIX.

Interesting... the only thing i see (without doing any actual research) IB has over FB is those change views. You can do that with triggers. FB has windows and CTEs.

It would be interesting to hear about a solid selection between the two (FB >= 3) and why one would choose IB.

To be clear, any "legacy" concerns should not be weighted into the decision.

[Sriram 4]

delphi159,

 

I have modified the query to provide column names while defining "dt1". Internally, in the "dt1" derived table definition, not every "select" from the UNIONs seem to name their columns. So, it would be nice for the external "dt1" to explicitly qualify them.

 

Also, made a small correction when using "dt2.endqnts" in the upper level query.

 

Hopefully, this works for you.

 

FYI: InterBase 2020 (with updates) has support for Derived Tables, non-recursive and recursive CTEs.

 

select  
      G.Goods,  
      G.Goods_id,
      coalesce(dt2.incomes,0)as incomes,
      coalesce(dt2.sales,0) as sales,
      coalesce(dt2.writeoffs,0) as writeoffs,
      coalesce(dt2.endqnts,0) as endqnts
From Goods G
Join
(
select                                                                          
      Goods_id,
      coalesce(sum(inc),0) as incomes,
      coalesce(sum(sale),0) as sales,
      coalesce(sum(writeoff),0) as writeoffs,
      sum(coalesce(inc,0)-coalesce(sale,0)-coalesce(writeoff,0)) as endqnts
    from(
        select 
            i.goods_id, 
            sum(i.qty) as inc,
            cast(0 as float) as sale,
            cast(0 as float) as writeoff
        from income i
        where cast(i.recdate as date) <= :d  
        group by i.goods_id  
     union all
        select 
            s.goods_id, 
            cast(0 as float),
            sum(s.qty) as sale,
            cast(0 as float)
         from sales s
         where cast(s.recdate as date) <= :d 
         group by s.goods_id    
     union all
        select
            w.goods_id,
            cast(0 as float), 
            cast(0 as float),
            sum(w.Qty)  as writeoff, 
        from writeoff w
        where cast(i.recdate as date) <= :d   
        group by w.goods_id) dt1 (Goods_id, inc, sale, writeoff) 
   group by Goods_id) dt2 (Goods_id, incomes, sales, writeoffs, endqnts)
   on G.Goods_id=dt2.Goods_id
   order by Goods

[Serge_G 87]

WITH dt2 AS (
   select 
            goods_id, 
            sum(qty) as inc,
            cast(0 as float) as sale,
            cast(0 as float) as writeoff
        from income
        where cast(recdate as date) <= :d  
        group by goods_id  
     union all
        select 
            goods_id, 
            cast(0 as float),
            sum(qty) as sale,
            cast(0 as float)
         from sales
         where cast(recdate as date) <= :d 
         group by goods_id    
     union all
        select
            goods_id,
            cast(0 as float), 
            cast(0 as float),
            sum(Qty)  as writeoff, 
        from writeoff
        where cast(recdate as date) <= :d   
        group by w.goods_id)
  )      

select  
      G.Goods,  
      G.Goods_id,
      coalesce(dt2.incomes,0)as incomes,
      coalesce(dt2.sales,0) as sales,
      coalesce(dt2.writeoffs,0) as writeoffs,
      coalesce(dt2.endqnts,0) as endqnts
From Goods G JOIN dt2 on G.Goods_id=dt2.Goods_id
   on G.Goods_id=dt2.Goods_id
order by G.Goods 

I think that, if CTE is really effective in Interbase 2020, this one is more clear

 

Some notes : 

  - Using alias in a union is not useful

  - I change the where clause in the last part of the union : where cast(recdate as date) <=:d  ( hoping that this column exists in the table "writeoff")

 

Question  the JOIN between Goods and dt2, it shouldn't rather be a LEFT JOIN