Toad World® Forums

How to get Script to work?

Hello,

I was wondering if someone could please help me out in getting the below script to work since I’m not an expert in SQL

What I need is to also get the Description of the EAC … the same as the ACTDES.

If this helps this is the script that pulls in the EAC + Description so I need to add this someway to the script below?

select flex_value, substr(t.description,0,50)

from apps.fnd_flex_values_vl t

where t.flex_value_set_id = 1009148

This is the script that I’m trying to get to work:

select

j.period_name MTH,

g.segment1 CO,

g.segment2 OU,

(g.segment3||’ - '||substr(t.description,0,50)) ACTDES,

substr(j.attribute3,1,6) EAC, — How do we add the EAC + Description

to_number((nvl(j.accounted_dr,0)-nvl(j.accounted_cr,0))) VALUE

from apps.gl_code_combinations g,

    apps.gl_je_categories_tl c,

    apps.gl_je_lines j,

    apps.gl_je_headers h,

    apps.gl_je_sources_tl s,

    apps.fnd_flex_values_vl t

where j.status=‘P’

and t.flex_value_set_id = 1009133

and t.flex_value = g.segment3

and h.actual_flag = ‘A’

and h.je_category = c.je_category_name

and h.je_source=s.je_source_name

and g.code_combination_id = j.code_combination_id

and j.period_name in (‘Jul-16’)

and h.ledger_id=326

and j.je_header_id = h.je_header_id

and(g.segment3) = (‘70050’)

If somone could please help out that would be greatly appreciated

Thanks
Example.docx (28.4 KB)

Hi Paul,

I’m not following, so this is best guess. Apologies if I’m wrong.

I think you want to ‘add’ the EAC and the ACTDES together as a string?

Select

Some stuff here… ,

substr(j.attribute3,1,6) || g.segment3 || ’ - ’ || substr(t.description,0,50) as EACTDESC ,

Other stuff here …

Where … ;

HTH

Cheers,

Norm. [TeamT]

On 24 March 2017 13:38:42 GMT+00:00, “paul.sousa” bounce-paulsousa@toadworld.com wrote:

How to get Script to work?

Thread created by paul.sousa
Hello,

I was wondering if someone could please help me out in getting the below script to work since I’m not an expert in SQL

What I need is to also get the Description of the EAC … the same as the ACTDES.

If this helps this is the script that pulls in the EAC + Description so I need to add this someway to the script below?

select flex_value, substr(t.description,0,50)

from apps.fnd_flex_values_vl t

where t.flex_value_set_id = 1009148

This is the script that I’m trying to get to work:

select

j.period_name MTH,

g.segment1 CO,

g.segment2 OU,

(g.segment3||’ - '||substr(t.description,0,50)) ACTDES,

substr(j.attribute3,1,6) EAC, — How do we add the EAC + Description

to_number((nvl(j.accounted_dr,0)-nvl(j.accounted_cr,0))) VALUE

from apps.gl_code_combinations g,

    apps.gl_je_categories_tl c,
    apps.gl_je_lines j,
    apps.gl_je_headers h,
    apps.gl_je_sources_tl s,
    apps.fnd_flex_values_vl t

where j.status=‘P’

and t.flex_value_set_id = 1009133

and t.flex_value = g.segment3

and h.actual_flag = ‘A’

and h.je_category = c.je_category_name

and h.je_source=s.je_source_name

and g.code_combination_id = j.code_combination_id

and j.period_name in (‘Jul-16’)

and h.ledger_id=326

and j.je_header_id = h.je_header_id

and(g.segment3) = (‘70050’)

If somone could please help out that would be greatly appreciated

Thanks

Attachments:

 [Example.docx](http://www.toadworld.com/cfs-file/__key/telligent-evolution-components-attachments/00-10-00-00-00-03-23-48/Example.docx)

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

Sent from my Android device with K-9 Mail. Please excuse my brevity.

Hello Norm,

Thanks for getting back to me…

This ACTDESC is actually working as expected so I dont need any changes to this one

(g.segment3||’ - '||substr(t.description,0,50)) ACTDES,

This field is where I need attribute3 eac and additional Description that is stored in the same field as above (t.descpription)

substr(j.attribute3,1,6) eac,

Hi Paul,

ok, I’ve been able to open the docs file you attached, now that I’m at home. My tablet couldn’t open it when I replied earlier.

So, you want to combine the EAC and the EACDES columns into one column of output? I think! (I’m old, be gentle with me - I have trouble understanding sometimes!)

select eac || ‘=’ || aecdes

from table

where something = something_else;

The concatenation operator, the double pipe symbol, || , is what you use to “add” strings together. In the above, I’ve added an equals sign between them to make it a bit more obvious.

You can, if desired, substr(), upper(), lower() etc each or any of the contcatenated columns if necessary.

You’d get something like:

A1011=70050 - Car Parking Charges

as the output. Quite a few times it seems too, given the sample data.

HTH

Cheers,

Norm. [TeamT]

Great…thanks so much Norm for your help

Much appreciated

On 24/03/17 19:02, paul.sousa wrote:

*RE: How to get Script to work?

Reply by paul.sousa

Great…thanks so much Norm for your help

Much appreciated

No problems Paul, there’s always someone on these lists willing to help.

We are a most helpful and friendly bunch. Well, I think we are! :slight_smile:

Cheers,

Norm. [TeamT]