CODEWITHSUNDEEP
sqlleft-jointeradata
omzeybek
omzeybek

Reputation: 315

Combining two select expressions with left join

Hi I am trying to collect aggregated time series data for my regression study. I need to left join multiple selects in one longitudinal table with a date key. However because i am dealing with a considerably large data frame, i need to limit date interval of my query. 


SELECT ts.date_of_transaction,
           s.rev

    FROM db.transaction as ts
    --Where ts.date_of_transaction > date '2019-08-13'

LEFT JOIN 

(select date_of_transaction, sum(amount) as rev from db.transaction where  date_of_transaction >= date '2019-08-13' and
    main_group_number=200 and
    group_number=15 and
    class_number in (45,25,20,30)
    group by date_of_transaction) as s 

ON  ts.date_of_transaction = s.date_of_transaction;

When i closed where clause at the fourth row, my query works without any problem. However, when I added my time filter to query for the outer table I receive the following error...

SELECT Failed. 3706: Syntax error: expected something between a string or Unicode character literal and the 'LEFT' keyword.

Upvotes: 0

Views: 1141

Answers (4)

dnoeth
dnoeth

Reputation: 60472

Depending on your actual query you might be able to replace the Left Join by a Windowed Aggregate plus Case (especially when you access the same table multiple times with different Where-conditions or different levels of aggregation), e.g.

SELECT ts.date_of_transaction,
       sum(case when main_group_number=200
                 and group_number=15
                 and class_number in (45,25,20,30)
                then amount
            end)
       over (partition by date_of_transaction) as rev
FROM db.transaction as ts
Where ts.date_of_transaction > date '2019-08-13'

for my regression study

Teradata has some statistical/regression functions builtin, e.g. KURTOSIS, REGR_SLOPE, ...

Upvotes: 0

Mr Lister
Mr Lister

Reputation: 46559

You can't do a JOIN in the WHERE clause of a SELECT. Put the WHERE at the very end instead.

SELECT ts.date_of_transaction,
           s.rev

    FROM db.transaction as ts

LEFT JOIN 

(select date_of_transaction, sum(amount) as rev from db.transaction where  date_of_transaction >= date '2019-08-13' and
    main_group_number=200 and
    group_number=15 and
    class_number in (45,25,20,30)
    group by date_of_transaction) as s 

ON  ts.date_of_transaction = s.date_of_transaction

Where ts.date_of_transaction > date '2019-08-13'

Upvotes: 2

Excelnoobster
Excelnoobster

Reputation: 129

Easiest way -

Where ts.date_of_transaction > '13 aug 2019'

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

You have error in this section. There are many ways to convert your string value into date format. You may find this link for help. link

select date_of_transaction, sum(amount) as rev from db.transaction where 
 date_of_transaction >= cast('2019-08-13' as date) and    ---- here instead of date You need to cast your string to date
    main_group_number=200 and
    group_number=15 and
    class_number in (45,25,20,30)
    group by date_of_transaction

Upvotes: 0

Related Questions