Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SELECT & concatenate some fields? 3

Status
Not open for further replies.
LMCRYER

LMCRYER

Programmer
Jul 30, 2001
388
US
It's not a good Monday morning here, folks.

I have a table that contains the following:

TEXT_ID ROW_ADDED_TS SEQ_NBR C_1 C_2

90904 1 1000 ABC DEF
90904 2 2000 GHI JKL
90904 3 3000 MNO PQ

I'm trying to create a view that shows the TEXT_ID,
and then all of C_1 and C_2 nicely together like this:

90904 ABCDEFGHIJKLMNOPQ

I need to know the syntax for telling this to give me C_1, and then C-@ for all of the rows that have the same TEXT_ID number, and give them to me in the order of SEQ_NBR (in one field)....

I know how to do the select, but not how to get them all
into one field? Concatenate it? How?

Please take pity on a Crystal developer trying to learn Oracle =)

LMC



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Sort by date Sort by votes
I TRIED THIS:

SELECT TEXT_ID, concat(TXDT.COMMENT_ONE, TXDT.COMMENT_TWO)
FROM TXDT
WHERE TXDT.PREFIX_ID = 'PEL'
AND TXDT.TEXT_ID = '090904'
GROUP BY TEXT_ID

AND GOT THIS:

ORA-00979 Not a valid group by expression

hm..I was hoping if I grouped on TEXT_ID it would bring them all in together, wishful thinking!!



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Upvote 0 Downvote
The problem you are running into is that you are trying to concatenate values from different rows. You might have better luck by adding a function:

Code: 
CREATE OR REPLACE FUNCTION concat_c1_c2(p_text_id IN txdt.test_id%TYPE) RETURN VARCHAR2 AS
   l_string VARCHAR2(4000) := NULL;
BEGIN
   FOR i IN (SELECT c1, c2
               FROM txdt
              WHERE text_id = p_text_id) LOOP
      l_string := l_string||i.c1||i.c2;
   END LOOP;
   RETURN l_string;
END;
/

Then you can get what you want with:
Code: 
  SELECT text_id, concat_c1_c2(text_id) 
    FROM (SELECT DISTINCT text_id FROM txdt);
Note that you are going to have problems here if any concatenated string exceeds 4000 characters.

Elbert, CO
0904 MST
 
Upvote 0 Downvote
I can live with that. Is there a way I can stop the error before it happens? (in other words, can I tell this that if it hits 4000 to stop and just give me that?))

I'm off to try this now, thanks for the tip! I will follow up with a post so hopefully it will help somebody else, too!

LMC

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Upvote 0 Downvote
It worked, it worked! You made my morning! My week, actually!

Now that I have it coming in I am seeing another problem that I need to deal with - the concatenated fields are messy - the white space (blank) that exists between them is coming in as well. I want to trim these out so that they come in as one clean statement.

Can you please guide me through that?

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Upvote 0 Downvote
Ah! Also, I need to be able to do a SET for this inside of a view...is that possible?



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Upvote 0 Downvote
I got bold and tried to do this like so:

