Error when Using Select Expert to select an Alphanumeric field

Have you tried the "Verify Database" action in the database menu in Crystal?  It looks like the database metadata in the report may be out of synch with the actual contents of the database.

Yes. I did that. No errrors were found. I tried with another alphanumeric field and I got the same error. I then tried the Select with a numeric field and it worked.
 

In the field explorer under "database fields", is "Show Field Type" checked and does the field say "String [4]"?

Also, I noticed that you said "The field size in the file is 4 Character".  Is this a flat file being treated as a datasource or is it an object in a relational database?

It is an object in a relational database.

I have attached the SQL code that Crystal Reports 11 generated. the DPTNO should be CC and not the 0xCC that is shown.  

SELECT "RTEREJS"."TITM#", "ITEMASA"."ITD20", "RTEREJS"."QR31", "RTEREJS"."QR32", "RTEREJS"."QR33", "RTEREJS"."QR34", "RTEREJS"."QR35", "RTEREJS"."QR36", "RTEREJS"."QR37", "RTEREJS"."QR38", "RTEREJS"."QR39", "RTEREJS"."QR42", "RTEREJS"."QR43", "RTEREJS"."QR44", "RTEREJS"."QR45", "RTEREJS"."QR46", "RTEREJS"."QR54", "RTEREJS"."QR56", "RTEREJS"."QR57", "RTEREJS"."QR58", "RTEREJS"."QR60", "ITEMASA"."DPTNO"
 FROM   "S102H3FM"."CSJQUERY"."RTEREJS" "RTEREJS" INNER JOIN "S102H3FM"."AMFLIBY"."ITEMASA" "ITEMASA" ON "RTEREJS"."TITM#"="ITEMASA"."ITNBR"
 WHERE  "ITEMASA"."DPTNO"=0xCC

Select allOpen in new window

Sorry I forgot to mention that the Field type is String [8]

You need to have ' ' around the value

WHERE "DPTNO" = '0xCC'

mlmcc

When I put 'CC' in the value of the Select Expert, I get the same error and the code looks like this:

 SELECT "RTEREJS"."TITM#", "ITEMASA"."ITD20", "RTEREJS"."QR31", "RTEREJS"."QR32", "RTEREJS"."QR33", "RTEREJS"."QR34", "RTEREJS"."QR35", "RTEREJS"."QR36", "RTEREJS"."QR37", "RTEREJS"."QR38", "RTEREJS"."QR39", "RTEREJS"."QR42", "RTEREJS"."QR43", "RTEREJS"."QR44", "RTEREJS"."QR45", "RTEREJS"."QR46", "RTEREJS"."QR54", "RTEREJS"."QR56", "RTEREJS"."QR57", "RTEREJS"."QR58", "RTEREJS"."QR60", "ITEMASA"."DPTNO"
 FROM   "S102H3FM"."CSJQUERY"."RTEREJS" "RTEREJS" INNER JOIN "S102H3FM"."AMFLIBY"."ITEMASA" "ITEMASA" ON "RTEREJS"."TITM#"="ITEMASA"."ITNBR"
 WHERE  "ITEMASA"."DPTNO"=0x''CC''

Select allOpen in new window

Here is another SQL using another alphanumeric field. This field has a value of 1. It is String [2]. Again I am seeing 0x1 in the SQL. Somehow Crystal report seems to be treating everything as numeric? Is this possible?

 SELECT "RTEREJS"."TITM#", "ITEMASA"."ITD20", "RTEREJS"."QR31", "RTEREJS"."QR32", "RTEREJS"."QR33", "RTEREJS"."QR34", "RTEREJS"."QR35", "RTEREJS"."QR36", "RTEREJS"."QR37", "RTEREJS"."QR38", "RTEREJS"."QR39", "RTEREJS"."QR42", "RTEREJS"."QR43", "RTEREJS"."QR44", "RTEREJS"."QR45", "RTEREJS"."QR46", "RTEREJS"."QR54", "RTEREJS"."QR56", "RTEREJS"."QR57", "RTEREJS"."QR58", "RTEREJS"."QR60", "ITEMASA"."ITTYP"
 FROM   "S102H3FM"."CSJQUERY"."RTEREJS" "RTEREJS" INNER JOIN "S102H3FM"."AMFLIBY"."ITEMASA" "ITEMASA" ON "RTEREJS"."TITM#"="ITEMASA"."ITNBR"
 WHERE  "ITEMASA"."ITTYP"=0x1

