Create Dataset with SQL

[BonusCup]

Hi,

I'm very new to Designer Cloud and I'm trying to create a dataset with a custom SQL that I use in regular Designer SQL Editor. I've created the connection to our database and see all my tables but I keep getting the below error message:

Invalid SQL: Table not found - com.trifacta.datasystem.connect.exception.NoSuchTableException

There are 2 tables I'm trying to do an inner join to the main table.  I can successfully create 3 separate datasets for each individual table but I'd rather use a custom SQL.

Custom SQL(works in MS sql server manager and Designer SQL Editor):

select T2DCBDATA.dbo.tblClient.ClientID,
T2DCBDATA.dbo.tblClient.SearchName as ClientName,
T2DCBDATA.dbo.tblClientExtraDetails.ClientSortName,
T2DCBDATA.dbo.tblClient.AddressDetail,
T2DCBDATA.dbo.tblClient.City,
T2DCBDATA.dbo.tblState.Description as State,
T2DCBDATA.dbo.tblClient.PostCode,
T2DCBDATA.dbo.tblClient.Telephone,
T2DCBDATA.dbo.tblClient.Fax,
T2DCBDATA.dbo.tblClient.email,
T2DCBDATA.dbo.tblClient.URL

from T2DCBDATA.dbo.tblClientExtraDetails
inner join tblClient on T2DCBDATA.dbo.tblClient.ClientID = T2DCBDATA.dbo.tblClientExtraDetails.ClientID
inner join tblState on T2DCBDATA.dbo.tblClient.StateID = T2DCBDATA.dbo.tblState.StateID

[BonusCup]

Another observation.  I can successfully add the 'flight_data' table as a dataset by clicking the add button next to the name of the table.  If I try to add the table using the below query with the 'Create Dataset with SQL' option, the query will validate successfully but when I go to save it as a dataset I get the error message below the query.

select FlightExternal.dbo.flight_data.*
from FlightExternal.dbo.flight_data

Error message:
Could not create dataset: Java.io. Ioexception: unable to init multipart upload to s3://d-us-w1-p00027-us-west-2/futurama-production/384/futurama-production/384/1172/queryresults/ricardo.hernandez%40cbh.com/.trifacta/ingest-job-staging-a91e62a0-4a94-4768-bad0-8f0b77bc3d56/ingested_data.json.

[BonusCup · Accepted solution]

I also reached out to the Trifacta community.  Please see link below for solution.

Solution from Trifacta Community