Forum Discussion

c_cook28954's avatar
c_cook28954
Advocate II
2 years ago

Advanced ODBC Query - Pulling through a Summarized/Pre-Merged Column

Hi all, 

 

I'm posting as I am trying to figure out how I can pull a summarized column through from an ODBC table using an Advanced ODBC query. 

 

Why I want to do this:

1. The table that I'm querying is quite large - two million rows within the last 20 months - and I'm trying to condense the table as much as I can so that I can reduce the processing power required. 

2. The tables are connected based on two criteria - Branch & Part_No - and it's going to take me a bit - of both time and processing power - to replicate this within Power BI so I'd like to use the summarized column being as it's already there. 

 

What does my query look like currently:

 

let
    Source = Odbc.Query("XXXXXXXXXXXXXX", 
    "SELECT 
    InTrans.BRANCH, 
    InTrans.FRANCHISE, 
    InTrans.PART_NO, 
    InTrans.Trans_Datetime, 
    InTrans.TYPE,
    InTrans.QTY, 
    InTrans.SALE_VAL, 
    InTrans.COST_VAL, 
    InTrans.Salesman,
    InTrans.REF_NO,
    InTrans.ORIGINAL_INVOICE_NO,
    InTrans.File_No,
    InTrans.trans_id
        #(lf)FROM 
            InTrans
        #(lf)WHERE 
            InTrans.Trans_Datetime >= DATEADD(month, -20, GETDATE())
        #(lf)AND 
            InTrans.Trans_Datetime < GETDATE()"),

 

 

Name of the summarized column I want to pull through: InMaster - image below for reference:

 

I've tried adding a column to the query that was InTrans.InMaster but I got the below error:

 

DataSource.Error: ODBC: ERROR [42S22] [SAP][ODBC Driver][SQL Anywhere]Column 'InMaster' not found
Details:
    DataSourceKind=Odbc
    DataSourcePath=XXX=XXXXXXXXXXX
    OdbcErrors=[Table]

 

 

I've tried pulling through the entire width of the table using date filters to condense the depth but the query has been running for over 10 minutes so I don't assume it's going to be efficient to use - you'll find the query I used below for reference:

 

let
    Source = Odbc.Query("XXXXXXXXX", 
    "SELECT 
    *
        #(lf)FROM 
            InTrans
        #(lf)WHERE 
            InTrans.Trans_Datetime >= DATEADD(month, -20, GETDATE())
        #(lf)AND 
            InTrans.Trans_Datetime < GETDATE()"),

 


I'm assuming that the answer is to do a join in the advanced query but I figured I'd ask to check.

 

Thanks in advance for any help.

 

Need Help

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Not clear what you are describing here. Is InMaster part of the InTrans table and you forgot to include it in the Select statement?

    • c_cook28954's avatar
      c_cook28954
      Advocate II
      2 years ago

      lbendlin InMaster is a separate table to InTrans but it's stored in the server as an expandable column in InTrans.

       

      I know this because if I import the entire table I can see the column and click a drop down to select the columns from InMaster that I want to expand for. 

       

      It looks exactly the same as you'd expect it to you if you did a merge in Power Query between two tables but I just can't pull it through using the SQL query.

       

      The only thing I've been able to do to get it to kind of work is do an inner join in the SQL and state exactly which column from InMaster I want.

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        Yes, that's what you are supposed to do. Run a native SQL query with all required joins. Does that not work for you?