Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!utgpu!water!watmath!clyde!rutgers!mit-eddie!think!ames!cit-vax!oberon!sdcrdcf!trwrb!ries
From: ries@trwrb.UUCP
Newsgroups: comp.databases
Subject: (Re)sorting blanks in SQL
Message-ID: <1977@trwrb.UUCP>
Date: Wed, 17-Jun-87 11:45:03 EDT
Article-I.D.: trwrb.1977
Posted: Wed Jun 17 11:45:03 1987
Date-Received: Sat, 20-Jun-87 00:43:19 EDT
Reply-To: ries@trwrb.UUCP (Marc Ries)
Distribution: na
Organization: TRW Operations and Support Group
Lines: 55


  NOTE: I  posted this query to CA but got no responses,  and  so
        am  trying  for  a  wider area.  Thanks and sorry for any
        inconvenience.

  [While this is being used with Informix's SQL-ACE report writer,
  the problem may be somewhat generic]

  I have a group  of  users  who  are  used  to  seeing  (FORTRAN
  created)  reports  with  all of the (sorted) items with "blank"
  attributes at the end of the report.

  ACE (I assume SQL, too?)  offers  two  options:  ASCending  and
  DECending,  with ASC the default and the "blanks" at the top of
  the report.

  Descending is no good because while I have the  blanks  at  the
  end of the report, the user sees "z-A" in the report main.

  The suggestion on how this might be accomplished was  something
  along the lines of:

     Create  a  variable  the  same  length  as  the  "field"  in
     question.

     Make a first pass of the database and extract all  of  those
     rows with  "blank/null"  data  (and  required  columns)  and
     assign   some  (intelligent)  value  like  "zzzzzz"  to  the
     variable "field".

     Make a second pass  and  extract  the  non-blank  rows  (and
     matching  columns)  and  assign the non-blank "field" to the
     variable "field".

     Combine the two temporary passes and sort  on  the  variable
     "field" and print the results.

  So far, within the limitations of SQL-ACE  (like  you  can  not
  sort  "ORDER BY" a user defined variable), I have not been able
  to put thoughts into  compilable  actions.  Has  anyone  had  a
  similar  experience  and/or can shed some light on the subject?
  
  PS:  The INFORMIX-SQL User/Reference Guides run out of advanced
  examples  real  fast.  Does anybody know of an advanced text or
  manual (IBM?) that might be of use?

  Thanks in advance,

  Marc Ries ries@trwrb

-- 
		Marc A. Ries

		sdcrdcf!---\ 
                ihnp4!------\----- trwrb! --- ries