trying for distinct records - not working! 1

[LMCRYER]

OK, I openly admit I am not an Oracle person - I am having some trouble with getting my head used to the syntax differences, etc. -- I have a Crystal report that is using this data (with a subreport) and it pulls in EXACTLY what I need, but I need to get rid of the subreport (long story) and cant seem to get the view in Oracle to behave.

I have a table that shows unique workorder_no's. This table is HUGE and I have a select statement that works perfectly for this part. I have a second table workorder_def, which has multiple records for each workorder_no.

For example:

12345 This is line item description one and
12345 the table brings in multiple lines for ea
12345 ch work order. I need only one line, the very
12345 top one, (first record entered) for the
12345 work order number.

I have one field in this table called row_added_ts, which is a timestamp of the entry. I am trying to do a select statement taht will give me one entry for each work order, and it should be the one with the earliest timestamp on it.
Its simple to do in Crystal, real easy in SQL, and yet I cant get it to behave in Oracle. I think something's different with the way it handles DISTINCT?

I've been wrestling the darn thing for 2 full days now, and would truly appreciate any and all help!!

LMC
cryerlisa@hotmail.com
cryerwest@myaetherbb.com



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[ddrillich]

select min(row_added_ts), workorder_no
from workorder_def
group by workorder_no

 

[LMCRYER]

So I have to have this busted out separately before I can get to the description?

I thought I'd be able to get

select min(row_added), workorder_no, workorder_desc
from workorder_def
group by workorder_no

BUT whenever I try to put that last piece on (workorder_Desc) the whole thing goes bonkers. This field type is long.



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[bgill]

When you say that "the whole thing goes bonkers" what do you mean?
Do you get an error message? does the select statement return any data, and if so, what?
Also you say

"I thought I'd be able to get

select min(row_added), workorder_no, workorder_desc
from workorder_def
group by workorder_no "

Is that just a typo or should min(row_added) not be
min(row_added_ts)

 

[BJCooperIT]

Distinct filters a list down to unique values. If you asked people what their favorite color is, you might get the following:

[u]NAME[/u]    [u]FAVORITE[/u]
FRED    BLUE
SALLY   PINK
DAN     ORANGE
TIM     RED
SUE     BLUE
CHRIS   PINK

When you [tt]select distinct FAVORITE from TAB;[/tt]
you would get:

[u]FAVORITE[/u]
BLUE
ORANGE
PINK
RED

If you post your code for your view maybe we can help more.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[LMCRYER]

OK, here is what I am trying to do - I have to get only the first entry for each workorder (I'm really only interested in the comment_one and comment_two fields, which are to be concatenated together later on...)

CREATE OR REPLACE FORCE VIEW CSIOWNER.WO_ELK_TEST_LMC
(TEXT_ID, ROW_ADDED_TS, COMMENT_ONE, COMMENT_TWO)
AS
SELECT
TXDT.TEXT_ID,
TXDT.ROW_ADDED_TS,
TXDT.COMMENT_ONE,
TXDT.COMMENT_TWO
FROM
TXDT
WHERE
TXDT.PREFIX_ID = 'PEL' AND
TXDT.TEXT_ID IN (SELECT DISTINCT WOPROJ.ORD_NBR FROM WOPROJ_RPT WOPROJ) and
TXDT.ROW_ADDED_TS = (select min (row_added_ts) from txdt where txdt.text_id in (select distinct WOPROJ.ORD_NBR from WOPROJ_RPT WOPROJ))
group by ROW_ADDED_TS, text_id, comment_one, comment_two;

I'm obviously doing SOMETHING wrong! =(


LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[BJCooperIT]

My apologies since I cannot proof the syntax right now. You might find this easier using an inline view on the distinct Order Numbers. Try this:

SELECT t.TEXT_ID,
       t.ROW_ADDED_TS,
       t.COMMENT_ONE ||' '|| t.COMMENT_TWO COMMENTS
FROM   TXDT t,
       (SELECT DISTINCT r.ORD_NBR
        FROM   WOPROJ_RPT r) w
WHERE  t.PREFIX_ID = 'PEL' 
AND    t.TEXT_ID   = WOPROJ.ORD_NBR
AND    t.ROW_ADDED_TS =
       (select min(m.ROW_ADDED_TS)
        from   TXDT m
        where  m.TEXT_ID = w.ORD_NBR)
group by t.ROW_ADDED_TS, t.TEXT_ID, t.COMMENTS;

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[BJCooperIT]

Doggonit!

AND    t.TEXT_ID   = w.ORD_NBR

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[LMCRYER]

Error message - INVALID COLUMN NAME
(note - the COMMENTS on the ORDER BY statement is highlighted)

This is exactly what I typed in:

SELECT t.TEXT_ID,
t.ROW_ADDED_TS,
t.COMMENT_ONE ||' '|| t.COMMENT_TWO COMMENTS
FROM TXDT t,
(SELECT DISTINCT r.ORD_NBR
FROM WOPROJ_RPT r) w
WHERE t.PREFIX_ID = 'PEL'
AND t.TEXT_ID = WOPROJ.ORD_NBR
AND t.TEXT_ID = w.ORD_NBR
AND t.ROW_ADDED_TS =
(select min(m.ROW_ADDED_TS)
from TXDT m
where m.TEXT_ID = w.ORD_NBR)
group by t.ROW_ADDED_TS, t.TEXT_ID, COMMENTS

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[BJCooperIT]

OK.

SELECT t.TEXT_ID,
       t.ROW_ADDED_TS,
       t.COMMENT_ONE ||' '|| t.COMMENT_TWO  COMMENTS
FROM   TXDT t,
       (SELECT DISTINCT r.ORD_NBR
        FROM   WOPROJ_RPT r) w
WHERE  t.PREFIX_ID = 'PEL' 
AND    t.TEXT_ID   = w.ORD_NBR
AND    t.TEXT_ID   = w.ORD_NBR
AND    t.ROW_ADDED_TS =
       (select min(m.ROW_ADDED_TS)
        from   TXDT m
        where  m.TEXT_ID = w.ORD_NBR)
group by t.ROW_ADDED_TS,
         t.TEXT_ID,
         t.COMMENT_ONE ||' '|| t.COMMENT_TWO

Let me know if this works for you!

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[LMCRYER]

Thank you, thank you, thank you!! I'm so grateful.
It worked beautifully.

Now off to tackle my next one. We are very short staffed and I've been given a bunch of Crystal reports (which I can use easily) to convert into stored proc's and views (to eliminate subreports, etc) - so I'm getting a very painful introduction to Oracle, what fun!!

(dont get me wrong, I'm loving it, but only because I have tek-tips!!)

LMC

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[BJCooperIT]

What a strange coincidence Lisa, I am an Oracle Developer pulling my hair out on a Crystal Reports contract!

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[LMCRYER]

I contract out for Crystal and am fairly sane with it, let me know if you have any questions, I would be thrilled to return the favor!

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned