0

I need to generate virtual table and fill it with range of date values.

The following query normally executes in MySql:

SELECT DATEID, STR_TO_DATE(DateValue, '%Y-%m-%d %H:%i:%s') AS DateValue FROM(
 SELECT CONVERT(DATE_FORMAT(ADDDATE(@day, INTERVAL @row1+1 DAY),'%Y%m%d'), UNSIGNED) AS DATEID, 
                                    @day := ADDDATE(@day, INTERVAL @row1+1 DAY) AS DateValue, 
                                    @row2 := @row2 + 1 AS row2
                                    FROM `INFORMATION_SCHEMA`.COLUMNS a, 
                                        (SELECT @row1 := 0) r, 
                                        (SELECT @row2 := 0) r2, 
                                        (SELECT @day := DATE_SUB('2000-01-01', INTERVAL 1 DAY)) d 
                                WHERE @row2 <= DATEDIFF('2020-01-01', '2000-01-01') ) as Result

Result: enter image description here

How can I get exactly that result using MongoDB BI Connector?

The problem is that ':=' assignment syntax is not working.

mongosqld started on localhost:3307

 public IDataReader ExecuteReader()
    {
        try
        {
            var connection = new MySqlConnection("Server=localhost;Port=3307;Database=masterDb;");

            var sqlQuery = @"SELECT DATEID, STR_TO_DATE(DateValue, '%Y-%m-%d %H:%i:%s') AS DateValue FROM(
                                SELECT CONVERT(DATE_FORMAT(ADDDATE(@day, INTERVAL @row1+1 DAY),'%Y%m%d'), UNSIGNED) AS DATEID, 
                                    @day := ADDDATE(@day, INTERVAL @row1+1 DAY) AS DateValue, 
                                    @row2 := @row2 + 1 AS row2
                                    FROM `INFORMATION_SCHEMA`.COLUMNS a, 
                                        (SELECT @row1 := 0) r, 
                                        (SELECT @row2 := 0) r2, 
                                        (SELECT @day := DATE_SUB('2000-01-01', INTERVAL 1 DAY)) d 
                                WHERE @row2 <= DATEDIFF('2020-01-01', '2000-01-01') ) as Result";

            if (connection.State != ConnectionState.Open)
                connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = sqlQuery;
            command.CommandType = CommandType.Text;
            command.CommandTimeout = ConnectionConstants.DefaultCommandTimeout;

            return command.ExecuteReader();

        }
        catch (Exception ex)
        {
            throw new ConnectorException(Sections.QueryExecution, ex.Message);
        }
    }

if input values as startDate = "2000-01-01" endDate = "2000-01-05"

output table should be as: enter image description here

sjahongir
  • 23
  • 7
  • Pls provide sample data, expected output, and the code you tried run in mongodb along with the exception you got. – Shadow Feb 22 '18 at 06:39
  • In [documentation of mongodb-bi-connector](https://docs.mongodb.com/bi-connector/v2.3/supported-operations/) written as **MongoDB Connector for BI Version 2.2 is compatible with SQL-99 SELECT statements**. But **:=** assignment does not works. – sjahongir Feb 22 '18 at 07:47
  • I am trying to execute simple query with **:=** assignment in MySql client. MySql client connected to mongosqld. Sample query: SELECT @x:=10; And exception ocurred as **syntax error at position 11 near :** – sjahongir Feb 22 '18 at 07:54
  • `:=` and session variables are great features of MySQL, but are not part of any SQL standards, I'm afraid. – Shadow Feb 22 '18 at 09:23
  • Can you give me a tip for my situation? – sjahongir Feb 22 '18 at 09:28
  • I already told you what you need to share with the SO community in order to get help. – Shadow Feb 22 '18 at 11:21

0 Answers0