Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using insert and delete at the same time

tennekOct 9 2013 — edited Oct 9 2013

Good afternoon,

i want to do an insert from table A into table B, while at the same time do a delete on table A

and i want to do it with out procedures.

here is the script i came up with,

INSERT into table_a (select * from table_b where status = 'D') DELETE from table_b where status = 'D'

but i get an ORA-00933 error.

Got any ideas on how i can do this??

thank you
This post has been answered by Mike Kutz on Oct 9 2013
Jump to Answer

Comments

metalray
I learned a bit more and figured out that the presentation variables get set wrong "<center><font Size=’4′><font color=”#000000″>@{P_OFFICE}</font></font></center>".
P_OFFICE gets populated with the display value of the repositories logical column i..e the dashboard prompts source, rather than the sort value (the ID).
Why is that? I extra created logical columns to have a behind the scenes ID value for every display value.
SriniVEERAVALLI
I assume your PS variable name something like P_OFFICE, and in the BI Pub report use as :P_OFFICE in your data source query.
Do not refer the PS variable as @{P_OFFICE} in BI Pub report. I doubt on the datatypes, values from PS variable.
1 person found this helpful
metalray
Hi. That helped. I get the right value now in the dropdownlist. When defining the dashboard prompt I wrote some custom SQL:

SELECT "A11_DIM_PERIOD"."DESCTEXT", "A11_DIM_PERIOD"."KEY" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY "A11_DIM_PERIOD"."KEY". Now the prompt shows the display values but as soon as I select on and hit apply the dropdownlist text changes to the Key values (whicih is fine for my parameters, because they need to be key values rather than text) but this is unfortunate for the front end user, they will wonder "hey, why did the drop down list value I selected just change". Right now, I dont know why. The prompts work fine just odd that their display data changes from rpd logical column description field to sort field.
SriniVEERAVALLI
When defining the dashboard prompt I wrote some custom SQL:
SELECT "A11_DIM_PERIOD"."DESCTEXT", "A11_DIM_PERIOD"."KEY" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY >"A11_DIM_PERIOD"."KEY"
In Prompt we go by only one column, in your query I see 2 columns. Since you need only Key use the below

SELECT "A11_DIM_PERIOD"."KEY" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY "A11_DIM_PERIOD"."KEY"
metalray
Hi Srini,
Thanks for the reply. I do need to key but I need the description(text) value too. The end user can not select the "Office Location" by ID/KEY only :)
The ID/KEY is used in the data warehouse because the fact tables only contains ID's but the dimension table has ID and Text value. I need to have both in the dropdownlist.
SriniVEERAVALLI
In that case you need to go with 2 prompts in BI and 2 parameter in BI Pub

SELECT "A11_DIM_PERIOD"."DESCTEXT" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY "A11_DIM_PERIOD"."KEY"

SELECT "A11_DIM_PERIOD"."KEY" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY "A11_DIM_PERIOD"."KEY"
1 person found this helpful
metalray
So the end-user has to select everything double? that is not too great.
It seems the "apply" of my dashboard prompts doesnt to anything unless I switch the
"automatic execution" in my report settings on. Which is really not what I prefer to do.
That means on every visit of the dashboard the report is executed even before the user
has the chance to select some meaningfull parameters.
metalray

Anyone an idea if there is a better way to do that?

metalray
Answer

I  gave up on it and now the whole parameter passing happens using the descriptive strings rather than IDs.

Marked as Answer by metalray · Sep 27 2020
1 person found this helpful
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 6 2013
Added on Oct 9 2013
#delete, #insert, #select
23 comments
4,551 views