Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

I would like to write the output of a query into a Sequel Da...


Joe Beydoun

Recommended Posts

I would like to write the output of a query into a Sequel Database.

Below, statement 1 works, I write specific values into SQL with no problem.

Statement 2 , I get an invalid file error.

Im not familiar with the syntax to do this, please help.

--------------------------------statement 1 (works)---------------------------------

ENGINE SQLMSS SET DEFAULT_CONNECTION DWSQL

SQL SQLMSS

INSERT INTO [staging].[dbo].[DBCAR]

VALUES

(ENGLAND, 2,JAGUAR,V12XKE AUTO,CONVERTIBLE)

END

-RUN

--------------------------------statement 2 (does not work)---------------------------------

SQL

SELECT COUNTRY,SEATS,CAR,MODEL,BODYTYPE FROM CAR;

TABLE ON TABLE HOLD AS HOLDCAR

END

-RUN

ENGINE SQLMSS SET DEFAULT_CONNECTION DWSQL

SQL SQLMSS

INSERT INTO [staging].[dbo].[DBCAR]

SELECT * FROM HOLDCAR

END

-RUN

-EXIT

ERROR:

[42S02] Invalid object name

(FOC1500) : HOLDCAR.

Link to comment
Share on other sites

No one said it explicitly so I thought I would explain why your case did not work

 

 

 

joe.beydoun:

 

TABLE ON TABLE HOLD AS HOLDCAR

 

 

Is creating a temporary HOLD file on the filesystem. It does have a master file, but its NOT a SQL table.

You then did a SQL SQLMSS INSERT statement. And by specifying SQLMSS after SQL you are effectively saying I am talking to MS SQL directly - just replace &Vars and pass it on. But MS-SQL does not know about the file system hold file.

Link to comment
Share on other sites

Thanks for the help, I used the FORMAT SQLMSS to make it work. Im sure there is a simpler way of doing this, but Im sharing the code that works if someone else lands here:

In summary,

 

Using a normal FEX I hold my data into a FOCUS table.

I created two files on the sequel server, one to hold daily values and the other historic.

Using an SQL command, I write the FOCUS table into the daily SQL table (it drops and creates the table automatically so its fresh every time).

Write the data from the Daily table to another SQL table. (Primary key includes date, so it does not over write the daily data).

 

TABLE FILE DAILY_ACTIVITY

SUM VALUE

BY KPI

BY REPORT_DATE

ON TABLE HOLD AS KPI_WAREHOUSE_DAILY1 FORMAT FOCUS

END

-*************************************************************************

-WRITE FOCUS TABLE INTO DAILY KPI_WAREHOUSE_DAILY FILE IN SEQUEL TABLE

-************************************************************************

ENGINE SQLMSS SET DEFAULT_CONNECTION DWSQL

SQL

SELECT KPI,REPORT_DATE,VALUE FROM KPI_WAREHOUSE_DAILY1;

TABLE ON TABLE HOLD AS [staging].[dbo].[kpi_warehouse_daily] FORMAT SQLMSS

END

-RUN

-*************************************************************************

-WRITE DAILY TABLE INTO MAIN TABLE KPI_WAREHOUSE TO STORE THE DATA

-************************************************************************

SQL SQLMSS

INSERT INTO [staging].[dbo].[kpi_warehouse]

SELECT E01,E02,E03 FROM [staging].[dbo].[kpi_warehouse_daily]

END

-RUN

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...