Home » SQL & PL/SQL » SQL & PL/SQL » If Statement / Variable as an SQL Column name (Oracle 10g 10.2.0.4.0 / SQL*Plus 10.1.0.5.0)
If Statement / Variable as an SQL Column name [message #433949] Sat, 05 December 2009 09:57 Go to next message
chum1313
Messages: 2
Registered: August 2009
Junior Member
I am having two problems at the moment and I have tried and tried to solved them and look for the answer. I am new at SQL maybe a few months and all self taught so it's kind of embarrassing to show what I have written. I have looked all over the place to trying to get an answer for my problems.

As it stands right now I cannot get the if statement to work. I would also like to select a column based on a variable... and I have looked all over for that as well. The reason for this is one of the table has a column that only changes by the month number. The column is called budcurroblg## (with the ## representing the month number) so it would be nice if there was a way to select that column based on the xmonth.

Thanks in advance

-Tim

Code below:

accept xmonth prompt 'Enter Month Number:';
accept xyear prompt 'Enter the Fiscal Year:(YYYY)';
accept xdatestart
accept xdateend
if &xmonth = 01 then &xdatestart := (&xyear +1)||0101
elsif &xmonth = 02 THEN &xdatestart := (&xyear+1) ||0201
elsif &xmonth = 03 THEN &xdatestart := (&xyear+1)||0301
elsif &xmonth = 04 THEN &xdatestart := (&xyear+1)||0401
elsif &xmonth = 05 THEN &xdatestart := (&xyear+1)||0501
elsif &xmonth = 06 THEN &xdatestart := (&xyear+1)||0601
elsif &xmonth = 07 THEN &xdatestart := (&xyear+1)||0701
elsif &xmonth = 08 THEN &xdatestart := (&xyear+1)||0801
elsif &xmonth = 09 THEN &xdatestart := (&xyear+1)||0901
elsif &xmonth = 10 THEN &xdatestart := &xyear||1001
elsif &xmonth = 11 THEN &xdatestart := &xyear||1101
elsif &xmonth = 12 THEN &xdatestart := &xyear||1201
else &xdatestart := &xyear||1001
end if

if &xmonth = 01 then &xdateend := (&xyear +1)||0131
elsif &xmonth = 02 THEN & xdateend:= (&xyear+1) ||0231
elsif &xmonth = 03 THEN & xdateend:= (&xyear+1)||0331
elsif &xmonth = 04 THEN & xdateend:= (&xyear+1)||0431
elsif &xmonth = 05 THEN & xdateend:= (&xyear+1)||0531
elsif &xmonth = 06 THEN & xdateend:= (&xyear+1)||0631
elsif &xmonth = 07 THEN & xdateend:= (&xyear+1)||0731
elsif &xmonth = 08 THEN & xdateend:= (&xyear+1)||0831
elsif &xmonth = 09 THEN & xdateend:= (&xyear+1)||0931
elsif &xmonth = 10 THEN & xdateend:= &xyear||1031
elsif &xmonth = 11 THEN & xdateend:= &xyear||1131
elsif &xmonth = 12 THEN & xdateend:= &xyear||1231
else &xdatestart := (&xyear+1)||0931
end if

select pm, appn,
(sum(CASE lpad(type_trans,1) when 'C' Then amount Else Null End)) as oblig,
(sum(CASE lpad(type_trans,1) when 'E' Then amount Else Null End)) as disber,
((sum(CASE lpad(type_trans,1) when 'C' Then amount Else Null End))-(sum(CASE lpad(type_trans,1) when 'E' Then amount Else Null End))) as ULO,
max(budgetmaster.budafp) as afp,
max(budgetmaster.budallot) as allotment,
(((sum(CASE lpad(type_trans,1) when 'E' Then amount Else Null End))/(sum(CASE lpad(type_trans,1) when 'C' Then amount Else Null End)))*100) as percent,
((((sum(CASE lpad(type_trans,1) when 'C' Then amount Else Null End))-(sum(CASE lpad(type_trans,1) when 'E' Then amount Else Null End)))/(sum(CASE lpad(type_trans,1) when 'C' Then amount Else Null End)))*100) as PerULO
from stfhistory left outer join budgetmaster
on stfhistory.pm = budgetmaster.budpm
and stfhistory.fy = budgetmaster.budfy
and stfhistory.mdep = budgetmaster.budmdep
and stfhistory.amsco = budgetmaster.budmamsco
where fy = '&xyear'
and stfhistory.date_entered between &xdatestart and &dateend
and stfhistory.appn = '2060'
group by pm, appn
order by pm
/
Re: If Statement / Variable as an SQL Column name [message #433951 is a reply to message #433949] Sat, 05 December 2009 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Please realize & keep in mind that SQL (language) is different from PL/SQL (language) is different from SQL*Plus client tool.

What works in one context, may not work in the other.

IF ... THEN ... ELSE is a PL/SQL construct.

ACCEPT is a SQL*Plus command.

Simply put they are mutually exclusive.
To invoke PL/SQL from inside SQL*PLUS, it must be enclosed by
BEGIN
...
...
END;


HTH!
Re: If Statement / Variable as an SQL Column name [message #433952 is a reply to message #433951] Sat, 05 December 2009 10:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And for your problem, have a look at the decode statement.

(Although, if you have ANY control about the data model, ditch that table and start over, because having a column per month is bad design.)
Re: If Statement / Variable as an SQL Column name [message #433953 is a reply to message #433949] Sat, 05 December 2009 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
SELECT   pm,
         appn,
         (Sum(CASE Lpad(type_trans,1)
                WHEN 'C'
                THEN amount
                ELSE NULL
              END)) AS oblig,
         (Sum(CASE Lpad(type_trans,1)
                WHEN 'E'
                THEN amount
                ELSE NULL
              END)) AS disber,
         ((Sum(CASE Lpad(type_trans,1)
                 WHEN 'C'
                 THEN amount
                 ELSE NULL
               END)) - (Sum(CASE Lpad(type_trans,1)
                              WHEN 'E'
                              THEN amount
                              ELSE NULL
                            END))) AS ulo,
         Max(budgetmaster.budafp)                         AS afp,
         Max(budgetmaster.budallot)                       AS allotment,
         (((Sum(CASE Lpad(type_trans,1)
                  WHEN 'E'
                  THEN amount
                  ELSE NULL
                END)) / (Sum(CASE Lpad(type_trans,1)
                               WHEN 'C'
                               THEN amount
                               ELSE NULL
                             END))) * 100) AS percent,
         ((((Sum(CASE Lpad(type_trans,1)
                   WHEN 'C'
                   THEN amount
                   ELSE NULL
                 END)) - (Sum(CASE Lpad(type_trans,1)
                                WHEN 'E'
                                THEN amount
                                ELSE NULL
                              END))) / (Sum(CASE Lpad(type_trans,1)
                                              WHEN 'C'
                                              THEN amount
                                              ELSE NULL
                                            END))) * 100) AS perulo
FROM     stfhistory
         LEFT OUTER JOIN budgetmaster
           ON stfhistory.pm = budgetmaster.budpm
              AND stfhistory.fy = budgetmaster.budfy
              AND stfhistory.mdep = budgetmaster.budmdep
              AND stfhistory.amsco = budgetmaster.budmamsco
WHERE    fy = '&xyear'
         AND stfhistory.date_entered BETWEEN &xdatestart AND &dateend
         AND stfhistory.appn = '2060'
GROUP BY pm,
         appn
ORDER BY pm 
Re: If Statement / Variable as an SQL Column name [message #433954 is a reply to message #433952] Sat, 05 December 2009 10:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oh, any you could probably get rid of the whole if/elsif, which are not working in that place anyway, if you just used to_date and probably a few other date functions on the input. "add_months" is comes in handy a lot when dealing with fiscal years that differ from calendar years.
Re: If Statement / Variable as an SQL Column name [message #434213 is a reply to message #433949] Mon, 07 December 2009 14:24 Go to previous messageGo to next message
chum1313
Messages: 2
Registered: August 2009
Junior Member
Thanks for the help, still have not had any luck with it though. I tried the decode statement (in the select statement) and it just returns the field name and not any of the data...

As for using date functions... well the data is stored as a number not a date... somewhat frustrating and I cannot think of another way than an if style statement. (and as you can tell it is my first one and have no idea where to put it or how to do it.)

Thanks,

-Tim
Re: If Statement / Variable as an SQL Column name [message #434214 is a reply to message #434213] Mon, 07 December 2009 14:32 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: If Statement / Variable as an SQL Column name [message #434215 is a reply to message #434213] Mon, 07 December 2009 14:41 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
chum1313 wrote on Mon, 07 December 2009 21:24

I tried the decode statement (in the select statement) and it just returns the field name and not any of the data...


Use the column name without quotes.

select 'something' from ....


means select the string 'something'

select something from ....


means select the contents of the something column.
Re: If Statement / Variable as an SQL Column name [message #434216 is a reply to message #433949] Mon, 07 December 2009 15:32 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
chum1313 wrote on Sat, 05 December 2009 10:57

Code below:

if &xmonth = 01 then &xdatestart := (&xyear +1)||0101
elsif &xmonth = 02 THEN &xdatestart := (&xyear+1) ||0201


Even though it was told to you that SQL and PL/SQL cannot be mixed here, one thing to note for the future, something is wrong here. Either xmonth is a string and you are missing the quotes around the '01' or xmonth is a number and then 01 is not how the number 1 is represented as a number.
Previous Topic: loop with condition
Next Topic: copy a row and insert into same table
Goto Forum:
  


Current Time: Wed Dec 07 03:21:53 CST 2016

Total time taken to generate the page: 0.06674 seconds