Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2012
    Posts
    3

    Unanswered: limit number of characters

    Hello,
    can you please help me to limit number of characters of this query?
    OTM restricts amount of fields to 2000 characters, so I need to condense following query.

    Can you please give me a tip how I can use the postal codes which are repeating?

    Thank you so much!

    select v1.order_release_gid
    from OTM_TM_NNL_OR_PLANNING v1, otm_planning_mv v2
    where v1.or_sloc = 'GTS.TM_NNL1_C_OFMR_ZZ'
    and v1.order_release_gid = v2.order_release_gid
    AND ((((trunc(v1.or_epd) <= trunc(current_date + v1.sl_ofs) AND
    (trunc(v1.or_lpd) >= trunc(current_date + v1.sl_ofs))) OR
    (trunc(v1.or_lpd) <= trunc(current_date + v1.sl_ofs)) OR
    (trunc(v1.or_ldd) <= trunc(current_date + v1.sl_ofs + 2))) AND
    ((((v2.country_code3_gid = 'GR' and
    substr(v2.postal_code, 0, 2) not in
    ('16',
    '06',
    '07',
    '13',
    '26',
    '30',
    '34',
    '83',
    '84',
    '09',
    '11',
    '12',
    '24',
    '31',
    '32',
    '33',
    '40',
    '46',
    '47',
    '48',
    '64',
    '65',
    '66',
    '81',
    '82')) OR
    (v1.dl_ctr = 'GR' andv2.country_code3_gid != 'JP')) OR
    v1.dl_ctrIN('CN', 'HK', 'JP') ORv1.dl_ctr = 'DK'))) OR
    (((trunc(v1.or_epd) <= trunc(current_date + v1.sl_ofs) AND
    (trunc(v1.or_lpd) >= trunc(current_date + v1.sl_ofs))) OR
    (trunc(v1.or_lpd) <= trunc(current_date + v1.sl_ofs)) OR
    (trunc(v1.or_ldd) <= trunc(current_date + v1.sl_ofs + 3))) AND
    v1.dl_ctr = 'CR' andv1.dl_pc4 = '6690' and exists
    (select 'o'
    from location_refnum lr
    where lr.location_gid = or_dloc
    andlr.location_refnum_qual_gid = 'OTM_OTOMAC_ID'
    andlr.location_refnum_valuein('787351', '97801'))) OR
    (((trunc(or_epd) <= trunc(current_date + sl_ofs) AND
    (trunc(or_lpd) >= trunc(current_date + sl_ofs))) OR
    (trunc(or_lpd) <= trunc(current_date + sl_ofs)) OR
    (trunc(or_ldd) <= trunc(current_date + sl_ofs + 3))) AND
    (dl_ctr = 'SP')) OR
    (((trunc(or_epd) <= trunc(current_date + sl_ofs) AND
    (trunc(or_lpd) >= trunc(current_date + sl_ofs))) OR
    (trunc(or_lpd) <= trunc(current_date + sl_ofs)) OR
    (trunc(or_ldd) <= trunc(current_date + sl_ofs + 7))) AND
    (dl_ctr = 'GC')) OR
    ((((trunc(v1.or_epd) <= trunc(current_date + v1.sl_ofs) AND
    (trunc(v1.or_lpd) >= trunc(current_date + v1.sl_ofs))) OR
    (trunc(v1.or_lpd) <= trunc(current_date + v1.sl_ofs)) OR
    (trunc(v1.or_ldd) <= trunc(current_date + v1.sl_ofs + 3))) AND
    (v2.country_code3_gid = 'GR' andsubstr(v2.postal_code, 0, 2) in
    ('16',
    '06',
    '07',
    '13',
    '26',
    '30',
    '34',
    '83',
    '84',
    '09',
    '11',
    '12',
    '24',
    '31',
    '32',
    '33',
    '40',
    '46',
    '47',
    '48',
    '64',
    '65',
    '66',
    '81',
    '82'))) or
    (v1.dl_ctr = 'GR' andv2.country_code3_gid != 'JP') OR
    v1.dl_ctrIN('CN', 'HK', 'JP') ORv1.dl_ctr = 'DK'))

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Do you mean the number of rows your query is returning?
    Because you are only selecting one column(v1.order_release_gid).
    What is the type/size of this column?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >OTM restricts amount of fields to 2000 characters

    post URL that documents OTM
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Oct 2012
    Posts
    3
    Thank you for your replies!
    I`ll try to be more specific: OTM 5.5 and 6.2 restricts number of characters allowed in the field Saved_query to only 2000 characters. When I remove all the spaces I`m still on the edge of 2000 characters in this field, but OTM will still not accept this query because of lenght of characters.
    What I`m trying to do is to trim this code to fit under this limitation. The best solution for me would be to use ('16','06','07','13','26','30','34','83','84','09' ,'11','12','24','31','32','33','40','46','47','48' ,'64','65','66','81','82') for both conditions in the query(which is repeating) so it will eliminate number of characters of the query to fit to OTM requirements. This is where I need little bit of your skills to help me. Thank you!

  5. #5
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    Hi.
    Do the fields v1.or_epd and v1.or_lpd are dates ? Do they have to be truncated, or are they already set to midnight ?

    to compare two days, when they have a time part:

    Code:
    trunc(dt_1)<=trunc(dt_2) is equivalent:
    to dt_1 < trunc(dt_2+1)
    
    trunc(dt_1)>=trunc(dt_2) is equivalent to:
    dt_1>=trunc(dt_2)

    To reduce more the query length, you can use a subselect with a case statement as well.
    Replace:
    Code:
    select a,b,d from ...
    where  ....
      AND ( COND1  AND COND2 )
            OR (  (NOT COND1) AND COND3 )
    by
    Code:
    select a,b,d from 
       (select a,b,d, case when cond1 then 1 else 0 end TEST1 from ... where ... )
    where  ( TEST1=1  AND COND2 )
    OR (  (TEST1=0) AND COND3 )
    Regards,
    Eric.

  6. #6
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    For your list of
    Code:
    substr(v2.postal_code, 0, 2) not in
    ('16','06','07','13','26','30','34','83','84','09','11','12','24','31','32','33','40','46','47','48','64','65','66','81','82'))
    Perhaps you should have another table or another column in an existing table.

    Regards,
    Eric

  7. #7
    Join Date
    Oct 2012
    Posts
    3
    Hello Eric,
    Thanks a lot for your input. I`ll try your suggestions and hopefully I can solve this issue!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •