Architecting, installing and maintaining your SAS environment

DI studio, issue with "Subquery" in SQL join transformation.

Reply
Regular Contributor
Posts: 160

DI studio, issue with "Subquery" in SQL join transformation.

 

Hi all,

I've found probably general issue in ETLs that have SQL join transformation with sub-query ,such ETLs can't be promoted correctly to another servers or even same server due SPK packages from such jobs are damaged.

I reproduced the issue on small test job.

OS - AIX, SAS 9.3.

So correct etls looks like in attached image correctETL.bmp (I erase table names due high security on companySmiley Happy

Correct SQL code from SQL join transformation  with subquery  looks like that:correctSQL.bmp

After import export, or even after simple Copy such ETL to same folder in DI studio, etls demages and looks like that:incorrectETL.bmp

And SQL code loses subquery after IN clause:incorrectSQL.bmp

Export command itself looks like that:

 

ExportPackage -profile "..." -package "C:\...\package.spk"

-objects "/.../.../test(job)

-subprop

-types

"Condition,BusinessRuleFlow,ExternalFile,Cube,SearchFolder,Table,GeneratedTransform,OLAPSchema,InformationMap.OLAP,Measure,Column,Job.CubeBuild,Action,Library,MiningResults,Hierarchy,DeployedJob,CalculatedMeasure,InformationMap.Relational,RootFolder,Document,ConditionActionSet,Prompt,Dimension,Note,StoredProcess,PromptGroup,Job,MessageQueue,Service.SoapGenerated,Level,SharedDimension,DeployedFlow"

So maybe some type of meta-object that needs to be exported omitted(lost), not sure.

It looks like Java/DI studio issue, maybe some hotfix can fix it.

Offcource almost each subquery can be replaced by inner join etc., but anyway - if somebody experienced same error - please share how it can be fixed.

Thanks!


incorrectSQL.bmpcorrectETL.bmpincorrectETL.bmpcorrectSQL.bmp
Super User
Posts: 5,257

Re: DI studio, issue with "Subquery" in SQL join transformation.

It there's any type of issues that should being sent to SAS tech support, this it.

Have you exported manually? In the wizard you could check for dependant objects.

And you are sure that all tables are in the target environment , or part of the package?

Data never sleeps
Regular Contributor
Posts: 160

Re: DI studio, issue with "Subquery" in SQL join transformation.

Hi Linus,

Thanks for good question.

Yep, I tried to export manually, and all source target table are in target env.

Furthermore,I even tried export to same environment from which I made spk - same result.

Additionaly even if just copy needed ETL and paste it into same folder -creates copy with "Copy of " prefix, and that copy will also has same issue(absolutely same).

So it looks like very similar to some java/DI issue. I tried to make all those steps from a few separate machines, not only mine,-same result.

Suppose you are right and this is question rather for SAS support, but co-workers informed me that they experienced very similar issues on SAS 9.1.3, so I just hope that someone here found out before some nice fix, more clever then just substitude subquery to inner join or smth. like that.

Thanks!

Ask a Question
Discussion stats
  • 2 replies
  • 398 views
  • 0 likes
  • 2 in conversation