LEFT JOIN with another database connection

I am getting this error:
[attachment=2]2016-10-21 11_14_15-192.168.11.10 - Remote Desktop Connection.png[/attachment]

i am trying to LEFT JOIN the cutomer ID # from a MSSQL database with text descriptions stored in a MySQL database. The MSSQL is the customer’s database and I cannot touch it. Does the JOIN alias support a remote database?

[attachment=1]2016-10-21 11_12_28-192.168.11.10 - Remote Desktop Connection.png[/attachment]

[attachment=0]2016-10-21 11_13_07-192.168.11.10 - Remote Desktop Connection.png[/attachment]

You can’t run any query over more than one database connection.

arrrrg, was afraid of that.
Thanks
Kevin

Are you allowed to do a Linked Server to the mySQL server?
If you can set it up as a linked server then you can do joins against it.

The easiest way might be to bind a custom dataset property to the query for the main table with an extra empty column for the customer name, then add a property-changed event for that custom property. That event would run your customer query and assign the proper customer description to the customer column of the other dataset and assign that new dataset to the actual Data property.

I (very quickly) threw this code together as an idea of how you could do this, so it’s not guaranteed bug free:

if event.propertyName == "MyCustomProperty":
    #Run customer query
    customerDataset = system.db.runQuery("SELECT customerid, description FROM QualityCheeseMySQL.customers")
    #Convert to a dict for faster customer lookups in the format {id:"description"}
    customerDict = {}
    for row in system.dataset.toPyDataSet(customerDataset): #using a pyDataSet to make use of python's iterators
        customerDict[row["customerid"]] = row["description"]
    
    #Get the data from the custom property where the main query is bound
    queryData = system.dataset.toPyDataSet(event.source.MyCustomProperty)
    #Use the GL1A field to get the corresponding description from the dictionary
    for row in queryData:
        row["customer"] = (customerDict[row["GL1A"]] if row["GL1A"] in customerDict else None)
    
    #Convert back to a dataset and assign it
    event.source.data = system.dataset.toDataSet(queryData)

And your bound query would need to include GL1A for the lookup, so it would look something like:

SELECT
    DEVICE,
    GL07 as 'Product',
    GT33 as 'Lot #',
    GL1A,
    LEFT(PD00,2) as 'Unit',
    CAST(SUBSTRING(PD00,5,1) AS DECIMAL) +
    CAST(SUBSTRING(PD00,7,3) AS DECIMAL)/1000 as 'Weight'

FROM
    PackageData

WHERE
    {Root Container.Report Viewer.WhereClause}

ORDER BY GT33 desc

Again, this code is just an example and has not been tested yet!

Hope that helps.

[quote=“TedNewman”]The easiest way might be to bind a custom dataset property to the query for the main table with an extra empty column for the customer name, then add a property-changed event for that custom property. That event would run your customer query and assign the proper customer description to the customer column of the other dataset and assign that new dataset to the actual Data property.

I (very quickly) threw this code together as an idea of how you could do this, so it’s not guaranteed bug free:

if event.propertyName == "MyCustomProperty":
    #Run customer query
    customerDataset = system.db.runQuery("SELECT customerid, description FROM QualityCheeseMySQL.customers")
    #Convert to a dict for faster customer lookups in the format {id:"description"}
    customerDict = {}
    for row in system.dataset.toPyDataSet(customerDataset): #using a pyDataSet to make use of python's iterators
        customerDict[row["customerid"]] = row["description"]
    
    #Get the data from the custom property where the main query is bound
    queryData = system.dataset.toPyDataSet(event.source.MyCustomProperty)
    #Use the GL1A field to get the corresponding description from the dictionary
    for row in queryData:
        row["customer"] = (customerDict[row["GL1A"]] if row["GL1A"] in customerDict else None)
    
    #Convert back to a dataset and assign it
    event.source.data = system.dataset.toDataSet(queryData)

And your bound query would need to include GL1A for the lookup, so it would look something like:

SELECT
    DEVICE,
    GL07 as 'Product',
    GT33 as 'Lot #',
    GL1A,
    LEFT(PD00,2) as 'Unit',
    CAST(SUBSTRING(PD00,5,1) AS DECIMAL) +
    CAST(SUBSTRING(PD00,7,3) AS DECIMAL)/1000 as 'Weight'

FROM
    PackageData

WHERE
    {Root Container.Report Viewer.WhereClause}

ORDER BY GT33 desc

Again, this code is just an example and has not been tested yet!

Hope that helps.[/quote]

Thanks, was thinking something along that solution, but ended up getting to create the required table in MSSQL database