#25 Create substring an company

[Riccardo Prandini]

Best strategy to create a SUBSTRING function.
I have to write something like

Query=Query.addCondition(new BinaryCondition(Op.EQUAL_TO,new CustomSql("SUBSTR("+TBSTSETP_FLGABI+",1,1)"),"S"));

it puts out

(SUBSTR(~aliasDb.TBSTSETP.FLGABI,1,1) = 'S'))

I have a problem.

My query is like

    SelectQuery Query = new SelectQuery();
    Query= Query.addColumns(TBSTANSE_CDSERVIZ);
    ...
      Query=Query.addCondition(new BinaryCondition(Op.EQUAL_TO,new CustomSql("SUBSTR("+TBSTSETP_FLGABI+",1,1)"),"S"));

This generate

SELECT 
t13.CDSERVIZ,
t13.DSSERVIZ,
t8.CDTIPO,
t8.DSTIPO 
FROM 
~aliasDb.TBSTANSE t13,
~aliasDb.TBSTTIDP t8,
~aliasDb.TBSTSETP t12 
WHERE 
(
(t8.CDTIPO = t12.TIPPRE) 
AND 
(t13.CDSERVIZ = t12.CODSER)
AND 
(t8.CDABI = '05034') 
AND 
(t13.CDABI = '05034') 
AND 
(t12.CODABI = '05034') 
AND 
(t12.DATINI <= CURRENT DATE) 
AND 
(t12.FLGABI > CURRENT DATE) 
AND 
(SUBSTR(~aliasDb.TBSTSETP.FLGABI,1,1) = 'S'))

===============================================
Question 1

I'd lke to view

AND 
(SUBSTR(t12.FLGABI,1,1) = 'S'))

is possible?

Question 2

Is it possible to extend some class (I don't know ) and create something like

Query.addCondition(new BinaryCondition(Op.EQUAL_TO,new SUBSTRINGCUSTOM TBSTSETP_FLGABI,1,1),"S"));

[Riccardo Prandini]

My solution is

FunctionCall SUBSTR = new FunctionCall(new CustomSql("SUBSTR")).addColumnParams(TBSTSETP_FLGABI).addNumericValueParam(1).addNumericValueParam(1);

Query=Query.addCondition(new BinaryCondition(Op.EQUAL_TO,
            SUBSTR
    ,"S"));

I have the problem that inside a FunctionCall there is

private SqlObjectList<SqlObject> _params = SqlObjectList.create();

DB2 function requeres ,<space> to separate items.

From

(SUBSTR(TBSTSETP.FLGABI,1,1) = 'S'))

TO

(SUBSTR(TBSTSETP.FLGABI, 1, 1) = 'S'))

This could became a constructor param of FunctionCall that set it to _params

[James Ahlborn]

Ticket moved from /p/openhms/bugs/22/

[James Ahlborn]

is this really a requirement? the db2 documentation shows examples with no spaces:

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_substr.html

[Riccardo Prandini]

Yes It Is a DB2 under HOST difference.

Il gio 27 feb 2020, 20:47 James Ahlborn jahlborn@users.sourceforge.net ha
scritto:

is this really a requirement? the db2 documentation shows examples with no
spaces:

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_substr.html

• [feature-requests:#25] Create substring an company*

Status: open
Group: Unassigned
Created: Tue Feb 25, 2020 03:44 PM UTC by Riccardo Prandini
Last Updated: Thu Feb 27, 2020 07:38 PM UTC
Owner: nobody

Best strategy to create a SUBSTRING function.
I have to write something like

Query=Query.addCondition(new BinaryCondition(Op.EQUAL_TO,new CustomSql("SUBSTR("+TBSTSETP_FLGABI+",1,1)"),"S"));

it puts out

(SUBSTR(~aliasDb.TBSTSETP.FLGABI,1,1) = 'S'))

I have a problem.

My query is like

SelectQuery Query = new SelectQuery();
Query= Query.addColumns(TBSTANSE_CDSERVIZ);
...
  Query=Query.addCondition(new BinaryCondition(Op.EQUAL_TO,new CustomSql("SUBSTR("+TBSTSETP_FLGABI+",1,1)"),"S"));

