0

I have a bit of a long query which I'll dump all of it because I have no clue as to which particular part is causing the problem.

SELECT DISTINCT
  vip.srname, vip.frname, vip.indv_id AS empid, prf.usrpf_formal_lastname, prf.usrpf_formal_givnames,
  job.job_desc as job_name, dep.dept_desc AS dept, p.rtype, p.rdesc, p.rcode,
  prf.usrpf_accept_grad as accept_grad, prf.usrpf_open_tt as open_tt
FROM acad_active vip
  LEFT JOIN map_dept dep ON dep.dept_src = vip.work_unit_name
  LEFT JOIN map_job job ON job.job_code = vip.job_no
  LEFT JOIN usr_preference prf ON prf.usrpf_emplno = vip.indv_id
  INNER JOIN (
    SELECT k.pikw_emplno AS empl_no, (
      CASE
        WHEN UPPER(kw_desc) LIKE UPPER('%' || :keyword || '%') THEN 'directkw'
        WHEN UPPER(akw_keyword) = UPPER(:keyword) THEN 'assockw'
        ELSE 'relkw'
      END
    ) AS rtype, w.kw_desc AS rdesc, w.kw_code AS rcode
    FROM clb_pikeyword k
      INNER JOIN iera_clb_keyword w ON w.kw_code = k.pikw_kwcode
      LEFT JOIN assoc_keyword a ON a.akw_formal_kwcode = k.pikw_kwcode AND k.pikw_external = 'Y'
      INNER JOIN (
        SELECT pikw_emplno
        FROM clb_pikeyword a1
          INNER JOIN assoc_keyword a2 ON a2.akw_formal_kwcode = a1.pikw_kwcode
        WHERE pikw_external = 'Y'
          AND UPPER(akw_keyword) = UPPER(:keyword)
        UNION
        SELECT pikw_emplno
        FROM clb_pikeyword d1
          INNER JOIN iera_clb_keyword d2 ON d2.kw_code = d1.pikw_kwcode
        WHERE pikw_external = 'Y'
          AND UPPER(kw_desc) LIKE UPPER('%' || :keyword || '%')
      ) e ON e.pikw_emplno = k.pikw_emplno
    WHERE k.pikw_external = 'Y'
    UNION
    SELECT t.txt_emplno AS empl_no, 'Text' as rtype, t.txt_text AS rdesc, t.txt_type AS rcode
    FROM (
      SELECT *
      FROM clb_txt txt
      WHERE txt.txt_type IN ('research', 'teaching', 'publicmedia')
    ) t
    WHERE t.txt_public = 'Y'
      AND (UPPER(t.txt_text) LIKE UPPER('%' || :keyword || '%'))
    UNION
    SELECT l.lang_emplno AS empl_no, 'Language' AS rtype, (
      CASE
        WHEN l.lang_country IS NOT NULL THEN l.lang_name || '(' || l.lang_country || ')'
        ELSE l.lang_name
      END
    ) AS rdesc, TO_CHAR(l.ra_pi_lang_id) AS rcode
    FROM pi_lang l
    WHERE l.lang_public = 'Y'
      AND (UPPER(l.lang_name) LIKE UPPER('%' || :keyword || '%'))
    UNION
    SELECT i.intl_emplno AS empl_no, 'Intl' AS rtype, (
      CASE
        WHEN i.intl_title IS NOT NULL AND i.intl_desc IS NOT NULL THEN i.intl_title || '& ' || i.intl_desc
        WHEN i.intl_title IS NOT NULL THEN i.intl_title
        ELSE i.intl_desc
      END
    ) AS rdesc, TO_CHAR(i.intl_sort_order) AS rcode
    FROM pi_intl i
    WHERE i.intl_public = 'Y'
      AND (UPPER(i.intl_desc) LIKE UPPER('%' || :keyword || '%') OR UPPER(i.intl_title) LIKE UPPER('%' || :keyword || '%'))
  ) p ON p.empl_no = vip.indv_id
WHERE vip.researcher = 'Y'
ORDER BY vip.srname, vip.frname, dep.dept_desc, job.job_desc, p.rtype, UPPER(p.rdesc)

So with that query, if I put any of the following:

  • w or x or some other 1-letter word (some 1-letter word works)
  • tw or as or some other 2-letter word (some 2-letter word works)

for the value of the :keyword parameter I get the error ORA-01489: result of string concatenation is too long. Which is somewhat misleading because when I put twitter or ask as the value for the parameter it works. Am I missing something?

So far I haven't seen the query fail on 3 or more letter words but I keep having issues with 1 or 2 letter words.

wogsland
  • 9,106
  • 19
  • 57
  • 93
dokgu
  • 4,957
  • 3
  • 39
  • 77
  • 1
    Well, the shorter the keyword the more matches, I guess. However, I see no aggregation or recursion, so I have no idea where you might build up a too long string. How long are the longest `lang_name`, `lang_country`, `intl_title`, and `intl_desc`? – Thorsten Kettner Feb 02 '17 at 16:18
  • http://stackoverflow.com/help/mcve – Gurwinder Singh Feb 02 '17 at 16:19
  • @ThorstenKettner That's why I'm confused as well. Those shouldn't take very long - usually something like `Portuguese, Swedish, Spanish(Brazil)`. – dokgu Feb 02 '17 at 16:22
  • 1
    Well, just select `max(length(lang_name))` and so on from the tables, just to make sure there doesn't exist some super-long value for whatever reasons. – Thorsten Kettner Feb 02 '17 at 16:26
  • 1
    I was thinking the same as Thorsten. You may have one very long `lang_name` or perhaps `lang_country` or whatever else you are concatenating, and the value may be something like 4000 letters 'x' in a row - which is why it gets caught when you input 'x' but not when you input a three-letter key. This would also explain why the query works on some one-letter keys but not on others. In any case, checking `max(length(lang_name))` and same for `lang_country` and whatever else you are concatenating should take very little time, so it's the first thing to try. –  Feb 02 '17 at 16:33
  • @mathguy and @ThorstenKettner I tried using `max` and `length` from within the `case` statement `WHEN l.lang_country IS NOT NULL THEN MAX(LENGTH(l.lang_name)) || '(' || MAX(LENGTH(l.lang_country)) || ')'` and I got an error about not a single group by stuff. – dokgu Feb 02 '17 at 16:45
  • Which line should I do the `MAX` and `LENGTH` solution on? – dokgu Feb 02 '17 at 16:46
  • 1
    That's not a solution to fix your current query - rather something else you should run directly against your data. Something like `select max(length(lang_name)) from pi_lang` (assuming those are the correct table and column names). Or even `select lang_name from pi_lang where length(lang_name) > 1000` - that should show all the language names more than 1000 characters long (and therefore almost surely garbage). –  Feb 02 '17 at 16:52
  • @mathguy The languages aren't the problem. But the `intl_desc` is at the moment having 3854 for the longest one. While the `intl_title` is at 183. I'm going to cut those strings to a length then. Thanks for pointing out where the possible problem was! If you want to put that as an answer I will accept it. – dokgu Feb 02 '17 at 16:58
  • 1
    So then, whenever you concatenate `intl_desc`, you should instead concatenate something like `substr(intl_desc, 1, 3000)`. This will return the first 3000 characters - or the entire description for most of them, if most of them are no more than 3000 characters. –  Feb 02 '17 at 17:00

0 Answers0