Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2011
    Posts
    5

    Unanswered: Filtering a field in a Table ?

    SELECT
    PMEQMT.EPLANT_ID

    Inside the table PMEQMT.EPLANT_ID we have two EPLANTS_ID's 1 and 2

    All I need to do is to choose a certain Eplant_id 1 or 2
    I tried SELECT PMEQMT.EPLANT_ID = '2' but my SQL checker gave me an error that didn’t help me. I am sure this is basic; I am new to SQL kinda sorta... Thx for any and help!!

    Mathew
    The Autism MX Project

    the complete code

    SELECT
    SUBSTR("PMEQMT"."EQNO",1,10) EQNO,
    "PMEQMT"."CLASS",
    ROUND("PMJOB"."TOTAL_UNITS",0) ACTUAL,
    SUBSTR("PMJOB"."UOM",1,8) UOM,
    SUBSTR("PMJOB"."ACT_EVERY",1,10) PLANNED,
    SUBSTR("PMTASKS"."DESCRIP",1,60) TASK_DESC,
    ROUND((("PMJOB"."TOTAL_UNITS"/"PMJOB"."ACT_EVERY")*100),0) PERCENTAGE,
    PMEQMT.EPLANT_ID

    FROM
    "IQMS"."PMEQMT" "PMEQMT",
    "IQMS"."PMJOB" "PMJOB",
    "IQMS"."PMTASKS" "PMTASKS"

    WHERE
    "PMEQMT"."ID" = "PMJOB"."PMEQMT_ID"(+) AND
    "PMJOB"."PMTASKS_ID" = "PMTASKS"."ID"(+) AND
    ("PMEQMT".PK_HIDE IS NULL OR
    "PMEQMT"."PK_HIDE" = 'N') AND
    ("PMEQMT"."CLASS" = 'MT' or "PMEQMT"."CLASS" = 'TL') AND
    ("PMJOB"."TOTAL_UNITS"/"PMJOB"."ACT_EVERY")>.9 and
    ("PMJOB"."TOTAL_UNITS"/"PMJOB"."ACT_EVERY")<1.

    ORDER BY
    PMEQMT.EQNO ASC,
    PMTASKS.DESCRIP ASC

    Well I figured out that I cant make the choice in the SELECT part of the code. I am assuming my choice of a certain EPLANT would be in the WHERE part of the code. Am I getting closer???

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The post SQL syntax is valid, but since we don't know what are the requirements,
    we can't say how close you may be to the correct solution.
    Code:
    SELECT Substr("PMEQMT"."EQNO", 1, 10)                                      eqno,
           "PMEQMT"."CLASS",
           Round("PMJOB"."TOTAL_UNITS", 0)
           actual,
           Substr("PMJOB"."UOM", 1, 8)                                         uom,
           Substr("PMJOB"."ACT_EVERY", 1, 10)
           planned,
           Substr("PMTASKS"."DESCRIP", 1, 60)
           task_desc,
           Round(( ( "PMJOB"."TOTAL_UNITS" / "PMJOB"."ACT_EVERY" ) * 100 ), 0)
           percentage,
           pmeqmt.eplant_id
    FROM   "IQMS"."PMEQMT" "PMEQMT",
           "IQMS"."PMJOB" "PMJOB",
           "IQMS"."PMTASKS" "PMTASKS"
    WHERE  "PMEQMT"."ID" = "PMJOB"."PMEQMT_ID"(+)
           AND "PMJOB"."PMTASKS_ID" = "PMTASKS"."ID"(+)
           AND ( "PMEQMT".pk_hide IS NULL
                  OR "PMEQMT"."PK_HIDE" = 'N' )
           AND ( "PMEQMT"."CLASS" = 'MT'
                  OR "PMEQMT"."CLASS" = 'TL' )
           AND ( "PMJOB"."TOTAL_UNITS" / "PMJOB"."ACT_EVERY" ) > .9
           AND ( "PMJOB"."TOTAL_UNITS" / "PMJOB"."ACT_EVERY" ) < 1.
    ORDER  BY pmeqmt.eqno ASC,
              pmtasks.descrip ASC
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2011
    Posts
    5
    I’m lost, that’s why I am here posting and I am new to this kinda/sorta...

    But the code states select these tables, from the IQMS DB, then it says “WHERE” to my understanding we see a formal adding and subtracting total units and so on.

    Can’t we then add something to the effect of “where the Eplant_ID equals 1 or 2” inside the formula, or before the formula or after the formula?

    WHERE

    “PMEQMT"."ID" = "PMJOB"."PMEQMT_ID"(+) AND "PMJOB"."PMTASKS_ID" = "PMTASKS"."ID"(+) AND ( "PMEQMT".pk_hide IS NULL OR "PMEQMT"."PK_HIDE" = 'N' ) AND ("PMEQMT"."CLASS" = 'MT' OR "PMEQMT"."CLASS" = 'TL' ) AND ( "PMJOB"."TOTAL_UNITS" / "PMJOB"."ACT_EVERY" ) > .9 AND ( "PMJOB"."TOTAL_UNITS" / "PMJOB"."ACT_EVERY" ) < 1.

    Surely there must be code that states the entire above to be true plus or only if or equal to PMEQMT.EPLANT_ID is equal to EPLANT_ID 1 or 2

    If so due to my complete lack of SQL code knowledge what would such a code look like?

    When you say “requirements” what did I leave out of my post or can I add to my post?

    Thx!

    Mathew
    The Autism MX Project

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Get rid of the the double quotes, they are not needed and a bad habit to get into

    Code:
    wHERE PMEQMT.ID = PMJOB.PMEQMT_ID(+)
     AND PMJOB.PMTASKS_ID = PMTASKS.ID(+)
     AND ( PMEQMT.pk_hide IS NULL 
          OR PMEQMT.PK_HIDE = 'N' )
     AND (PMEQMT.CLASS = 'MT' 
          OR PMEQMT.CLASS = 'TL' ) 
    AND ( PMJOB.TOTAL_UNITS / PMJOB.ACT_EVERY ) > .9 
    AND ( PMJOB.TOTAL_UNITS / PMJOB.ACT_EVERY ) < 1
    and PMEQMT.EPLANT_ID in (1,2)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2011
    Posts
    5
    Thx Bill I will try it today and see where I get!

  6. #6
    Join Date
    Dec 2011
    Posts
    5

    Bill is too cool!

    Thx Bill! You made my day! And it makes me look I know what I am doing! Honestly thx very much!

    Can you point me in a direction of a doc or page that kind of breaks down code and explains what the ( mean and what does ‘ mean and when to use it. Sorry I just took over for a person on Disability so I can't call him to ask any questions.

    Again Bill! THX!! When I ran the Evaluate SQL added and PMEQMT.EPLANT_ID in (1) and only got results for Eplant 1 I was STOKED!! VERY COOL!!!!

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    While there are manuals that show sql syntax, I would suggest that you join a few good forums and read the questions and ask questions. A few that I get on to help is this one and

    Tek-Tips Forums
    Welcome to The Oracle FAQ | Oracle FAQ

    Enjoy oracle, its an interesting world.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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