The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I have an issue where I'm trying to append the results of two queries into a third, using the append query option.
The tables all are just a list of User ID and Date, and I'm also adding a column to have a static number for the instance ID so that I can track which site was visited in the aggregate statistics.
However, the Query Folding gives an SQL statement that is syntatcially incorrect, and SQL reports an error, though the Query Builder reports no errors.
The resultant SQL by viewing the native query is:
(
select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
344 as `instance_id`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_344_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
union all select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
345 as `instance_id`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_345_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
)
union all select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
329 as `instance_id`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_329_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
This throws an error. If I then modify the three queries within query builder to add an extra column by duplicating the date column, the query works, and produces this SQL:
select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
`_`.`instance_id` as `instance_id`,
`_`.`date - Copy` as `Hour`
from
(
(
select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
344 as `instance_id`,
`_`.`date` as `date - Copy`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_344_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
union all select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
345 as `instance_id`,
`_`.`date` as `date - Copy`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_345_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
)
union all select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
329 as `instance_id`,
`_`.`date` as `date - Copy`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_329_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
) `_`
By comparing the two, it seems when just adding the extra column of a number for instance_id the query folding is omitting a
select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
`_`.`instance_id` as `instance_id`
from
(
from the beginning of the query, and
) `_`
from the end.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.