insert into COMMENT_1
VALUES(SELECT text_id, concat_c1_c2_LMCTEST031504(text_id)
FROM (SELECT DISTINCT text_id FROM txdt);


ERROR MSG: Error at line 34, col1 ORA - 00933 SQL Command not properly ended...

Any help out there?


LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Upvote 0 Downvote
Try altering the function something like this:

[tt] l_string := l_string || Ltrim(Rtrim(i.c1)) ||
' ' || Ltrim(Rtrim(i.c2)) || ' '; [/tt]

Try this for your INSERT statement:

[tt]insert into COMMENT_1
VALUES(
SELECT text_id, concat
FROM (SELECT DISTINCT text_id,
concat_c1_c2_LMCTEST031504(text_id) concat FROM txdt);
[/tt]
 
Upvote 0 Downvote
Almost there - I keep getting a "sql command not properly ended" and highlighting on the word INSERT...

It makes me think I need some kind of comma or something before the INSERT statement maybe???



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Upvote 0 Downvote
Here is the entire thing (in case I am leaving out anything of value)

SELECT
OORD.ORD_NBR,
PART.USER_FIELD_3,
SOPN.PO_OPERATION_DESC,
SOPN.MAJOR_SEQ_NBR,
SOPN.ALTERNATE_SEQ_NBR,
SOPN.MINOR_SEQ_NBR,
OORD.CONTROL_ID,
OORD.QTY_ON_ORD,
SOPN.PO_PLAN_RUN_RSRC_RQMT,
SOPN.PO_OPERATION_STATUS,
SOPN.PO_ACT_RUN_RSRC_USED,
CIDM.CTRL_ID,
CIDM.CTRL_ID_DESC,
OORD.ACTUAL_OPEN_DATE,
OORD.DATE_CLOSED,
SOPN.ORD_NBR as s_ORD_NBR,
CIDM.PROJ_NBR,
OORD.ORD_STAT,
SOPN.PO_OPERATION_CLASS_CODE,
OORD.PROD_ACTY_BF_IND
FROM
CSIOWNER.OORD OORD,
CSIOWNER.PART PART,
CSIOWNER.SOPN SOPN,
CSIOWNER.CIDM CIDM
WHERE
(OORD.PART_NBR=PART.PART_NBR (+)) AND
(OORD.ORD_NBR=SOPN.ORD_NBR (+)) AND
(OORD.CONTROL_ID=CIDM.CTRL_ID (+)) AND
(PART.USER_FIELD_3='ELK') ORDER BY CIDM.PROJ_NBR


insert into COMMENT_1
VALUES(
SELECT text_id, concat
FROM (SELECT DISTINCT text_id,
concat_c1_c2_LMCTEST031504(text_id) concat FROM txdt);


LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Upvote 0 Downvote
you need a semi-colon after your order by statement (before your insert statement).
 
Upvote 0 Downvote
You have no semi-colon (;) after your SELECT statement before the INSERT.

[tt] ORDER BY CIDM.PROJ_NBR;[/tt]
 
Upvote 0 Downvote
I added in the :)) and it keeps telling me that it is an invalid character!! ugh! Here it is again...

SELECT
OORD.ORD_NBR,
PART.USER_FIELD_3,
SOPN.PO_OPERATION_DESC,
SOPN.MAJOR_SEQ_NBR,
SOPN.ALTERNATE_SEQ_NBR,
SOPN.MINOR_SEQ_NBR,
OORD.CONTROL_ID,
OORD.QTY_ON_ORD,
SOPN.PO_PLAN_RUN_RSRC_RQMT,
SOPN.PO_OPERATION_STATUS,
SOPN.PO_ACT_RUN_RSRC_USED,
CIDM.CTRL_ID,
CIDM.CTRL_ID_DESC,
OORD.ACTUAL_OPEN_DATE,
OORD.DATE_CLOSED,
SOPN.ORD_NBR as s_ORD_NBR,
CIDM.PROJ_NBR,
OORD.ORD_STAT,
SOPN.PO_OPERATION_CLASS_CODE,
OORD.PROD_ACTY_BF_IND
FROM
CSIOWNER.OORD OORD,
CSIOWNER.PART PART,
CSIOWNER.SOPN SOPN,
CSIOWNER.CIDM CIDM
WHERE
(OORD.PART_NBR=PART.PART_NBR (+)) AND
(OORD.ORD_NBR=SOPN.ORD_NBR (+)) AND
(OORD.CONTROL_ID=CIDM.CTRL_ID (+)) AND
PART.USER_FIELD_3='ELK'
ORDER BY CIDM.PROJ_NBR;

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Upvote 0 Downvote
What application are you trying to run this in?

If its in a script, try removing the semi-colon (;) and placing a forward slash at the end:

[tt]WHERE
(OORD.PART_NBR=PART.PART_NBR (+)) AND
(OORD.ORD_NBR=SOPN.ORD_NBR (+)) AND
(OORD.CONTROL_ID=CIDM.CTRL_ID (+)) AND
PART.USER_FIELD_3='ELK'
ORDER BY CIDM.PROJ_NBR
/
[/tt]

Are you GETting the script? If so, the semi-colon will be brought in and if you try to execute the SQL it will cause the invalid character error.
 
Upvote 0 Downvote
I am in TOAD, SQL editor....

The view works FINE, but when I try to add in the INSERT at the bottom of it -- that is when it starts telling me I have errors...

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Livia8
  • Locked
  • Question
Question re: SELECT DISTINCT 1
Replies
6
Views
401
SkipVought
S
Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
2K
carp
carp
LarrySteele
  • Locked
  • Question
Searching multiple fields for any/all words from a search string.
Replies
2
Views
104
LarrySteele
LarrySteele
kernal
  • Locked
  • Question
CONCAT DayofWeek Fields if Field Value =Y 3
Replies
4
Views
99
kernal
kernal
greatwhite1
  • Locked
  • Question
Selecting Duplicating records 1
Replies
8
Views
114
carp
carp

Part and Inventory Search

Sponsor

Back
Top