RE: sql join on substrings, or derived fields, giving me grief -- MIDRANGE-L

See changes to WHERE clause:

with T1 as ( 
 select substr(joesd,70,30) as MessageText, 
        substr(joesd,46,6)  as MessageDate, 
        substr(joesd,52,6)  as MessageTime, 
        substr(joesd,58,10) as MessageId 
 From Zmfstuff 
 Where substr(joesd,70,30) like '7G MSGID MAP TO ID%' 
    Or substr(joesd,70,30) like '7P CRT UNDEL NOTICE OR DSN%' 
  ) 
Select Jodate, jojob, jouser, jonbr, jopgm, joobj, jolib, jombr,
       substr(joesd,70,30) as MessageText, 
       substr(joesd,46,6)  as MessageDate, 
       substr(joesd,52,6)  as MessageTime, 
       substr(joesd,58,10) as MessageId 
>From zmfstuff A, T1 
Where substr(a.joesd,70,30)=T1.MessageText 
  and substr(a.joesd,58,10)=T1.MessageId

See change to FROM clause.

with T1 as ( 
 select substr(joesd,70,30) as MessageText, 
        substr(joesd,46,6)  as MessageDate, 
        substr(joesd,52,6)  as MessageTime, 
        substr(joesd,58,10) as MessageId 
 From Zmfstuff 
 Where substr(joesd,70,30) like '7G MSGID MAP TO ID%' 
    Or substr(joesd,70,30) like '7P CRT UNDEL NOTICE OR DSN%' 
  ) 
Select Jodate, jojob, jouser, jonbr, jopgm, joobj, jolib, jombr,
       substr(joesd,70,30) as MessageText, 
       substr(joesd,46,6)  as MessageDate, 
       substr(joesd,52,6)  as MessageTime, 
       substr(joesd,58,10) as MessageId 
>From zmfstuff A join T1 on substr(a.joesd,46,6) = T1.MessageDate AND
substr(a.joesd,58,10) = T1.MessageID 

Elvis

-----Original Message-----
 Subject: sql join on substrings, or derived fields, giving me grief

Trying to debug email and I ran this command
DSPJRN JRN(QZMF) 
       OUTPUT(*OUTFILE) 
       OUTFILE(QTEMP/ZMFSTUFF)
       OUTMBR(MAR2) 
       ENTDTALEN(512) 

Now my goal is to find non delivery messages.  I've pretty much weeded it 
down to:
select substr(joesd,70,30) as MessageText, 
       substr(joesd,46,6)  as MessageDate, 
       substr(joesd,52,6)  as MessageTime, 
       substr(joesd,58,10) as MessageId 
>From Zmfstuff 
Where substr(joesd,70,30) like '7G MSGID MAP TO ID%' 
   Or substr(joesd,70,30) like '7P CRT UNDEL NOTICE OR DSN%'

Actually, you probably don't need the 'Or' since there seems to be a 
one-to-one relationship.

Now, I want to use this as a temporary table to join back to the main 
table to get more information.  Like, here is one row returned by the 
above:
MESSAGETEXT                     MESSAGEDATE  MESSAGETIME  MESSAGEID 
7P CRT UNDEL NOTICE OR DSN        050908       152201     0000005261
I want to see the related messages:
SPECIFIC  
DATA  
EMLPDF    QSPLJOB   870507QZMFSLOGEID 103BDDD0509081522010000005261 *EH 
SNDMAIL TO MSF 
EMLPDF    QSPLJOB   870507QZMFSLOGEID 103BDDD0509081522010000005261 *E1 O 
ourgal@xxxxxxxxxxxxxx 
EMLPDF    QSPLJOB   870507QZMFSLOGEID 103BDDD0509081522010000005261 *E2 R 
theirgal@xxxxxxxxxxxxxxxx 
EMLPDF    QSPLJOB   870507QZMFSLOGEID 103BDDD0509081522010000005261 *EX 
/TMP/EMHDR786.TXT 
EMLPDF    QSPLJOB   870507QZMFSLOGEID 103BDDD0509081522010000005261 *ET 
MSG SIZE 45456 
EMLPDF    QSPLJOB   870507QZMFACRT1ID 103BDDD0509081522010000005261        
 