Select allOpen in new window

The String[8] and String[2] are what is shown beside those fields in the CR Field Explorer?  Just making sure.

 When you go to Report > "Selection Formulas" > Record, what does it say?

 James

Put the whole thing in quotes

mlmcc

In the database expert, what provider are you using for the data source (OLE DB, ODBC)?  

It's been a couple of years, but I recall the ODBC driver for the AS/400 had about a thousand bells and whistles on it.  Though, if the Field Explorer is displaying correctly that the datatype is String [8] then that suggests that the problem is within Crystal.

On your data source in the Database Expert what are the properties on the data source?  In Database Expert, you can expand "Current Connections" in the tree and right click on the datasource.  You might find some direction to head in there.

I am using ODBC. I am not seeing anything strange. Exactly what should I be looking for?

The "Selection Formulas" has - {ITEMASA.DPTNO} = "CC"
The SQL though has - WHERE  "ITEMASA"."DPTNO"=0xCC

There's something in the configuration about "Convert binary data (CCSID 65535)".  Is this enabled or not?  You might try flipping that switch in the ODBC DSN.

It is enabled. If it is off the data is represented in hex.

What do you mean by "the data is represented in hex"?

 0x is hex notation, so, from that POV, "represented in hex" is what you're getting now.

 Or are you talking about the data coming into the report?

 James

The AS/400 ODBC drivers make available the ability to have the data returned exactly as it's stored.  This would usually only be used by application developers that need more control over EBCDIC <-> ASCII or for special handling of masked values in integers.

I'm nearly at a loss here.  gmollineau, if you look at the file (yes it's a file - the AS/400 treats files/tables as the same thing, like a maddening wave-particle duality) layout and confirm what the datatype is according to it.  I think you can get to this through the "Work With Files" menu screens.  Furthermore, is this a logical or physical file?  If it's logical (like a view or an index), I'm wondering if there's a data conversion taking place in that layer.

I wish I had some better ideas here, because the short story is that if the metadata captured by Crystal indicates that it thinks the field is a string[8], I can't imagine why it would try to cast your string parameter as anything else.

Maybe find more ways to break it.  What happens if you ask for GG rather than CC.  Since CC is legal hex, maybe if you give it some illegal hex you'll get some better error messages (not that the Crystal Error messages are the most forthcoming).

Thanks for the explanation agandau.  I was thinking that just maybe that setting was causing CR to interpret the CC as hex, but I guess that would have been too easy.  :-)


 It's not a solution, but it might also be interesting to try entering the hex value for "CC", which I think would be 4343, and seeing what happens.

 James

Actually, I think you do have the solution there James.

I just created a table in SQL Server and a couple of the columns with datatype of binary.  Crystal interprets them as strings.

CREATE TABLE [dbo].Q_24318429 (
	[MyKey] [int] NULL,
	[MyBinary01] [binary](1) NULL,
	[MyBinary02] [binary](2) NULL,
	[MyBinary10] [binary](10) NULL
) ON [PRIMARY]
 
insert into Q_24318429 values (1, 0xCC, 0xCC, 0xDEADBEEF)
 
select * from Q_24318429
 
MyKey       MyBinary01 MyBinary02 MyBinary10
----------- ---------- ---------- ----------------------
1           0xCC       0xCC00     0xDEADBEEF000000000000
 
(1 row(s) affected)

Select allOpen in new window

crystal-hex-as-string.JPG

I guess this only half the problem -- having some greater understanding of crystal's reponse to binary columns.  gmollineau's problem is that an error message is being returned whenever trying to perform this compare.

I've queried the iSeries/400 before using Client Access and HiT software drivers, and never had to concern myself with any conversions like this.  I think this issue is just peculiar to how the data is set up.  What we'd need to see is the file description from the 400.

