Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116

    Unanswered: Help with SUBSTR syntax

    I need help with my statement using the SUBSTR. ( And this is not the MS SQL - but some flavor like it that we use on our AS400)

    Code:
     
       SELECT   DISTINCT codstrctn, coacctno, cosrvlcn, losrvlctyp, losrvlcsts,
                  losrvlcnm, losrvlcnml, losrvlc, losrvlcn1, losrvlctyn,
                  losrvlcstc, losrvlczpc, cobstypcd, cosyscode, coctqty, cocntsz,
                  covolcd, cosrvschcd, copdycd001, copdycd002, copdycd003,
                  copdycd004, copdycd005, copdycd006, copdycd007, coonclflg,
                  cocmprsnfl, rachgamt, rahlrt, cbblnmln, cbblnmln1, cbblstn,
                  cbblstnm, cbblctynm, cbblstcd, cbblzpcd, racurr
       FROM     siqsprod/sipcontr, siqsprod/siplocat, siqsprod/sipcbill,
                  siqsprod/siprate
      JOIN     codstrctn.01=lodstrctn.02 AND coacctno.01=loacctno.02 AND
                 cosrvlcn.01=losrvlcn.02 AND codstrctn.01=cbdstrctn.03 AND
                 coacctno.01=cbacctno.03 AND codstrctn.01=radstrctn.04 AND
                 coacctno.01=raacctno.04 AND cosrvlcn.01=rasrvlcn.04
      WHERE    losrvlcnm CONTAINS 'SCHLOTSKY' AND racurr='C' AND
                 SUBSTR(losrvlcstc,2,2) IN('GA','TX') OR
                 losrvlcnml CONTAINS 'SCHLOTSKY' AND racurr='C' AND
                 SUBSTR(losrvlcstc,2,2) IN('GA','TX') OR
                 cbblnmln CONTAINS 'SCHLOTSKY' AND racurr='C' AND
                  SUBSTR(losrvlcstc,2,2) IN('GA','TX') OR
                  cbblnmln1 CONTAINS 'SCHLOTSKY' AND racurr='C' AND
                  SUBSTR(losrvlcstc,2,2) IN('GA','TX')
       GROUP BY
       HAVING
    UNION
    ORDER BY
    I'm sure I haven't told you enough. I'm trying to write a query that will allow me to put in a number of different states for each search, so that I don't have to write additional lines for every state. So I'm taking a stab at doing this and it didn't return any data. Yet if I take out the statement about the SUBSTR it returns data. So can any one please set me in the right direction.

    Thanks . . .
    texasalynn
    texasalynn
    It's AWL Good!

  2. #2
    Join Date
    Feb 2004
    Posts
    78
    I think you are using DB2 if it is an AS400. Check out that forum.

    Anyways, can you give us an example of what the column losrvlcstc contains?

    I believe substring(x,2,2) would return 'CD' from 'ABCD'

    Why don't you run sample select substring(...) from .. to see what the values are ?

    Stick ()'s in the AND / OR's to keep things nice and clean.


    Hope this helps,
    Eric

  3. #3
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    LOSRVLCSTC Char 2 Srv Loc State Code

    This is what is shows. I will always use the full field data in my request. So maybe I'm using the wrong thing? As you can tell I'm very much a newbie
    texasalynn
    It's AWL Good!

  4. #4
    Join Date
    Feb 2004
    Posts
    78
    Yeah, you don't even need substring since the field is 2 characters to begin with,

    Try this:
    Code:
    SELECT   DISTINCT codstrctn, coacctno, cosrvlcn, losrvlctyp, losrvlcsts,
                  losrvlcnm, losrvlcnml, losrvlc, losrvlcn1, losrvlctyn,
                  losrvlcstc, losrvlczpc, cobstypcd, cosyscode, coctqty, cocntsz,
                  covolcd, cosrvschcd, copdycd001, copdycd002, copdycd003,
                  copdycd004, copdycd005, copdycd006, copdycd007, coonclflg,
                  cocmprsnfl, rachgamt, rahlrt, cbblnmln, cbblnmln1, cbblstn,
                  cbblstnm, cbblctynm, cbblstcd, cbblzpcd, racurr
       FROM     siqsprod/sipcontr, siqsprod/siplocat, siqsprod/sipcbill,
                  siqsprod/siprate
      JOIN     codstrctn.01=lodstrctn.02 AND coacctno.01=loacctno.02 AND
                 cosrvlcn.01=losrvlcn.02 AND codstrctn.01=cbdstrctn.03 AND
                 coacctno.01=cbacctno.03 AND codstrctn.01=radstrctn.04 AND
                 coacctno.01=raacctno.04 AND cosrvlcn.01=rasrvlcn.04
      WHERE    racurr='C' AND losrvlcsts IN('GA','TX') AND
                 (losrvlcnm CONTAINS 'SCHLOTSKY' OR
                 losrvlcnml CONTAINS 'SCHLOTSKY' OR
                 cbblnmln CONTAINS 'SCHLOTSKY' OR
                 cbblnmln1 CONTAINS 'SCHLOTSKY' )
       GROUP BY
       HAVING
    UNION
    ORDER BY

  5. #5
    Join Date
    Feb 2004
    Posts
    78
    Yeah, you don't even need substring since the field is 2 characters to begin with,

    Try this:
    Code:
    SELECT   DISTINCT codstrctn, coacctno, cosrvlcn, losrvlctyp, losrvlcsts,
                  losrvlcnm, losrvlcnml, losrvlc, losrvlcn1, losrvlctyn,
                  losrvlcstc, losrvlczpc, cobstypcd, cosyscode, coctqty, cocntsz,
                  covolcd, cosrvschcd, copdycd001, copdycd002, copdycd003,
                  copdycd004, copdycd005, copdycd006, copdycd007, coonclflg,
                  cocmprsnfl, rachgamt, rahlrt, cbblnmln, cbblnmln1, cbblstn,
                  cbblstnm, cbblctynm, cbblstcd, cbblzpcd, racurr
       FROM     siqsprod/sipcontr, siqsprod/siplocat, siqsprod/sipcbill,
                  siqsprod/siprate
      JOIN     codstrctn.01=lodstrctn.02 AND coacctno.01=loacctno.02 AND
                 cosrvlcn.01=losrvlcn.02 AND codstrctn.01=cbdstrctn.03 AND
                 coacctno.01=cbacctno.03 AND codstrctn.01=radstrctn.04 AND
                 coacctno.01=raacctno.04 AND cosrvlcn.01=rasrvlcn.04
      WHERE    racurr='C' AND losrvlcstc IN('GA','TX') AND
                 (losrvlcnm CONTAINS 'SCHLOTSKY' OR
                 losrvlcnml CONTAINS 'SCHLOTSKY' OR
                 cbblnmln CONTAINS 'SCHLOTSKY' OR
                 cbblnmln1 CONTAINS 'SCHLOTSKY' )
       GROUP BY
       HAVING
    UNION
    ORDER BY

  6. #6
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    Thanks that is great. But I'm confused because I thought I had to repeat those parameters on each different section (losrvlcnml, cbblnmln, and cbblnmln1). Won't what you show only pull those states on that first choice?
    texasalynn
    It's AWL Good!

  7. #7
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    oh-oh-oh I see it!!!!! Thanks so much I get it now.

    You're the bomb Dilyias!
    texasalynn
    It's AWL Good!

  8. #8
    Join Date
    Feb 2004
    Posts
    78
    Glad I could help! =)

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Quote Originally Posted by Dilyias
    I think you are using DB2 if it is an AS400. Check out that forum.

    Anyways, can you give us an example of what the column losrvlcstc contains?

    I believe substring(x,2,2) would return 'CD' from 'ABCD'

    Why don't you run sample select substring(...) from .. to see what the values are ?

    Stick ()'s in the AND / OR's to keep things nice and clean.


    Hope this helps,
    Eric
    eric
    select substr('ABCD',2,2) returns BC

  10. #10
    Join Date
    Feb 2004
    Posts
    78
    Yeah, oops.. typo!

Posting Permissions

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