This generate

SELECT t13.CDSERVIZ,t13.DSSERVIZ,t8.CDTIPO,t8.DSTIPO FROM ~aliasDb.TBSTANSE t13,~aliasDb.TBSTTIDP t8,~aliasDb.TBSTSETP t12 WHERE ((t8.CDTIPO = t12.TIPPRE) AND (t13.CDSERVIZ = t12.CODSER)AND (t8.CDABI = '05034') AND (t13.CDABI = '05034') AND (t12.CODABI = '05034') AND (t12.DATINI <= CURRENT DATE) AND (t12.FLGABI > CURRENT DATE) AND (SUBSTR(~aliasDb.TBSTSETP.FLGABI,1,1) = 'S'))

===============================================
Question 1

I'd lke to view

AND (SUBSTR(t12.FLGABI,1,1) = 'S'))

is possible?

Question 2

Is it possible to extend some class (I don't know ) and create something
like

Query.addCondition(new BinaryCondition(Op.EQUAL_TO,new SUBSTRINGCUSTOM TBSTSETP_FLGABI,1,1),"S"));

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/openhms/feature-requests/25/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[James Ahlborn]

this seems to be a very specific problem, so best left to CustomSql.

[Riccardo Prandini]

I think this is how sql should be done correctly.
if you call each table t1...tn also the occurence of each table should report the same tn and not the original one

SELECT 
t13.CDSERVIZ,t13.DSSERVIZ,
t8.CDTIPO,t8.DSTIPO 
FROM 
~aliasDb.TBSTANSE t13,
~aliasDb.TBSTTIDP t8,
~aliasDb.TBSTSETP t12 
WHERE 
(
(t8.CDTIPO = t12.TIPPRE) 
AND 
(t13.CDSERVIZ = t12.CODSER)
AND 
(t8.CDABI = '05034') 
AND 
(t13.CDABI = '05034') 
AND 
(t12.CODABI = '05034') 
AND 
(t12.DATINI <= CURRENT DATE) 
AND 
(t12.FLGABI > CURRENT DATE) 
AND 
(SUBSTR(~aliasDb.TBSTSETP.FLGABI,1,1) = 'S'))

if you call

~aliasDb.TBSTSETP t12 

this has to be used everywhere

(SUBSTR(~aliasDb.TBSTSETP.FLGABI,1,1) = 'S'))

is there any workaround to get?

(SUBSTR(t12.FLGABI,1,1) = 'S'))

[James Ahlborn]

I'm confused. i thought your original problem is that lack of spaces between the arguments, but the example you are showing does not have spaces. if you don't need the spaces, then everything should work correctly if you do something like:

new FunctionCall("SUBSTR").addCustomParams(TBSTSETP_FLGABI,1,1);

[Riccardo Prandini]

Yes this is the second problem i think that with a customizable substring query the things cold be resolved.

[James Ahlborn]

If you want a custom version of the equivalent functionality, you can create a "CustomFunctionCall" class by copying the FunctionCall class and changing the params initializer to:

private SqlObjectList<SqlObject> _params = SqlObjectList.create(", ");

[Riccardo Prandini]

Yes this one is the solution.

[James Ahlborn]

this isn't the first time i've heard the request for a space after a comma. what if i added a system property to set the default list delimiter to include a trailing space (for all comma separated lists)?

[Riccardo Prandini]

When we use the class we have to work with websphere and other application server managed by specific office an the customization via system propery is not an option everywhere.
We have a config file and we act at runtime .

Thanks

[James Ahlborn]

You can set system properties at runtime. you just need to use System.setProperty() before you start using the sqlbuilder classes (i'd do it in a static{} block in one of your main classes).

[Riccardo Prandini]

ok but the property has to be a lot specific to not mess around with other apps/ packages

[James Ahlborn]

https://sourceforge.net/p/openhms/code/HEAD/tree/sqlbuilder/trunk/src/main/java/com/healthmarketscience/sqlbuilder/SqlObjectList.java#l46