Home » SQL & PL/SQL » SQL & PL/SQL » CAN YOU??
CAN YOU?? [message #599186] Tue, 22 October 2013 18:40 Go to next message
nataliafoster26
Messages: 64
Registered: October 2013
Member
can you have a have a variable for example 'CODE_EX'
where you can
CODE_EX varchar2 := 'CASE TABLE_COLLUMN.TABLE_NAME ||
' WHEN ....;
FOR EXAMPLE THIS BELOW WORKS
vSalesCenterId2 VARCHAR2(300) := 'SELECT sc_name vDesc, sales_center_id vCode '||
                                 ' FROM s_sales_center '||
                                 ' ORDER BY sales_center_id';

IF NOT WHAT CAN I DO?this may sound like a dumb question
or maybe have a decode

[Updated on: Tue, 22 October 2013 18:42]

Report message to a moderator

Re: CAN YOU?? [message #599187 is a reply to message #599186] Tue, 22 October 2013 18:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
you can assign any characters you desire to any VARCHAR2 variable.

Dynamic SQL does not scale & should be avoided when ever possible.

what problem are you actually trying to solve?

How will you, I or anyone recognize any specific post as containing the desired & correct solution?
Re: CAN YOU?? [message #599188 is a reply to message #599186] Tue, 22 October 2013 19:06 Go to previous messageGo to next message
nataliafoster26
Messages: 64
Registered: October 2013
Member
this is what i have created right now, comes from s_sales_center table
CREATE TABLE PREMIER.S_SALES_CENTER
(
SALES_CENTER_ID NUMBER(12) NOT NULL,
CURRENCY_CODE VARCHAR2(3 BYTE),
MASTER_SALES_CENTER_ID NUMBER(12),
INF_SRC_DB VARCHAR2(3 BYTE) NOT NULL,
)
if inf_src_db =AUS" THEN CURRENCY CODE WOULD EQUL USD OTHERWISE BPS
I dont see any mistakes but still a long time ultil i try it
vcode varchar2 := select inf_src_dd infdb, currency_code ocode
                      from s_sales_center
                      if infdb='AUS'
                      THEN UPDATE S_SALES_CENTER
set currency_code = case inf_src_db when 'AUS' then 'USD' else 'BPS' end

i would think from this it would return me either usd or bps

[Updated on: Tue, 22 October 2013 19:07]

Report message to a moderator

Re: CAN YOU?? [message #599189 is a reply to message #599188] Tue, 22 October 2013 19:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
do you realize & understand that SQL & PL/SQL are entirely DIFFERENT & independent languages?
do you realize that IF ... THEN construct is only valid in PL/SQL & does not exist for plain SQL (like SELECT, INSERT, UPDATE, & DELETE)?

IF INFDB = 'AUS'
THEN
UPDATE S_SALES_CENTER SET CURRENCY_CODE = 'USD'
ELSE
UPDATE S_SALES_CENTER SET CURRENCY_CODE = 'BPS'
END IF

You can have whole, complete & valid SQL statements that can be executed or branched around depending upon external conditions
and not have to resort to Dynamic SQL since the actual SQL statements are known & fixed.
I still do not understand what problem you are trying to solve or what you would deem an acceptable or desired results.
Re: CAN YOU?? [message #599191 is a reply to message #599188] Tue, 22 October 2013 21:00 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I have already provided a solution in your previous topic for this.
http://www.orafaq.com/forum/mv/msg/189968/598814/#msg_598814

And once again, when you could do in static SQL why do you want dynamic sql? For that matter why pl/sql at all?

Update : Provided link from previos topic where it was already answered

[Updated on: Tue, 22 October 2013 21:16]

Report message to a moderator

Previous Topic: PLSQL
Next Topic: dropdown
Goto Forum:
  


Current Time: Thu Mar 28 04:45:28 CDT 2024