1

I would like to know how to automatically write the name of the current day in the table name when i execute a request like :

from qgpltemp.monday (if i run on monday)

from qgpltemp.tuesday (if i run on tuesday)

etc...

(the table is already created with the good name before executing the request)

I have Tried :

select DAYNAME(CURRENT_DATE)as day,cast (row1 as integer) from qgpltemp.:day

and some other things , but i often get the error :

Error: [SQL0104] Elément syntaxique : n'est pas correct. Eléments possibles : <IDENTIFIER>.
SQLState:  42601
ErrorCode: -104

The command DAYNAME(CURRENT_DATE)as day works fine, i only get the name of the day if i manually write .monday etc...

Kevin
  • 2,258
  • 1
  • 32
  • 40
Cass
  • 144
  • 1
  • 1
  • 13
  • Why are you embedding *data* in the *metadata* of the system, rather than keeping it as queryable data? – Damien_The_Unbeliever Jul 05 '17 at 08:21
  • possibly dublicated: https://stackoverflow.com/questions/21226571/mysql-date-column-auto-fill-with-current-date – Fotis Grigorakis Jul 05 '17 at 08:31
  • I don't know if i will ask correctly your question , but i try to make a request in a way that the user will not re-write each day the request for changing the name of the table because after i will integrate that request in a soft and a user will just execute a run for obtain the result. – Cass Jul 05 '17 at 08:31
  • 2
    But this would be *trivial* to solve if you just had a single table and an additional *column* containing the day - you'd just include the day as a condition in your `WHERE` clause. By embedding the day data in your table names, you're making this (and likely every other query) significantly more complex. – Damien_The_Unbeliever Jul 05 '17 at 08:36
  • I will change my table as u write, it's probably the best option, thank you – Cass Jul 05 '17 at 09:39

2 Answers2

2

I think something like this could be what you're looking for

declare @day varchar(max)
select @day = datename(dw,getdate())

declare @sqlCommand varchar(max)
Select @sqlCommand = 'Select * from qgpltemp.' + @day

exec(@sqlCommand)

if you insert that in your script it will execute the command to select all from a table called the current day.

Kevin
  • 2,258
  • 1
  • 32
  • 40
  • Thanks for your help, i will change the configuration of my table, however i will keep in mind your solution – Cass Jul 05 '17 at 09:43
  • I was providing the literal answer to your problem. 'Damien_The_Unbeliever ' provided a useful comment under your question, I think changing your table design if possible is the best approach also! good luck! – Kevin Jul 05 '17 at 10:02
1

Create an alias for the current day. Then your procedures can just point to the alias.

CREATE or replace ALIAS Qgpltemp.today FOR QGPLTEMP.MONDAY ;

select * from qqgpltemp.today;
danny117
  • 5,581
  • 1
  • 26
  • 35