Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Solved Static control

More
17 Feb 2016 11:36 - 08 Mar 2016 16:08 #1 by Josserand BRINON
Static control was created by Josserand BRINON
Hi,

I would like to know how to run a static control in a process.
I know how to use a flow control, but in the current case, I cannot use a flow control.

I've tried to drag&drop :
- the reject template in the process
- the table to ckeck onto the template.
But the compilation doesn't succeed. I guess it is not the good way to proceed.

Thanks.
Last edit: 08 Mar 2016 16:08 by Josserand BRINON. Reason: Topic Icon changed to solved
More
18 Feb 2016 15:10 - 18 Feb 2016 15:10 #2 by Thomas BLETON
Replied by Thomas BLETON on topic Static control
After drag&drop the table on the template, try to rename it to "REF" instead. The reject template is expecting a metadata link named "REF".
Is it any better ? If not, please share the exact compilation error ;-)
Last edit: 18 Feb 2016 15:10 by Thomas BLETON.
More
18 Feb 2016 16:04 - 18 Feb 2016 16:05 #3 by Josserand BRINON
Replied by Josserand BRINON on topic Static control
Thanks Thomas, it works :)

But I'm surprised that all my conditions are not controled by the process...
On my table, I have declared:
- 1 primary key,
- 2 alternative key,
- 1 condition.

The process controls the PK, the condition, but anyone of the AK... I don't understand why.

Attachments:
Last edit: 18 Feb 2016 16:05 by Josserand BRINON. Reason: Attachment
More
19 Feb 2016 14:57 #4 by Thomas BLETON
Replied by Thomas BLETON on topic Static control
Actually this works in a mapping context. But not in a single-process (static) context.
This is due to a missing parameter in our Rdbms technology, we will add it to the next Designer version.

Here is how to patch your workspace if you can't wait:
- Menu Window / Show view / Internal resources
- Search for "rdbms/rdbms.tech"
- Right click on it and "Import into workspace" (accept creation of "technology" project if prompted)
- Go back to your Project explorer and open technology/**/rdbms.tech
- Deploy hierarchy to the node "com.stambia.rdbms.datastore : datastore"
- Right click on this "com.stambia.rdbms.datastore : datastore" node and choose New > Xpath Expression
- Give it the following properties : Code="AK_REF", Expression="ak", IsProvider="false", Description=""
- Save... it should work.
More
19 Feb 2016 18:10 #5 by Josserand BRINON
Replied by Josserand BRINON on topic Static control
B) Perfect! It works...

But now, I'm facing another question, about the detection method of the non-unique AK.
In the reject template, it's written:
where	exists (select 'X' 
				from 	%x{md:physicalPath($REF,'checkedName')}x% %x{$REF/ref:target()/tech:tableAliasWord()}x% E
				where	%x{md:patternList($REF/ref:columns()[if ($REF/tech:rejectMode()='S') then tech:isPK() else tech:isUK()],'C.[COL_NAME]\t= E.[COL_NAME]', '\n\t\t\t\tand\t')}x%
				group by %x{md:patternList($CONSTRAINT_REF/ref:columns(),'E.[colName]', ',\n\t\t\t')}x%
				having	count(*) > 1 
		)
After compilation:
where	exists (select 'X' 
				from 	[database].[schema].[myTable] as E
				where	C.[pk_column]	= E.[pk_column]
				group by E.[ak_column]
				having	count(*) > 1 
		)

If I vulgarize, it means the query wants to "look for non-unique ak_column per pk_column".
But if my pk_column is unique, there won't never be any duplicate values on ak_column... Am I wrong? :huh:

I have checked that with my data:
myTable contains some non-unique values on "ak_column".
But the template does not detect any rejects. :(

Maybe I have an idea about what query would do the job... But I have to try it before.
My idea: not using an "exists", but an "inner join" with quite the same subquery (which detects duplicate values).

To be continued...
More
23 Feb 2016 10:15 - 23 Feb 2016 10:16 #6 by Thomas BLETON
Replied by Thomas BLETON on topic Static control
Hmm yes it seems there is a mistake in the template's code.
Can you try replacing the string "tech:isPK()" with the string "tech:isAK()" ?
Does it give satisfying results ?
If yes, then I'll submit the modification to Stambia's dev team for validation and publication in the next official template release.
Last edit: 23 Feb 2016 10:16 by Thomas BLETON.
More
25 Feb 2016 10:01 #7 by Josserand BRINON
Replied by Josserand BRINON on topic Static control
Yes, thank you Thomas, your correction fixes the mistake.

I continue with other questions related to static controls.

In my case, I am controling historized tables (SCD). Consequently, my unique keys are unique, but only for active records. This aspect is not managed by the template, is it?
The solution may be creating a CK instead of an AK, that allows to write the SQL code with the condition "active=1"...
More
25 Feb 2016 15:10 #8 by Thomas BLETON
Replied by Thomas BLETON on topic Static control
You're right, the Reject template is not designed for static control on SCD tables.
The CK solution can surely do the job!

Note: you can create your own template for your specific needs, just copy the standard template with another file name, and store it outside of the "template.generic" / "template.specific" folder (template.<yourcompany> ? ;-))