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


Tim Downie
 

I find its best to try apg the sql task to start with then you dont get these datatype mismatches which you can chase for ages
From: main@magicu-l.groups.io <main@magicu-l.groups.io> on behalf of Florian Groothuis <f.groothuis@...>
Sent: Monday, 22 May 2017 8:42:06 AM
To: main@magicu-l.groups.io
Subject: Re: [magicu-l] Can we load magic memory table with sql script - unipaas 1.9
 

And found the problem. I placed the result in a Alpha15, but the SQL-field is defined as Alpha4. So Magic fills the remaining positions with NULL() instead of CHR(32). Changing my virtual to Alpha4 solved this issue.

 

Met vriendelijke groet - With kind regards,

Florian Groothuis
Analist/programmeur
+31 (0)6 21927914


meilink.eu

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


Van: main@magicu-l.groups.io [mailto:main@magicu-l.groups.io] Namens Florian Groothuis
Verzonden: maandag 22 mei 2017 10:38
Aan: main@magicu-l.groups.io
Onderwerp: Re: [magicu-l] Can we load magic memory table with sql script - unipaas 1.9

 

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:

 

Kenmerk

Groep

Artikel

Omschrijving

100007AFMRPLART

GES

002027

Geschaafd vuren 20x145

100007AFMRPLART

GES

002028

Geschaafd vuren 30x40

100007AFMRPLART

GES

002029

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.

 

Gr

Florian

 

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

 

Thanks Tim.  Yes it works.

Lado

 

 

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


To: main@magicu-l.groups.io
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.

 

Thx

 

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: main@magicu-l.groups.io <main@magicu-l.groups.io> on behalf of Lado Wali <ladowali@...>
Sent: Thursday, 18 May 2017 6:55:30 PM
To: main@magicu-l.groups.io
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.

Thx

Lado

 

Inline image 1

 

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

Lado,

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 ?
>
>Thanks
>Lado
>


 

 

Met vriendelijke groet - With kind regards,

Florian Groothuis
Analist/programmeur
+31 (0)6 21927914

 


meilink.eu
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 main@magicu-l.groups.io to automatically receive all group messages.