Generating a command 1

[ddrillich]

Good Day,

I would like to generate a formatted Autonomy command that should look like -

http://localhost:9101/DREDELETEREF?docs=C023085335+C023085336+

C023085337

The content should come from the OID column in a table called v_product.

Is it possible to generate this Autonomy command via one SQL command?

Regards,
Dan

 

[SantaMufasa]

Dan,

Could you please post what have you tried so far?

What do you want to do with the "Autonomy command" once you generate it? I.e., where do you want the command to "go" once it exists?...Webclient?...text file?...other?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ddrillich]

Hi Dave,

I haven't tried much so far. Sorry about that.

The "Autonomy command" should hopefully go to a text file.

Regards,
Dan

 

[SantaMufasa]

http://localhost:9101/DREDELETEREF?docs=C023085335+C023085336+[/URL] C023085337", what would the corresponding value(s) look like in v_product.OID?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ddrillich]

Dave,

The corresponding values in v_product.OID are C023085335, C023085336, C023085337.

Regards,
Dan

 

[SantaMufasa]

...and what other value on those three v_product.OID rows link them all together?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ddrillich]

Dave,

It's actually very straight forward. I wish I could simply call you ;-)

All that I need is to concatenate these values using the plus sign.

Regards,
Dan

 

[ddrillich]

Ok, let me try to explain it in a different way.

I have a tiny table called emp, with four rows.

When running the command -
select empid from emp

I get the following result set -
1
2
3
4

I would like instead to get one string via the SQL command, such as 1+2+3+4.

Is it possible?

 

[SantaMufasa]

Dan,

Here is a code model that is more like your original. (I've made a few presumptions about join columns, but you can certainly modify the code, below, to fit your situation. Credit goes to Taupirho for recently teaching us the code construct that allows us, in Oracle 10g, to easily pivot a table):

select * from v_product;

ID OID             PRODUCT_LINE
-- --------------- ------------
 1 C023085335                10
 2 C023085336                10
 3 C023085337                10
 4 C023085338                30
 5 C023085339                30
 6 C023085340                20

select '[URL unfurl="true"]http://localhost:9101/DREDELETEREF?docs='||[/URL]
       substr(max(sys_connect_by_path(oid,'+')),2) URL
  from (select product_line, oid,row_number() over (partition by product_line order by oid) rn from v_product)
 start with rn=1
connect by prior rn = rn -1
       and prior product_line = product_line
 group by product_line
 order by product_line
/

URL
------------------------------------------------------------------------
[URL unfurl="true"]http://localhost:9101/DREDELETEREF?docs=C023085335+C023085336+C023085337[/URL]
[URL unfurl="true"]http://localhost:9101/DREDELETEREF?docs=C023085340[/URL]
[URL unfurl="true"]http://localhost:9101/DREDELETEREF?docs=C023085338+C023085339[/URL]

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ddrillich]

Dave,

Wow - as usual I'm sorry that I'm only able to grant you one star ;-)


Regards,
Dan

 

[ddrillich]

Dave,

Given your table's content, is it possible to generate only one URL? Such as -

http://localhost:9101/DREDELETEREF?...6+C023085337+C023085338+C023085339+C023085340

Regards,
Dan

 

[SantaMufasa]

Yes, Dan, it makes the code even simpler:

select '[URL unfurl="true"]http://localhost:9101/DREDELETEREF?docs='||[/URL]
       substr(max(sys_connect_by_path(oid,'+')),2) URL
  from (select product_line, oid,row_number() over (order by oid) rn from v_product)
 start with rn=1
connect by prior rn = rn -1
 order by product_line
/

URL
---------------------------------------------------------------------------------------------------------
[URL unfurl="true"]http://localhost:9101/DREDELETEREF?docs=C023085335+C023085336+C023085337+C023085338+C023085339+C023085340[/URL]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ddrillich]

Thank you Dave!!!