× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. November 2001

RE: Custom sort in a logical???

Rob,

OUTSTANDING question...!  I am EXTREMELY interested in the answer.  My
wild-hair guess is that it's a limitation of ANSI.

There are multiple approaches to "fixing" that problem, if that's what it
is.  The iNation being one possible "fix".

jt


"Have a GREAT day...!  And a BETTER ONE TOMORROW~~~:-)" (sm)


> -----Original Message-----
> From: midrange-l-admin@midrange.com
> [mailto:midrange-l-admin@midrange.com]On Behalf Of rob@dekko.com
> Sent: Monday, November 12, 2001 9:24 AM
> To: midrange-l@midrange.com
> Subject: Re: Custom sort in a logical???
>
>
>
> Right on old bean!  I always thought it silly that you can't combine the
> properties of a view with an index within SQL.  Is that a DB2 limitation,
> an iSeries limitation, or an ANSI limitation?  And how does one go about
> changing the standards?
>
> Rob Berendt
>
> ==================
> "They that can give up essential liberty to obtain a little temporary
> safety deserve neither liberty nor safety."
> Benjamin Franklin
>
>
>
>                     "Alexei Pytel"
>                     <pytel@us.ibm.com>        To:
> midrange-l@midrange.com
>                     Sent by:                  cc:
>                     midrange-l-admin@mi       Fax to:
>                     drange.com                Subject:     Re:
> Custom sort in a logical???
>
>
>                     11/09/2001 03:40 PM
>                     Please respond to
>                     midrange-l
>
>
>
>
>
>
>
> Yes, but you cannot create SQL view with ordering and you cannot
> create SQL
> index on a derived field. So CASE approach may require SQL to build
> temporary index for every query.
>
>     Alexei Pytel
>
>
>
>
>
>                     rob@dekko.com
>                     Sent by:                  To:
> midrange-l@midrange.com
>                     midrange-l-admin@mi       cc:
>                     drange.com                Subject:     Re: Custom sort
> in a logical???
>
>
>                     11/09/2001 01:33 PM
>                     Please respond to
>                     midrange-l
>
>
>
>
>
>
> Wouldn't the CASE clause from SQL do the same thing?
>
> Rob Berendt
>
> ==================
> "They that can give up essential liberty to obtain a little temporary
> safety deserve neither liberty nor safety."
> Benjamin Franklin
>
>
>
>                     "Alexei Pytel"
>                     <pytel@us.ibm.com>        To:
> midrange-l@midrange.com
>                     Sent by:                  cc:
>                     midrange-l-admin@mi       Fax to:
>                     drange.com                Subject:     Re: Custom sort
> in a logical???
>
>
>                     11/09/2001 02:24 PM
>                     Please respond to
>                     midrange-l
>
>
>
>
>
>
>
> I think you are talking about sort sequence table, not a
> translation table.
> It is a different type of table. It is created via CRTTBL command with
> TBLTYPE(*STRSEQ).
> It is not used to convert one character to another, but to specify
> precedence between characters.
>
> Sort sequence table operates on character level, not on full value of key
> field.
> If you need to set sorting rules on a full key level, you can create a
> dictionary file, which will assign priorities to key values, join to this
> dictionary and sort on priority field from a dictionary file.
>
>     Alexei Pytel
>
>
>
>
>
>                     "Dennis Lovelady"
>                     <dlovelady@dtcc.com       To:
> midrange-l@midrange.com
>                     >                         cc:
>                     Sent by:                  Subject:     Re: Custom sort
> in a logical???
>                     midrange-l-admin@mi
>                     drange.com
>
>
>                     11/09/2001 01:11 PM
>                     Please respond to
>                     midrange-l
>
>
>
>
>
>
> Hi, Rob:
>
> I don't understand why you say a translate table won't work.  Of course, I
> don't know enough about the data...  but if Group is a single-byte field,
> xlate table is the way to go, seems to me.
>
> Dennis
>
>
>
>
>
>
> rob@dekko.com@midrange.com on 11/09/2001 01:59:48 PM
>
> Please respond to midrange-l@midrange.com
>
> Sent by:  midrange-l-admin@midrange.com
>
>
> To:   midrange-l@midrange.com
> cc:
> Subject:  Re: Custom sort in a logical???
>
>
>
> I don't think a translation table will work.  Unless your
> translation table
> converts Group 7 into Group 1 and Group 9 into Group 6, and
> leaves the rest
> of the groups alone.
>
> I haven't found a solution but I tried the following:
> SELECT iclas FROM iim GROUP BY iclas
> and got:
> Itm
> Cls
> 01
> 02
> 05
> 10
> ...
>
> So then I tried
> SELECT
>  case iclas
>  when '01' then '02'
>  else iclas
>  end
> from iim
> group by iclas
> and got
> CASE expression
>       02
>       02
>       05
>       10
> ...
> Which looks good except there are two '02'.  So then I tried
> SELECT
>  case iclas
>  when '01' then '02'
>  else iclas
>  end
> from iim
> group by 1
> and got:
> Column ICLAS or expression specified in SELECT list not valid.
>
> But if you don't need the GROUP but a simple ORDER will work try:
> SELECT
>  case iclas
>  when '01' then '02'
>  else iclas
>  end
> from iim
> order by 1
>
> To create the view, or logical file:
> CREATE VIEW ROB/deleteme
> (ICLAS, IPROD) AS
>   SELECT
>    case iclas
>    when '01' then '02'
>    else iclas
>   END,
>   iprod
> from iim
>
> Then to test this new view:
> select iclas
>  from rob/deleteme
>  group by iclas
> will give you:
> ICLAS
>  02
>  05
>  10
> ...
>
>
> Rob Berendt
>
> ==================
> "They that can give up essential liberty to obtain a little temporary
> safety deserve neither liberty nor safety."
> Benjamin Franklin
>
>
>
>                     "Wills, Mike N.
>                     (TC)"                     To:
> "'midrange-l@midrange.com'" <midrange-l@midrange.com>
>                     <MNWills@taylorcorp       cc:
>                     .com>                     Fax to:
>                     Sent by:                  Subject:     Custom
> sort in a
> logical???
>                     midrange-l-admin@mi
>                     drange.com
>
>
>                     11/09/2001 12:39 PM
>                     Please respond to
>                     midrange-l
>
>
>
>
>
>
> Is it possible to do a custom sort order in a logical, if so how is this
> done? I need to group some things together differently than how it was set
> up. For example: I want Group 1 and 7 together and Group 6 and 9 together
> for easy processing. The rest then get sorted as normal after that.
>
> TIA,
>
> Mike
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.