Re: Can we load magic memory table with sql script - unipaas 1.9

Florian Groothuis

No it doesn’t.


I use a DirectSQL-statement to load a memory tabel. Using this statement:


Select Artikel.PartGrpCode,Artikel.PartCode,Artikel.Description

From dbo.T_Part as Artikel

inner join dbo.T_PartSelection as Selectie on Artikel.PartCode=Selectie.PartCode

where Artikel.PartObsInd=0 and Selectie.SelectionCode='PLK' and Artikel.PartGrpCode =':1'


The memory-table is filled but it is corrupted. The first 3 records look like this:









Geschaafd vuren 20x145




Geschaafd vuren 30x40




Geschaafd vuren 30x145


When I range on ‘GES’ I have an empty dataview.

A LEN(TRIM(GROEP)) results in 8 instead of 3

ASCIIVAL(MID(GROEP,x,1) results in 71-69-83-32-0-0-0-0


When I load the table using a batchtask with T_Part as main file and linking T_PartSelection with a reange on SelectionCode to populate the table I have the same recordset as a result but:


When I range on ‘GES’ I have a correct recordset.

A LEN(TRIM(GROEP)) results in 3

ASCIIVAL(MID(GROEP,x,1) results in 71-69-83-32-32-32-32-32


So,something in that DirectSQL-construction is corrupting my data.





Van: [] Namens Lado Wali
Verzonden: vrijdag 19 mei 2017 15:05
Onderwerp: Re: [magicu-l] Can we load magic memory table with sql script - unipaas 1.9


Thanks Tim.  Yes it works.




On May 19, 2017 1:59 AM, "Tim Downie" <tim_downie@...> wrote:

if its a batch it will loop all records the sql statement finds   -end = no before  - works just like having a main table



Sent: Thursday, 18 May 2017 9:04 PM

Subject: Re: [magicu-l] Can we load magic memory table with sql script - unipaas 1.9


How it will loop the record? 

Should I use batch task No Before but there will be no main table but direct sql.




On May 18, 2017 5:01 PM, "Tim Downie" <tim_downie@...> wrote:

Thts not going to work - Sql results needs to go into virtuals then init the link create with the virtuals or update var in record suffix

From: <> on behalf of Lado Wali <ladowali@...>
Sent: Thursday, 18 May 2017 6:55:30 PM
Subject: Re: [magicu-l] Can we load magic memory table with sql script - unipaas 1.9


Hi Steve,


Can you please see below and correct me if I am wrong. Its not adding record anyway in the memory table.




Inline image 1


On Thu, May 18, 2017 at 12:54 PM, Steven Blank <sgblank@...> wrote:


The result of a Direct SQL task will NOT be written to a physical table IF you select a memory-based database for the Result Database.

It would be nice if MSE gave us access to the memory table created to receive the result of a Direct SQL statement, or a way to extend the scope of it's existence a little beyond the immediate task, but until they do, you can use a Link Create operation in the batch task to copy the Direct SQL result into a memory table over which you DO have control.

Steve Blank

At 09:30 AM 5/18/2017, you wrote:
>Hi Group,
>Is there any way we can load magic memory table with direct sql data without writing it first to physical table and then write to memory table ?



Met vriendelijke groet - With kind regards,

Florian Groothuis
+31 (0)6 21927914

Meilink Beheer Borculo B.V. • Kamerlingh Onnesstraat 1
7271 AZ  Borculo • Nederland • +31 (0)545 253525
KvK 08009803 • Our general terms and conditions apply • Disclaimer

Join to automatically receive all group messages.