topic Re: DI studio, issue with "Subquery" in SQL join transformation. in Administration and Deployment

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

Yura2301 — Tue, 21 Oct 2014 09:09:59 GMT

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 company:)

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!

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

LinusH — Tue, 21 Oct 2014 10:34:44 GMT

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?

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

Yura2301 — Tue, 21 Oct 2014 10:42:24 GMT

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!