Jump to content
Please Use CODE Tags

SQL ETL can't get OLTP table

JupiteL
By JupiteL
in Programming
 Share
Posted

Hi, I am working on my school project. I have a little problem that my friends can't help me because they said it's a simple problem but their solutions won't work for me.

--TimeDimensionIF EXISTS (SELECT * FROM FilterTimeStamp WHERE TableName = 'TimeDimension')BEGINSELECT DISTINCT	[Date] = CAST([Date] AS Date),	[Year] = YEAR([Date]),	[Quarter] = 		CASE		        WHEN MONTH([Date]) BETWEEN 1 AND 3 THEN 1			WHEN MONTH([Date]) BETWEEN 4 AND 6 THEN 2			WHEN MONTH([Date]) BETWEEN 7 AND 9 THEN 3			WHEN MONTH([Date]) BETWEEN 10 AND 12 THEN 4		END,	[Month] = MONTH([Date]),	[Day] = DAY([Date])FROM(	SELECT BreadExpiredDate AS [Date] FROM MsBread	UNION	SELECT PaymentDate AS [Date] FROM TrPayment	UNION	SELECT SellDate AS [Date] FROM TrHeaderSell	UNION	SELECT ProductionDate AS [Date] FROM TrHeaderProduction	UNION	SELECT DistributionDate AS [Date] FROM TrHeaderMasterDistribution	UNION	SELECT PayrollDate AS [Date] FROM TrHeaderPayroll) xWHERE [Date] > (SELECT LastETL FROM BreadOLAP.dbo.FilterTimeStamp WHERE TableName = 'TimeDimension')ENDELSEBEGINSELECT DISTINCT	[Date] = CAST([Date] AS Date),	[Year] = YEAR([Date]),	[Quarter] = 		CASE			WHEN MONTH([Date]) BETWEEN 1 AND 3 THEN 1			WHEN MONTH([Date]) BETWEEN 4 AND 6 THEN 2			WHEN MONTH([Date]) BETWEEN 7 AND 9 THEN 3			WHEN MONTH([Date]) BETWEEN 10 AND 12 THEN 4		END,	[Month] = MONTH([Date]),	[Day] = DAY([Date])FROM(	SELECT PaymentDate AS [Date] FROM TrPayment	UNION	SELECT SellDate AS [Date] FROM TrHeaderSell	UNION	SELECT ProductionDate AS [Date] FROM TrHeaderProduction	UNION	SELECT DistributionDate AS [Date] FROM TrHeaderMasterDistribution	UNION	SELECT PayrollDate AS [Date] FROM TrHeaderPayroll) xEND

It appear that my ETL query can't get tables from my OLTP server (TrPayment, TrHeader, etc.) despite having them already in the database and have the correct name.

 

Sorry if the question is confusing.

Link to comment
https://linustechtips.com/topic/528818-sql-etl-cant-get-oltp-table/
Share on other sites
Link to post
Share on other sites
Posted

Hi, I am working on my school project. I have a little problem that my friends can't help me because they said it's a simple problem but their solutions won't work for me.

--TimeDimensionIF EXISTS (SELECT * FROM FilterTimeStamp WHERE TableName = 'TimeDimension')BEGINSELECT DISTINCT	[Date] = CAST([Date] AS Date),	[Year] = YEAR([Date]),	[Quarter] = 		CASE		        WHEN MONTH([Date]) BETWEEN 1 AND 3 THEN 1			WHEN MONTH([Date]) BETWEEN 4 AND 6 THEN 2			WHEN MONTH([Date]) BETWEEN 7 AND 9 THEN 3			WHEN MONTH([Date]) BETWEEN 10 AND 12 THEN 4		END,	[Month] = MONTH([Date]),	[Day] = DAY([Date])FROM(	SELECT BreadExpiredDate AS [Date] FROM MsBread	UNION	SELECT PaymentDate AS [Date] FROM TrPayment	UNION	SELECT SellDate AS [Date] FROM TrHeaderSell	UNION	SELECT ProductionDate AS [Date] FROM TrHeaderProduction	UNION	SELECT DistributionDate AS [Date] FROM TrHeaderMasterDistribution	UNION	SELECT PayrollDate AS [Date] FROM TrHeaderPayroll) xWHERE [Date] > (SELECT LastETL FROM BreadOLAP.dbo.FilterTimeStamp WHERE TableName = 'TimeDimension')ENDELSEBEGINSELECT DISTINCT	[Date] = CAST([Date] AS Date),	[Year] = YEAR([Date]),	[Quarter] = 		CASE			WHEN MONTH([Date]) BETWEEN 1 AND 3 THEN 1			WHEN MONTH([Date]) BETWEEN 4 AND 6 THEN 2			WHEN MONTH([Date]) BETWEEN 7 AND 9 THEN 3			WHEN MONTH([Date]) BETWEEN 10 AND 12 THEN 4		END,	[Month] = MONTH([Date]),	[Day] = DAY([Date])FROM(	SELECT PaymentDate AS [Date] FROM TrPayment	UNION	SELECT SellDate AS [Date] FROM TrHeaderSell	UNION	SELECT ProductionDate AS [Date] FROM TrHeaderProduction	UNION	SELECT DistributionDate AS [Date] FROM TrHeaderMasterDistribution	UNION	SELECT PayrollDate AS [Date] FROM TrHeaderPayroll) xEND

It appear that my ETL query can't get tables from my OLTP server (TrPayment, TrHeader, etc.) despite having them already in the database and have the correct name.

 

Sorry if the question is confusing.

What are your friends suggesting?  What are you trying to write in?

Please spend as much time writing your question, as you want me to spend responding to it.  Take some time, and explain your issue, please!

Spoiler

If you need to learn how to install Windows, check here:  http://linustechtips.com/main/topic/324871-guide-how-to-install-windows-the-right-way/

Event Viewer 101: https://youtu.be/GiF9N3fJbnE

 

Link to post
Share on other sites
Posted
  • Author

What are your friends suggesting?  What are you trying to write in?

They said I wrote the wrong tables and haven't execute it on my OLTP, I wrote it correctly and have the tables executed on my OLTP server. I'm writing in SQL Server Management Studio and about to continue to work on SSIS if this part is done.

Link to post
Share on other sites
Posted

They said I wrote the wrong tables and haven't execute it on my OLTP, I wrote it correctly and have the tables executed on my OLTP server. I'm writing in SQL Server Management Studio and about to continue to work on SSIS if this part is done.

Look at your very first line.

Please spend as much time writing your question, as you want me to spend responding to it.  Take some time, and explain your issue, please!

Spoiler

If you need to learn how to install Windows, check here:  http://linustechtips.com/main/topic/324871-guide-how-to-install-windows-the-right-way/

Event Viewer 101: https://youtu.be/GiF9N3fJbnE

 

Link to post
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
 Share
Go to topic listing Programming

×