Anyone having success with Relativity and being able to treat numerics like SQL where you can encase with single quote? In SQL for an int field select * from table where add_date = '20160701' works the same as select * from table where add_date = 20160701. However with Relativity this field is a PIC 9()8) and running without the single quote works but using the single quote I get message INCOMPATIBLE TYPES IN PREDICATE. Trying to save a boat load of code changes as we are migrating to Relativity from an ODBC connector that worked like SQL.
#ReativitynumerichandlingFor date data, I would transform the underlying COBOL integer to a SQL date using a date transform.
Anyone having success with Relativity and being able to treat numerics like SQL where you can encase with single quote? In SQL for an int field select * from table where add_date = '20160701' works the same as select * from table where add_date = 20160701. However with Relativity this field is a PIC 9()8) and running without the single quote works but using the single quote I get message INCOMPATIBLE TYPES IN PREDICATE. Trying to save a boat load of code changes as we are migrating to Relativity from an ODBC connector that worked like SQL.
#ReativitynumerichandlingThanks Tom. I should have used a better example because I have instances of data that in SQL would be defined as smallint, int, and decimal that currently another programmer has always encased where values in single quotes.
Anyone having success with Relativity and being able to treat numerics like SQL where you can encase with single quote? In SQL for an int field select * from table where add_date = '20160701' works the same as select * from table where add_date = 20160701. However with Relativity this field is a PIC 9()8) and running without the single quote works but using the single quote I get message INCOMPATIBLE TYPES IN PREDICATE. Trying to save a boat load of code changes as we are migrating to Relativity from an ODBC connector that worked like SQL.
#ReativitynumerichandlingYour existing code would appear to be taking advantage of some sort of relaxation of the ODBC SQL language definition.
In looking at the documented list of errors the Relativity's SQL engine produces, I do not find any that are close to "INCOMPATIBLE TYPES IN PREDICATE". Since you are using a linked table (from SQL Server) are you sure that the WHERE predicate is even getting passed through to the underlying SQL engine?
[This can be a real source of confusion, in products that have their own SQL engines. The MS Access 'Jet Engine' was/is notorious for mangling queries badly before they get to the underlying ODBC SQL engine. It might be useful to turn on ODBC tracing and see what is getting passed to Relativity, as well as determining if this error message is originated in Relativity.]
Anyone having success with Relativity and being able to treat numerics like SQL where you can encase with single quote? In SQL for an int field select * from table where add_date = '20160701' works the same as select * from table where add_date = 20160701. However with Relativity this field is a PIC 9()8) and running without the single quote works but using the single quote I get message INCOMPATIBLE TYPES IN PREDICATE. Trying to save a boat load of code changes as we are migrating to Relativity from an ODBC connector that worked like SQL.
#ReativitynumerichandlingI can get this message 'Error in predicate' directly in the SQL window inside of Relativity Designer. Has anyone had any success or horror stories by setting a numeric column to be a character type in the table? This was suggested as a work around but I am concerned that it may open up a can of worms with Relativity accepting an entry like 'ABC' and trying to hand off to Cobol with a PIC 9(03) clause.
Anyone having success with Relativity and being able to treat numerics like SQL where you can encase with single quote? In SQL for an int field select * from table where add_date = '20160701' works the same as select * from table where add_date = 20160701. However with Relativity this field is a PIC 9()8) and running without the single quote works but using the single quote I get message INCOMPATIBLE TYPES IN PREDICATE. Trying to save a boat load of code changes as we are migrating to Relativity from an ODBC connector that worked like SQL.
#ReativitynumerichandlingMy guess (and it is a guess) is that the Designer uses some .Net gadget for the SQL window. Again, the way to see if it is Relativity itself returning this message or the client software (possibly embedded in the Designer) is to turn on ODBC tracing and look at the message traffic. If it is Relativity itself, then raise a support incident - against the documentation if nothing else.
Anyone having success with Relativity and being able to treat numerics like SQL where you can encase with single quote? In SQL for an int field select * from table where add_date = '20160701' works the same as select * from table where add_date = 20160701. However with Relativity this field is a PIC 9()8) and running without the single quote works but using the single quote I get message INCOMPATIBLE TYPES IN PREDICATE. Trying to save a boat load of code changes as we are migrating to Relativity from an ODBC connector that worked like SQL.
#ReativitynumerichandlingI will dig into the trace issue and go from there.
