Column Unknown error, column seems to exist ?

[linux guy]

I'm using dbeaver to work on my firebird database.

I'm trying to save this query:

CREATE OR ALTER VIEW myQuery (DATE_BEGIN_LOCAL, VOLUME)
AS
SELECT DATE_BEGIN_LOCAL, VOLUME, MC, PRICE  
FROM HOURLY_VOLUME hv
JOIN ASSETS ON HOURLY_VOLUME.ASSET = ASSETS.ASSET  
JOIN HOURLY_PRICE hp ON HOURLY_VOLUME.DATE_BEGIN_LOCAL = HOURLY_PRICE.DATE_BEGIN_LOCAL
WHERE ASSET = 'SCR4' AND VOLUME > ''
AND DATE_BEGIN_LOCAL > '1/1/2022 0:00';

I'm getting this error in dbeaver:

SQL Error [335544351] [42000]: unsuccessful metadata update; CREATE OR ALTER VIEW myQuery failed; Dynamic SQL Error; SQL error code = -206; Column unknown; HOURLY_VOLUME.ASSET; At line 7, column 16 [SQLState:42000, ISC error code:335544351]
  unsuccessful metadata update; CREATE OR ALTER myQuery failed; Dynamic SQL Error; SQL error code = -206; Column unknown; HOURLY_VOLUME.ASSET; At line 7, column 16 [SQLState:42000, ISC error code:335544351]
  unsuccessful metadata update; CREATE OR ALTER VIEW myQuery failed; Dynamic SQL Error; SQL error code = -206; Column unknown; HOURLY_VOLUME.ASSET; At line 7, column 16 [SQLState:42000, ISC error code:335544351]
    unsuccessful metadata update
    unsuccessful metadata update
      CREATE OR ALTER VIEW myQuery failed
      Dynamic SQL Error
      SQL error code = -206
      Column unknown
      HOURLY_VOLUME.ASSET
      At line 7, column 16

Yet...

SQL> show table HOURLY_VOLUME;
DATE_BEGIN_LOCAL                TIMESTAMP Nullable  
ASSET                           VARCHAR(100) Nullable  
VOLUME                          FLOAT Nullable

What am I missing ?  Why is the column unknown ?

I've refreshed the HOURLY_VOLUME table in dbeaver and it has ASSET as well.

Thanks

[linux guy]

I get the same error in isql-fb

SQL> CREATE OR ALTER VIEW myQuery (DATE_BEGIN_LOCAL, VOLUME)
CON> AS
CON> SELECT DATE_BEGIN_LOCAL, VOLUME, MC, ACTUAL_PRICE   
CON> FROM HOURLY_VOLUME hv  
CON> JOIN ASSETS ON HOURLY_VOLUME.ASSET = ASSETS.ASSET   
CON> JOIN HOURLY_PRICE hp ON HOURLY_VOLUME.DATE_BEGIN_LOCAL = HOURLY_PRICE.DATE_BEGIN_LOCAL  
CON> WHERE ASSET = 'SCR4' AND VOLUME > ''
CON> AND DATE_BEGIN_LOCAL > '1/1/2022 0:00';
Statement failed, SQLSTATE = 42S22
unsuccessful metadata update
-CREATE OR ALTER VIEW myQuery failed
-Dynamic SQL Error
-SQL error code = -206
-Column unknown
-HOURLY_VOLUME.ASSET
-At line 5, column 16

[linux guy]

And if I remove the join on HOURLY_VOLUME.ASSET, it can't find the HOURLY_VOLUME.DATE_BEGIN_LOCAL column either.

Does a column have to be indexed to be used in a join ?

[Alex MC]

Use alias hv

Le ven. 28 oct. 2022 à 22:24, linux guy <linux...@gmail.com> a écrit :

And if I remove the join on HOURLY_VOLUME.ASSET, it can't find the HOURLY_VOLUME.DATE_BEGIN_LOCAL column either.

Does a column have to be indexed to be used in a join ?

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAJPRYe%2BYwBEbEheatsjqJ8fzxEgWgArs6i%2BMjw54biBbAVUBqQ%40mail.gmail.com.

[Alex MC]

As you use table alias hv, use it everywhere

Also in dbeaver, do not add empty line in the middle of query

Regards

Alexandre 

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAJPRYeJj2zBK_uXDb4%2BOSg-6R%2B77VQ%2B1%2BsMqk%3DDdqP%2BNtoj9%2BA%40mail.gmail.com.

[linux guy]

Thanks.

This format worked:

CREATE OR ALTER VIEW myQuery (DATE_BEGIN_LOCAL, VOLUME, ACTUAL_POOL_PRICE) AS
SELECT hv.DATE_BEGIN_LOCAL, VOLUME, ACTUAL_PRICE  
FROM HOURLY_VOLUME hv
JOIN HOURLY_PRICE hp ON hv.DATE_BEGIN_LOCAL = hp.DATE_BEGIN_LOCAL
WHERE hv.ASSET = 'SCR4' AND VOLUME > ''
AND hv.DATE_BEGIN_LOCAL > '1/1/2022 0:00';

[Mark Rotteveel]

On 28-10-2022 22:25, Alex MC wrote:
> As you use table alias hv, use it everywhere
> Also in dbeaver, do not add empty line in the middle of query

You can use empty lines in the middle in DBeaver, but then you have to
select the entire query to run it, or use execute script (Alt+X).
--
Mark Rotteveel

[Mark Rotteveel]

Also, you can disable it in the settings File > Properties > Editors >
SQL Editor > SQL Processing, option "Blank line is statement delimiter".

Mark
--
Mark Rotteveel

[Alex MC]

Thanks for dbeaver info, I didn't know it

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/75f1887b-5916-e1a1-14f2-1883d58a8069%40lawinegevaar.nl.