Solved

Error when Using Select Expert to select an Alphanumeric field

Posted on 2009-04-13
31
375 Views
Last Modified: 2012-05-06
I am getting an errpr - Failed to retrieve data from database when I use the Select Expert to select specific records from an IBm iseries database.
If I use a numeric field with the Select Expert it works fine.
I am trying to select all records with CC in a field (DPTNO). The error returns : - Numeric Constant 0xCC not valid.
When I view the SQL, I am seeing the statement - WHERE "DPTNO" = 0xCC

The field size in the file is 4 Character. and the actual data is CC.

Please advise.
0
Comment
Question by:gmollineau
  • 12
  • 10
  • 7
  • +1
31 Comments
 
LVL 6

Expert Comment

by:agandau
ID: 24133106
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.
0
 

Author Comment

by:gmollineau
ID: 24133221
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.
 
0
 
LVL 6

Expert Comment

by:agandau
ID: 24133270
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?



0
 

Author Comment

by:gmollineau
ID: 24133326
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

Open in new window

0
 

Author Comment

by:gmollineau
ID: 24133346
Sorry I forgot to mention that the Field type is String [8]
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24133797
You need to have ' ' around the value

WHERE "DPTNO" = '0xCC'

mlmcc
0
 

Author Comment

by:gmollineau
ID: 24133951
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''

Open in new window

0
 

Author Comment

by:gmollineau
ID: 24134021
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

Open in new window

0
 
LVL 34

Expert Comment

by:James0628
ID: 24134576
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24134778
Put the whole thing in quotes

mlmcc
0
 
LVL 6

Expert Comment

by:agandau
ID: 24138461
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.



0
 

Author Comment

by:gmollineau
ID: 24151982
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


0
 
LVL 6

Expert Comment

by:agandau
ID: 24152488
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.

0
 

Author Comment

by:gmollineau
ID: 24153009
It is enabled. If it is off the data is represented in hex.
0
 
LVL 34

Expert Comment

by:James0628
ID: 24154098
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Expert Comment

by:agandau
ID: 24157652
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).
0
 
LVL 34

Expert Comment

by:James0628
ID: 24164362
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
0
 
LVL 6

Expert Comment

by:agandau
ID: 24168498
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)

Open in new window

crystal-hex-as-string.JPG
0
 
LVL 6

Expert Comment

by:agandau
ID: 24168841
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.
0
 

Author Comment

by:gmollineau
ID: 24170906
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.
0
 
LVL 6

Expert Comment

by:agandau
ID: 24171537
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.
0
 
LVL 34

Expert Comment

by:James0628
ID: 24178516
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
0
 
LVL 6

Expert Comment

by:agandau
ID: 24184940
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.
0
 

Author Comment

by:gmollineau
ID: 24185447
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
0
 

Author Comment

by:gmollineau
ID: 24185486
HI Agandau
I don't have any problems with numeric fields. It is only with the character (alphanumeric) fields.
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 24189999
It's interesting that you say in your original message that the field is 4 characters in the file, but CR says that it's 8 characters.  That sort of fits the hex vs character thing, in that if CR was somehow getting the hex representation for those 4 characters, that would be 8 digits.  But that doesn't actually seem to be what's happening, since the characters (eg.  CC) show up when you use that field on the report.

 Try this.  Go to Report > Selection Formulas > Group and enter the following:

{ITEMASA.DPTNO} = "CC"

 When I tried that, I only got that group.  If the problem is how the field and/or parameter values are being interpreted when used in a record selection that's passed to the server, this may be a workaround, since the group selection is, AFAIK, not passed to the server (ie. the test is done in CR).

 Note that group selection does not actually eliminate the other records from the report.  It just doesn't show them.  But if you have the group tree open, you'll see the other groups in it, and any groups within those groups.  If you click on one of those other groups you won't see it.  CR will just take you to the nearest page to where that group would be (in this case, the first or last page of the group that is showing).  Also, if you use any of the regular CR summary functions, they will include all of the other groups, not just the one that is visible.  Since you're doing your own summaries using variables, that doesn't seem to be a problem in this case.


 However, I did notice some errors in your totalling formulas.  You have formulas (eg. DEP_BEGCST and REP_BEGCST) that add to a shared variable.  That's fine, but then you use the same formulas to output the variable.  The problem with that is that since those formulas update the variables, the last value gets added to the variable again each time you use the formula.  In the case of your grand totals (eg. REP_BEGCST), you use the formulas in the ITNBR group footer to update the variable, but then use them again in the DPTNO footer, where they add the value from the last ITNBR group again, and then in the report footer to output the total, where they add the value from the last ITNBR group in the last DPTNO group again.

 You need to create separate formulas to output those summary variables (for both the DPTNO group totals and the grand totals).

 James
0
 

Author Closing Comment

by:gmollineau
ID: 31569665
Thank you James
0
 
LVL 34

Expert Comment

by:James0628
ID: 24230528
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
0
 

Author Comment

by:gmollineau
ID: 24242917
Hi James

Your suggestion -

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

for the Record Select works.

Thanks again
0
 
LVL 6

Expert Comment

by:agandau
ID: 24242988
Sorry if I made a mountain out of a mole-hill here, folks.
0
 
LVL 34

Expert Comment

by:James0628
ID: 24249215
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now