It is a MAPICS/XA File. The Item Master
I tried attaching the report but the add file facility does not take the .rpt file.

I don't think there's a problem in the .rpt file.  Instead having some better understanding of what the data looks like natively.  If MAPICS is the application installed on the iSeries machine, it might have some documentation of the structures of the files it builds on the machine.

Do you still get the error message about invalid numeric?  I didn't get that when trying to reproduce the error as above.  And all I entered into the select expert was CC, with no quotation marks or anything, and it successfully pulled the record.

Did you try James' suggestion above entering 4343 into the select expert (with no quotation marks).  I'm curious whether you get an error message or any results back.  4343 would be the hex representation for ASCII CC, and I think C3C3 would be the EBCDIC.

gmollineau,

 EE only accepts certain file extensions and RPT isn't one of them.  If you change the extension to one of the accepted types (eg. TXT), you can u/l it that way.  You might want to add a note saying that the extension needs to be changed back to RPT.



 agandau,

 What I meant by "not a solution" is that _if_ entering the hex representation worked, it probably wouldn't be very practical to expect users to enter the hex representation for whatever characters they were looking for.  Besides which, it would probably only work if the hex digits were between 0 and 9 (as in the 4343 representation for CC).  If this is being passed using a numeric parameter, CR presumably isn't going to allow A - F.

 James

James, I understood you correctly - maybe I shouldn't have phrased that as you having the solution so much as having some ideas and answers.

You don't think typing in "416d65726963616e20496e737572616e6e652047726f75702c20496e632e" to filter on customer name "American Insurance Group Inc." is practical?  : )

When I ran the sample above in SQL, it did accect the "CC" as input and treated it correctly.  

gmollineau, I tried finding some MAPICS documentation online but didn't get too far.  I just strongly suspect this column is stored as a binary, and if it consistently displays as text, then the RPG must be decoding it.  Hopefully there aren't too many columns like this.

Please see sample Crystal file attached. It is named Stock-Status.rpt.txt.
I put the .txt to get it accepted by EE.
Stock-Status.rpt.txt

HI Agandau
I don't have any problems with numeric fields. It is only with the character (alphanumeric) fields.

Thank you James

So, you were able to get the CC records that way?  It'd be better if you could get the record selection working, but if that's just not going to work for some reason, at least this gives you a workaround.

 Assuming that the problem is how the field and/or parameter are being interpreted when the record selection is passed to the server, a somewhat better answer than using the group selection would be if you could alter the test in the record selection so that it's not passed to the server.  The group selection was just an easy way to see if testing the value in CR worked.  If it does, then if you can get CR to do the test in the record selection, rather than passing it to the server, then CR will hopefully include only the records you want.  The difference between the record selection and group selection is that with the group selection, CR is still including all of the other records in the report data.  It's just not showing the other groups.  With the record selection, CR would actually be filtering the other records out of the report data.

 The trick is figuring out how to modify the test in the record selection so that it's not passed to the server.  You could try adding some function that does not alter the result of the test and is not supported by the server.  For example (assuming that the field is 4 characters long):

Left ({ITEMASA.DPTNO}, 4) = Left ("CC", 4)

 If the server does not have a Left function, or equivalent, that test would presumably not be passed to the server.  If you want to try this idea, you could try making a change and then use "Show SQL Query" to see if the test was still being passed.


 Of course it would be more efficient if you could get the record selection on the server working, but if not, having CR do the record selection is the next best thing, followed by group selection.

 James

Hi James

Your suggestion -

Left ({ITEMASA.DPTNO}, 4) = Left ("CC", 4) -

for the Record Select works.

Thanks again

Sorry if I made a mountain out of a mole-hill here, folks.

gmollineau,

 Interesting.  Thanks for letting me know.


 agandau,

 I don't think you did.  There certainly seemed to be something odd going on.  It just (eventually) occurred to me that if something was not being interpreted the "correct" way on the server, maybe it would be interpreted correctly within CR.  It'd still be better (more efficient) to do the comparison on the server, but at least this provides a workaround.

 James