Welcome Guest! Log in

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

Please Log in or Create an account to join the conversation.

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.

Please Log in or Create an account to join the conversation.

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

Please Log in or Create an account to join the conversation.

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.

Please Log in or Create an account to join the conversation.

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...

Please Log in or Create an account to join the conversation.

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.

Please Log in or Create an account to join the conversation.

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"...

Please Log in or Create an account to join the conversation.

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> ? ;-))

Please Log in or Create an account to join the conversation.