The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I did not find a Warehouse category, so I'm asking here.
In a newly created Fabric Warehouse, I want to create a date dimension table from a query. I can't make the CREATE TABLE or INSERT INTO work. For example:
-- CREATE table [dbo].test AS
WITH date_basic AS (
SELECT TOP (DATEDIFF(DAY, '1970-01-01', '2099-12-31') + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2
),
dates as (
SELECT
CAST(DATEADD(DAY, date_basic.N, '1970-01-01') AS DATE) AS date_test
FROM date_basic)
select * from dates;
Solved! Go to Solution.
@v-nikhilan-msft you are right, I made this work
CREATE table [dbo].test AS
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)),
date_basic as (
SELECT TOP (DATEDIFF(DAY, '1970-01-01', '2099-12-31') + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1),
dates as (
SELECT
CAST(DATEADD(DAY, date_basic.N, '1970-01-01') AS DATE) AS date
FROM date_basic)
select * from dates
@v-nikhilan-msft you are right, I made this work
CREATE table [dbo].test AS
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)),
date_basic as (
SELECT TOP (DATEDIFF(DAY, '1970-01-01', '2099-12-31') + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1),
dates as (
SELECT
CAST(DATEADD(DAY, date_basic.N, '1970-01-01') AS DATE) AS date
FROM date_basic)
select * from dates
Hi @BiJoe
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.
I tried to add content by editing original post, but the editor wiped out all content due to "invalid HTML" >.<
The following results in "The query references an object that is not supported in distributed processing mode."
WITH date_basic AS (
SELECT TOP (DATEDIFF(DAY, '1970-01-01', '2099-12-31') + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2
),
dates as (
SELECT
CAST(DATEADD(DAY, date_basic.N, '1970-01-01') AS DATE) AS date_test
FROM date_basic)
insert [dbo].test (date_test)
select * from dates
go
Hi @BiJoe
Thanks for using Fabric Community.
The error The query references an object that is not supported in distributed processing mode
indicates that you've used an object or function that can't be used while you query data in Microsoft Fabric Warehouse.
Fabric Warehouse uses distributed processing for performance, but some objects and constructs aren't supported in this mode. The error message indicates that your query references an unsupported object, likely sys.all_columns
.
You can refer to this link for more information: Link1
Hope this helps. Please let me know if you have any further questions.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Fabric update to learn about new features.
User | Count |
---|---|
8 | |
3 | |
3 | |
3 | |
2 |