question

AkilaAriyarathne-4992 avatar image
0 Votes"
AkilaAriyarathne-4992 asked AkilaAriyarathne-1261 commented

Azure Stream Analytics - SQL - Showing 0 rows joining with reference input with SQL Server Reference input

Even reference data available in joining SQL Server reference input tables, no result and no errors

FROM table, Event hub input
JOIN tables, SQL Server reference input
INTO, SQL Server table
SELECT a.Id
,SUM(r.FYQ1_c) AS FYQ1c
,SUM(r.FYQ2
c) AS FYQ2c
,SUM(r.FYQ3
c) AS FYQ3c
,SUM(r.FYQ4
c) AS FYQ4c
INTO
[OutRevenueSplitVsAccount]
FROM
[RevenueSplitEvHuBIn] AS r TIMESTAMP BY LastModifiedDate
INNER JOIN [OpportunityInputTbl] AS o ON r.Opportunity
_c = o.Id
INNER JOIN [AccountInputTbl] AS a ON o.AccountId = a.Id
GROUP BY a.Id, TumblingWindow(day,2)

azure-stream-analytics
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @AkilaAriyarathne-4992 and welcome to Microsoft Q&A.

While I begin my attempt to reproduce your issue, could you please manually check your data as to whether the join -- when working correctly -- produces results?

1 Vote 1 ·

Yes, I checked manually and it worked, Also I tried to upload sample data and again it failed.

0 Votes 0 ·

@AkilaAriyarathne-4992 there is one thing about your query that stands out.

The query you shared starts with FROM. Is this intentional or did you miss a line? To my understanding, the query should start with SELECT or WITH.

0 Votes 0 ·

SELECT a.Id
,SUM(r.FYQ1_c) AS FYQ1c
,SUM(r.FYQ2c) AS FYQ2c
,SUM(r.FYQ3c) AS FYQ3c
,SUM(r.FYQ4c) AS FYQ4c
INTO
[OutRevenueSplitVsAccount]
FROM
[RevenueSplitEvHuBIn] AS r TIMESTAMP BY LastModifiedDate
INNER JOIN [OpportunityInputTbl] AS o ON r.Opportunity_c = o.Id
INNER JOIN [AccountInputTbl] AS a ON o.AccountId = a.Id
GROUP BY a.Id, TumblingWindow(day,2)

Query starts from SELECT as above, its just an explanation about reference

0 Votes 0 ·

Hi @MartinJaffer-MSFT, I still unable to fix the issue, Highly apricate if u help on this.

0 Votes 0 ·

0 Answers