Home » SQL & PL/SQL » SQL & PL/SQL » Pass Append 'pipes' || as part of a string
Pass Append 'pipes' || as part of a string [message #186364] Mon, 07 August 2006 13:15 Go to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Hello!

I was curious if anyone out there has experience with the following scenerio. I am building myself a dynamic SQL statement in a PL/SQL package. One of the fields that is being selected is a concatenation of six segments from the E-Business Suite (gl_code_combinations segments1-6). This string forms a unique accounting 'bucket' in my report. The problem I'm hitting, even though the package validates, is that when it attempts to execute the SQL statement, it cannot find the 'FROM' keyword. I can show you what I'm doing with an example:

		l_select_clause := SELECT aia.invoice_num              INVOICE_NUMBER,'||
                                 ' pov.vendor_name              VENDOR_NAME,'||
                                 ' aid.distribution_line_number DISTRIBUTION_NUMBER,'||
                                 ' ROUND(aid.amount , 2)        AMOUNT,'||
                                 ' gcc.segment1'||'''-'''||
 								 ' gcc.segment2'||'''-'''||
								 ' gcc.segment3'||'''-'''||
								 ' gcc.segment4'||'''-'''||
								 ' gcc.segment5'||'''-'''||
								 ' gcc.segment6                 ACCOUNTING_STRING';
								 
		-- Set FROM clause of dynamic cursor c_report
		l_from_clause := ' FROM ap_invoices_all                 aia,'|| 
                             ' ap_invoice_distributions_all    aid,'|| 
                             ' ap_payment_schedules_v          aps,'|| 
                             ' po_vendors                      pov,'|| 
							 ' gl_code_combinations            gcc'; 



It's a little tricky to tell, but see where I'm concatenating all the gcc.segments? I need to be able to pass in the concatenation pipes as part of the string, but I'm not sure on how to accomplish this. I need for the ACCOUNTING_STRING to read in the output as:
segment1_value-segment2_value-segment3_value-segment4_value-segment5_value-segment6_value. I see the dashes, but its having a hard time concatenating them together as part of the string. I hope I am clear.

Please advise!

Thanks,
Steve
Re: Pass Append 'pipes' || as part of a string [message #186373 is a reply to message #186364] Mon, 07 August 2006 13:58 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If I understand correctly, I think you're looking for:

' gcc.segment1 ||''-''||' ||
' gcc.segment2 ||''-''||' ||
' gcc.segment3 ||''-''||' ||
' gcc.segment4 ||''-''||' ||
' gcc.segment5 ||''-''||' ||
' gcc.segment6 ACCOUNTING_STRING';


which would produce this output:

gcc.segment1 ||'-'|| gcc.segment2 ||'-'|| gcc.segment3 ||'-'|| gcc.segment
4 ||'-'|| gcc.segment5 ||'-'|| gcc.segment6 ACCOUNTING_STRING

[Updated on: Mon, 07 August 2006 13:59]

Report message to a moderator

Re: Pass Append 'pipes' || as part of a string [message #186375 is a reply to message #186364] Mon, 07 August 2006 13:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You want the double pipes inside your string:
' gcc.segment1||''-''||
gcc.segment2||''-''||
gcc.segment3||''-''||
gcc.segment4||''-''||
gcc.segment5||''-''||
gcc.segment6


Hint: assign the sql to a tempstring and dbms_output it. It will make things much clearer!
Re: Pass Append 'pipes' || as part of a string [message #186376 is a reply to message #186375] Mon, 07 August 2006 14:01 Go to previous message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Thank you both for your prompt replies. I am a bit of a newbie at this, so I was a little unsure on how to proceed. I didn't want to mess up my running application (I know, but TEST is down for a very specific patching requirement at the moment) so I appreciate your input!

Cheers! Cool

Steve
Previous Topic: Inserts
Next Topic: count()
Goto Forum:
  


Current Time: Mon Dec 05 19:23:21 CST 2016

Total time taken to generate the page: 0.21350 seconds