Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BiJoe
Helper II
Helper II

Fabric warehouse create table as

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;

 

 

 
Without the create table line, the query works just fine. Commenting in create table leads to error message:
 
The query references an object that is not supported in distributed processing mode.
Msg 15816, Level 16, State 4, CODE LINE 1
 
I have also tried creating table from stored procedure. What am I doing wrong?
1 ACCEPTED SOLUTION
BiJoe
Helper II
Helper II

@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

 

View solution in original post

4 REPLIES 4
BiJoe
Helper II
Helper II

@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.

BiJoe
Helper II
Helper II

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.

vnikhilanmsft_0-1703069420508.png

 

 

You can refer to this link for more information: Link1

Hope this helps. Please let me know if you have any further questions.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 FBC Gallery Image

Fabric Monthly Update - March 2024

Check out the March 2024 Fabric update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors