Order by and datatype conversion

DATABASE

Order by and datatype conversion

Postby bruce.crisp » Wed, 17 Dec 2003 05:00:14 GMT

   (Ingres II 2.6 with patch 9653 on Tru64 Unix 5.1)

Hello everyone,

We have noticed that since we upgraded to Ingres 2.6,
datatype conversion is not working if we have an order by
clause in a query.

Is this a known bug?  Have others encountered it?

Examples:

  1> select * from shlf_list
     where org_nbr=1526
     and shlf_list_typ_cde=2
     and shlf_list_srt_form >= 'A'
     order by shlf_list_srt_form

E_US1130 illegal character to integer conversion or non-numeric character
    in money field.
    (Mon Dec 15 14:29:51 2003)
End of Request - Terminated by Errors

If we put the value of shlf_list_typ_cde (and it is type char) = '2'
the query runs correctly.  If there is no order by clause, the 2 does
not need to be in quotes and the datatype conversion kicks in as it should.

  1> select * from shlf_list
     where org_nbr=1526
     and shlf_list_typ_cde=2
     and shlf_list_srt_form >= 'A'

----------------------------------------------------------------------
org_nbr      shlf_list_keyxshlf_l   shlf_list_strng_text
----------------------------------------------------------------------
1526          3334493   2             ABC123Test 1995
1526          3395535   2             ABC123Test 1996
[ etc. etc.]

Thanks for your feedback on this.

Bruce Crisp

Library and Archives Canada


Similar Threads:

1.scripted import step - Custom Order vs. Last Order

OK, I have another (probably dumb) question.  I've continually run
into difficulties with scripted Import Record commands.  Here's what
happens:

I open the "Specify Import Order" window.  I set up a custom mapping
of fields, importing from one table into another.  Everything works
perfectly.  Then I change the database structure, delete a field, add
a field (nothing to do with the fields to be imported).  And now the
import script no longer correlates the fields correctly.  Argh.  It
seems the import order is strictly hierarchical, like "Field 1 on the
left goes to Field 10 on the right", rather than tying a specific
field name on the left to a specific field name on the right.  Can
that possibly be true??  It seems far too dysfunctional.  And if it is
true, how do people avoid having imports that aren't consistent?

I've tried changing the "Arrange by..." button, from Last Order to
Custom Order, but of course that only seems to affect the display in
the set-up window, not the resulting permanence of the field import
mapping.

Help?

thanks,
Lanse Stover
Mac, OS-X, FMP 9 Advanced

2.[Info-Ingres] To order by, or NOT to order by

I realize the 'order by' question has been discussed to death and I'm
likely just opening a can of worms, but please bare with me.
 
I have a case that I can't explain.
 
I have a 35 million row table with some varchar columns.
These varchar columns have  secondary indexes.
 
If I do a wildcard range search on one of the varchars, i.e. >='aa%'
with an order by on the varchar, the optimizer decides to 
ignore the secondary indexes because I'm calling back most 
of the rows (according to the stats). The QEP ends up doing a scan, 
then sorts the result set. Takes a very long time. Fair enough.
 
But, if i drop or ignore the stats, the QEP will use the secondary and return
results almost instantly. The order by obviously didn't sort anything. Does
this mean the optimizer thought the 'order by' was redundant and returned 
the rows according to the order in the secondary index ? 
 
I know were not supposed to rely on the row order in any tables or indexes.
 
Andre

3.Ordering YYYY MM DD in reverse chrono order

Hello,

I am trying to select distinct dates and order them in the reverse
chronological order.  Although the column type is TIMESTAMP, in this
case I want only YYYY, MM, and DD back.

I am using the following query, but it's not returning dates back in
the reverse chronological order:

SELECT DISTINCT
  date_part('year', uu.add_date),  date_part('month', uu.add_date), 
  date_part('day', uu.add_date)

FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1

ORDER BY
date_part('year', uu.add_date), date_part('month', uu.add_date), 
date_part('day',  uu.add_date) DESC;


This is what the above query returns:

 date_part | date_part | date_part
-----------+-----------+-----------
      2004 |         2 |         6
      2004 |         4 |        20
(2 rows)


I am trying to get back something like this:
2004 4 20
2004 4 19
2004 2 6
...

My query is obviously wrong, but I can't see the mistake.  I was
wondering if anyone else can see it.  Just changing DESC to ASC, did
not work.

Thank you!

4.Datatypes in Informix

ovkrishna wrote:

> I am trying to obtain an extract of my Informix database into a
> formatted spreadsheet.
> 
> The information of the fields in a table is maintained in SYSCOLUMNS
> table. But the datatype of each column is given by COLTYPE field which
> is of SMALLINT Type. Which table holds the reference of this datatype
> name against the number?
> 
> There is one table SYSXTDTYPES. This contains the extended datatypes.
> But I could not locate the table for the basic datatypes.

If you look in the software section of the IIUG.org website, you should find 
some code that will show you what to do.

-- 
Ciao,
The Obnoxious One

"Ogni uomo mi guarda come se fossi una testa di cazzo"

5.max no of column with datatype LONG

Dear members ,
       Can anybody tell me how many columns can be created in a table ?
  and also how many columns can be created with data type LONG 
 
 
Regards,
 
 
 


		
---------------------------------
Yahoo! for Good
 Click here to donate to the Hurricane Katrina relief effort. 
sending to informix-list

6. Informix crashes with wrong datatype in stored procedure

7. Using CHAR /LVARCHAR datatype in Informix

8. Invalid scale for DECIMAL datatype



Return to DATABASE

 

Who is online

Users browsing this forum: No registered users and 77 guest