Re: 'substring' a LONG column

From: <larry.oltmanns_at_gmail.com>
Date: Wed, 13 Mar 2013 11:23:23 -0700 (PDT)
Message-ID: <737a1f7a-f36e-44b9-86ca-3b7fa8e21c15_at_googlegroups.com>

On Wednesday, September 19, 2001 3:17:31 PM UTC-5, Richelle Hutchinson wrote:

> Does anyone know how to 'substring' a LONG field?  We need to obtain the
> first 80 characters and the substring function won't work.  Changing this
> column to a LOB datatype on the database is not an option at this time.
> 
> Thanks in advance.
> 
> -Richelle Hutchinson

You can to a to_char(Substr(PRJ.DESCRLONG,1,254)

SELECT DISTINCT PAR.SETID
, PAR.EMPLID
, PAR.FISCAL_YEAR
, substr(PAR.GBGM_PAY_PERIOD,5,2) GBGB_CAL_MONTH
, CASE WHEN substr(PAR.FUND_SOURCE,1,1) <> to_char(0) THEN PAR.FUND_SOURCE ELSE NULL END AS PROJECT_ID
, to_char(Substr(PRJDESCR.DESCRLONG,1,254))
, CASE WHEN substr(PAR.FUND_SOURCE,1,1) = TO_CHAR(0) THEN SUBSTR(PAR.FUND_SOURCE,2,5) ELSE NULL END AS FUND_DEPTID
, PAR.EARNED_AMT
, PAR.EARNINGS_CATEGORY
, PAR.GBGM_ERNG_PERIOD
, PAR.ACCOUNTING_PERIOD
, PAR.GBGM_PAY_PERIOD
, PAR.GBGM_ERNG_PERIOD
, PAR.DEPTID
, PAR.ACCOUNT2

  FROM PS_GBGM_CRT_PAR PAR, PS_GBFS_CRITVAL VAL, (SELECT BUSINESS_UNIT, PROJECT_ID, DESCR254, Substr(DESCRLONG,1,254) from PS_PROJECT_DESCR) DSC, PS_PROJECT_DESCR PRJDESCR     WHERE VAL.GBFS_CRITERIA_ID = 'EARNINGS_CODES'

     AND PAR.EARNINGS_CATEGORY = VAL.GBFS_CRITERIA_VAL
     AND VAL.GBFS_PROG_NAME = 'GBGM_TE_MRPT'
     AND GBFS_CRITERIA_SVAL = 'Y'
     AND PAR.FISCAL_YEAR = '2012'
     AND PAR.GBGM_PAY_PERIOD = PAR.GBGM_ERNG_PERIOD
     AND PAR.SETID = DSC.BUSINESS_UNIT
     AND PAR.FUND_SOURCE =  DSC.PROJECT_ID
     AND PAR.SETID = PRJDESCR.BUSINESS_UNIT
     AND PAR.FUND_SOURCE = PRJDESCR.PROJECT_ID

    ORDER BY PAR.FISCAL_YEAR, PAR.ACCOUNTING_PERIOD, PAR.EMPLID; Received on Wed Mar 13 2013 - 19:23:23 CET