Results 1 to 7 of 7
  1. #1
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1

    Question Unanswered: Help SQL Statement To Identify Certain Fields

    Hi I am extract data from one of our Sql servers with the code below

    Code:
     Select  SectDesc, PrGDesc, 'Product Description'=SetDescF, ProdSet, ProdSect, SectChar, Prdct.ProdGrp, OrigPr
    
    From  Prdct
    
    INNER JOIN qryPRDGroupDets on Prdct.ProdGrp=qryPRDGroupDets.PGCode
    
    where supersed =''
    And OrigPr Not Like '9%' And OrigPr Not Like '%MDM%'
    And LISTPR1>'0' And STANCOST>'0'
    which works fine, but what I need to do is reference the "OrigPr" field and mark it as "valid" or "Invalid", the "OrigPr" the field contains alpha numeric data e.g. A000, A001, A002 - ZZ99 and so on, amongst all of the potential different types of codes we have codes that end in treble Zero (0) e.g. A000 which are valid, but if they end in double 00 e.g. AA00 then this is invalid, the problem I have is I can’t just add

    Code:
    'Marker'= Case When Right(OrigPr, 2) = '00' Then 'Invalid' ELSE 'Valid' End
    For it will mark the A000 as invalid, is there a way of getting around this

    Thanks

    Mark

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHEN Right(OrigPr, 3) = '000' THEN 'Valid' ELSE 'Invalid' END
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Code:
    SELECT 
        SectDesc
    ,   PrGDesc
    ,   'Product Description'=SetDescF
    ,   ProdSet
    ,   ProdSect
    ,   SectChar
    ,   Prdct.ProdGrp
    ,   OrigPr
    ,   CASE WHEN OrigPr LIKE '%000' THEN 'Invalid' ELSE 'Valid' END Marker
    FROM
        Prdct
    INNER JOIN
        qryPRDGroupDets
    ON
        Prdct.ProdGrp=qryPRDGroupDets.PGCode
    WHERE
        supersed =''
    AND 
        OrigPr NOT LIKE '9%'
    AND
        OrigPr NOT LIKE '%MDM%'
    AND
        LISTPR1>'0'
    AND
        STANCOST>'0'
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Thanks Gvee

    but I may of confused you, here is a snap shot of hopefull what I'm trying to get accross

    SectDesc OrigPr Marker
    Bearings A001 Valid
    Bearings BC02 Valid
    Bearings S900 Valid
    Bearings S912 Valid
    Bearings Q960 Valid
    Bearings T800 Valid
    Bearings T000 Valid
    Bearings RR00 Invalid
    Bearings SR00 Invalid
    Bearings RS00 Invalid


    Basically if the OrigPr field starts with 2 Alpha characters and end with double 00 (Zero), then this is invalid, all other connotations are valid.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Apologies, misread the post.
    Code:
    SELECT 
        SectDesc
    ,   PrGDesc
    ,   'Product Description'=SetDescF
    ,   ProdSet
    ,   ProdSect
    ,   SectChar
    ,   Prdct.ProdGrp
    ,   OrigPr
    ,   CASE WHEN OrigPr LIKE '%000' THEN 'Valid' WHEN RIGHT(OrigPr, 2) = '00' THEN 'Invalid' ELSE 'Valid' END Marker
    FROM
        Prdct
    INNER JOIN
        qryPRDGroupDets
    ON
        Prdct.ProdGrp=qryPRDGroupDets.PGCode
    WHERE
        supersed =''
    AND 
        OrigPr NOT LIKE '9%'
    AND
        OrigPr NOT LIKE '%MDM%'
    AND
        LISTPR1>'0'
    AND
        STANCOST>'0'
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHEN OrigPr LIKE '[A-Z][A-Z]00' THEN 'Invalid' ELSE 'Valid' END
    George
    Home | Blog

  7. #7
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Thanks Weejas, I think I everyone including myself, your's was nearly there I tweaked it a little, gvee bang on the money,

    Thank you both for your valued input my Migraine is clearing

Posting Permissions

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