QMSF      QMSF      856912QZMFSLOGPID 103BDDD0509081522010000005261 *P2 R 
SMTP MsgFwd theirgal@xxxxxxxxxxxxxxxx
QMSF      QMSF      856912QZMFXLOGCID 103BDDD0509081522010000005261 *C6 
FWD TO QTMSOUTQ 
QMSF      QMSF      856912QZMFXLOGCID 103BDDD0509081522010000005261 *C1 O 
ourgal@xxxxxxxxxxxxxx 
QMSF      QMSF      856912QZMFXLOGCID 103BDDD0509081522010000005261 *C2 R 
<theirgal@xxxxxxxxxxxxxxxx> 
QTSMTPCLTPQTCP      058806QZMFXLOG8ID 103BDDD0509081522010000005261 *8B 
QTMSOUTQ TO CLNT 
QMSF      QMSF      856912QZMFBIGE2ID 103BDDD0509081522010000005261 
000010000100000 
QTSMTPCLTPQTCP      058806QZMFXLOG8ID 103BDDD0509081522010000005261 *83 U 
<theirgal@xxxxxxxxxxxxxxxx> 
QTSMTPCLTPQTCP      058806QZMFXLOG8ID 103BDDD0509081522010000005261 *87 
CLNT(UNDELV) TO QTMSINQ 
QTSMTPBRSRQTCP      857538QZMFXLOG7ID 103BDDD0509081522010000005261 *7A 
QTMSINQ TO BRSR 
QTSMTPBRSRQTCP      857538QZMFXLOG7ID 103BDDD0509081522010000005261 *7P 
CRT UNDEL NOTICE OR DSN 
QTSMTPBRSRQTCP      857538QZMFXLOG7ID 103BDDD0509081522010000005261  7H 
BRSR TO MSF 
QTSMTPBRSRQTCP      857538QZMFXLOG7ID 103BDDD0509081522010000005261 *71 O 
@ 
QTSMTPBRSRQTCP      857538QZMFXLOG7ID 103BDDD0509081522010000005261 *72 R 
<ourgal@xxxxxxxxxxxxxx> 
QTSMTPBRSRQTCP      857538QZMFXLOG7ID 103BDDD0509081522010000005261 *7G 
MSGID MAP TO ID 103BDDD0509081522110000005263

But I can't seem to join using JOIN, or WHERE, on substrings.

with T1 as ( 
 select substr(joesd,70,30) as MessageText, 
        substr(joesd,46,6)  as MessageDate, 
        substr(joesd,52,6)  as MessageTime, 
        substr(joesd,58,10) as MessageId 
 From Zmfstuff 
 Where substr(joesd,70,30) like '7G MSGID MAP TO ID%' 
    Or substr(joesd,70,30) like '7P CRT UNDEL NOTICE OR DSN%' 
  ) 
Select Jodate, jojob, jouser, jonbr, jopgm, joobj, jolib, jombr,
       substr(joesd,70,30) as MessageText, 
       substr(joesd,46,6)  as MessageDate, 
       substr(joesd,52,6)  as MessageTime, 
       substr(joesd,58,10) as MessageId 
>From zmfstuff A, T1 
Where A.substr(joesd,70,30)=T1.substr(joesd,70,30) 
  and A.substr(joesd,58,10)=T1.substr(joesd,58,10) 
Qualified object name SUBSTR not valid.


with T1 as ( 
 select substr(joesd,70,30) as MessageText, 
        substr(joesd,46,6)  as MessageDate, 
        substr(joesd,52,6)  as MessageTime, 
        substr(joesd,58,10) as MessageId 
 From Zmfstuff 
 Where substr(joesd,70,30) like '7G MSGID MAP TO ID%' 
    Or substr(joesd,70,30) like '7P CRT UNDEL NOTICE OR DSN%' 
  ) 
Select Jodate, jojob, jouser, jonbr, jopgm, joobj, jolib, jombr,
       substr(joesd,70,30) as MessageText, 
       substr(joesd,46,6)  as MessageDate, 
       substr(joesd,52,6)  as MessageTime, 
       substr(joesd,58,10) as MessageId 
>From zmfstuff join T1 using (MessageDate, MessageID) 
Column MESSAGEDATE not in specified tables. 

Rob Berendt