Using embedded Firebird 3.0 Define a table EMP with columns empid autovalue name varchar address varchar startdate date Now I want startdate to have as default value current_date. The way I prefer to do this, is to run the SQL command "alter table emp alter column startdate set default current_date. This works OK. Now create a query where the condition for startdate is "> current_date - 365". To make it work, define the query as "run sql directly". Now create a report on this query using the wizard, just a straight list of all the fields from the query. The startdate on running the report comes out as a number. Changing the report properties to refer to the query i.s.o. the sql statement the report wizard inserted, does not make a difference. Create from the query a view, and run again the report wizard, this time on the view, and the startdate comes out as a date, which is the logical thing to be expected. Thee report wizard should have done this properly based on the query.
alter table emp alter column startdate set default current_date or alter table emp alter startdate set default current_date doesn't work for me. I got: *ALTER TABLE EMP failed *column STARTDATE does not exist in table/view EMP caused by 'alter table EMP alter startdate set default current_date' I double checked "STARTDATE" exists.
@ Julien my fault, the table and column names should be quoted alter table "EMP" alter "startdate" set default current_date'
fault again alter table "EMP" alter column "startdate" set default current_date
Ok it worked. Now I'm trying to make the query and with SQL edit, this works: SELECT * FROM "EMP" WHERE "startdate" > CURRENT_DATE not this: SELECT * FROM "EMP" WHERE "startdate" > CURRENT_DATE - 365 or even this: SELECT * FROM "EMP" WHERE "startdate" > (CURRENT_DATE - 365) I got syntax error without precision.
Testing with Version: 7.0.3.1 Build ID: d7547858d014d4cf69878db179d326fc3483e082 CPU threads: 8; OS: Mac OS X 10.16; UI render: default; VCL: osx Locale: fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR Calc: threaded on macOS Apple Silicon M1, I get an error message when attempting to execute the query : Syntax error in SQL expression The query runs just fine as a query and produces the correct results.
(In reply to Alex Thurgood from comment #5) > Testing with > > Version: 7.0.3.1 > Build ID: d7547858d014d4cf69878db179d326fc3483e082 > CPU threads: 8; OS: Mac OS X 10.16; UI render: default; VCL: osx > Locale: fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR > Calc: threaded > > on macOS Apple Silicon M1, I get an error message when attempting to execute > the query : I meant execute the report based on the query.
How do you create a view on which to base your report ? The "create view" option only works from the Tables menu, however, I don't seem to be able to get it to do the simple subtraction in the way you describe via the GUI, as I get SQL token errors, or SQL incorrect date formatting errors when defining the criteria of the view in the GUI and then attempting to move out of that GUI cell. Does the view have to be defined via Tools > Options > SQL ?
The default value has nothing to do with this behavior. The query could only be executed by direct SQL, because the GUI doesn't know what to do with '- 365'. Remember: DATEADD (and this is a part of it) isn't known in the old internal HSQLDB. So the Report Builder doesn't know how to handle with this query. You also have to set the report builder to Analyze SQL → No for executing a query, which doesn't work with the Query GUI. This query will work: SELECT * FROM "EMP" WHERE WHERE "startdate" > CAST( CURRENT_DATE AS DATE ) - 365 I don't know why, but the GUI will accept this and it will also work together with report builder and format the date directly as a date. One hint: Report Builder will better work with views. He could analyze the SQL, all grouping and sorting will work. The view looks for the report like a table with clear defined types for every field.
@ Julien and Alex. To run this query as I wrote it, you have to set "Run SQL directly", then there will be no error and the SQL will givve the correct result. Making a view from the query is done by right-click on the query name in the database overview and picking the proper option (I cann't give you the exact English name since I am running in Dutch). @ Robert Setting the "Analyze -No" does not impact the missing formatting of the date field.
@ herman If it is set to Analyze SQL → No it doesn't analyze the type of the field also. The content of the field are numbers - so it will be shown as numbers. '0' → '1899-12-30'. If it is set to Analyze SQL → Yes it analyzes the code and detects a data. So it will show a date. But your code works only with Analyze SQL → No. I won't call it a bug of the Report Builder. You could change the code so it could work with Analyze SQL → Yes (and in the query with the GUI, not 'run SQL directly') and it works well.
Giving an overview of what I see, taking into account that I use the query as I stated it: no casting, running sql directly, which in itself works OK. Generating the report with the wizard: 1. when based on this view, the report has the select command in the data properties and "Analyze - yes": not running, syntax fault. 2. in the existing report now, just change "Analyze - no": report runs, but displays the date field as a number, which is just plain wrong. 3. in the existing report now, change the data properties of the report to refer to the query: the report runs and the Analyze setting does not matter (yes or no): the report runs , but displays the date field as a number. 4. Use the query to make a view, rerun the report wizard now based on the view and run the report without any other intervention: it runs and the date field is displayed as a date, no intervention necessary. And that is the behavior I expect in the pt 2. and 3. I can live with the fact that - due to a problem between Base and Firebird - pt 1 fails, but I cannot accept the date field is formatted the wrong way in pt. 2 and 3.
If you change in the report Analyze → Yes the whole report won't start here when creating the connection to the report by the wizard, because the wizard reads the SQL-code from the query here. The report would only start when I connect it directly (not by wizard) to the query. Then I mark the field, set the properties to date for the field and all will work fine here. So I can't confirm a bug. If it couldn't be changed by formatting of the field in the report please add an example as attachment here.
I do not agree there is no bug. The table field is defined as date, the query does not do any casting, so there is no reason why the report wizard should make a number field of it. That I can change the format of the report field is not a solution, it is a workaround, but it shouldn't be necessary in the first place.