Filtering a field in a Table ?

stagger43 · #1 (**permalink**) 12-07-11, 17:02

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???

anacedent · #2 (**permalink**) 12-07-11, 18:08

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

stagger43 · #3 (**permalink**) 12-07-11, 21:20

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

beilstwh · #4 (**permalink**) 12-08-11, 09:14

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)

stagger43 · #5 (**permalink**) 12-08-11, 11:06

Thx Bill I will try it today and see where I get!

stagger43 · #6 (**permalink**) 12-08-11, 11:27

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!!!!

beilstwh · #7 (**permalink**) 12-12-11, 10:18

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech