Adding New Columns....

[vb89]

I tried to add two new columns to my current coding (they're in bold):

tTotalSQL = " replace(to_char(((sum(decode(minutes_seconds,null,minutes*60,minutes_seconds)) - (mod(sum(decode(minutes_seconds,null,minutes*60,minutes_seconds)),60)))/60),'999999990'),' ','')||decode(sum(minutes_seconds),null,'',':')||decode(sum(minutes_seconds),null,'',replace(to_char(mod(sum(decode(minutes_seconds,null,0,minutes_seconds)),60),'00'),' ','')) AS col0," &_
" sum(field_goals_made) AS col1," &_
" sum(field_goals_att) AS col2," &_
" DECODE(sum(field_goals_att),0,'-',to_char(sum(field_goals_made)/sum(field_goals_att),'9.900')) AS col3," &_
" sum(three_point_made) AS col4," &_
" sum(three_point_att) AS col5," &_
" sum(free_throws_made) AS col6," &_
" sum(free_throws_att) AS col7," &_
" DECODE(sum(free_throws_att),0,'-',to_char(sum(free_throws_made)/sum(free_throws_att),'9.900')) AS col8," &_
" sum(offensive_rebounds) AS col9," &_
" sum(defensive_rebounds) AS col10," &_
" sum(total_rebounds) AS col11," &_
" sum(assists) AS col12," &_
" sum(steals) AS col13," &_
" sum(blocks) AS col14," &_
" sum(turnovers) AS col15, " &_
" sum(personal_fouls) AS col16," &_
" sum(plus_minus) AS col17," &_
" sum(points) AS col18," &_
" sum(games) AS GP, sum(games_started) AS GS," &_
" DECODE(sum(games),0,'-',to_char(sum(points)/sum(games),'990.0')) AS avgpts," &_
" DECODE(sum(three_point_att),0,'-',to_char(sum(three_point_made)/sum(three_point_att),'9.900')) AS pct3p," &_
" sum(disqualifications) AS DQ, sum(technical_foul) AS Tech," &_
" sum(flagrant_fouls) AS Flag, sum(ejections) AS Eject," &_
" DECODE(sum(turnovers),0,'-',to_char(sum(assists)/sum(turnovers),'990.00')) AS ast_to," &_
" DECODE(sum(turnovers),0,'-',to_char(sum(steals)/sum(turnovers),'990.00')) AS stl_to," &_
" DECODE(sum(games),0,'-',to_char(sum(points+assists+total_rebounds)/sum(games),'990.0')) AS PAR_G," &_
" DECODE(sum(games),0,'-',to_char(sum(plus_minus)/sum(games),'990.0')) AS +/-PAR_G," &_
" DECODE(sum(minutes),0,'-',to_char(sum(points+assists+total_rebounds)*48/sum(minutes),'990.0')) AS PAR_48," &_
" DECODE(sum(minutes),0,'-',to_char(sum(plus_minus)*48/sum(minutes),'990.0')) AS +/-PAR_48," &_
" DECODE(sum(field_goals_att),0,'-',to_char(sum(field_goals_made+ 0.5*three_point_made)/sum(field_goals_att),'9.900')) AS TSP"

And for some reason the code throws an error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected

/data/nba/players/player_logs_test.asp, line 517

But this doesn't really make sense to me as the code on line 517/518 is:
517:Set rsTotals = SPSSconn.Execute(tTotalSQL)
518: response.Write("<tr align=""right"">")

Any ideas?

 

[jmeckley]

for future reference the forums you would use are
forum333
or one of these

this forum is dedicated to asp.net which is different than asp.

the error makes perfect sense. up until you execute the script it's just text, so there is no incorrect value. when you execute the script the database compiles the sql and the error is revealed. So the error comes from this line of code.

to remedy the problem you need to copy the value of 'tTotalSQL' to Oracle's equivalent of MS SQL Query Analyzer. Run the query to find the exact problem. Hint: the problem has to do with where you placed the From clause in the sql statement.

Jason Meckley
Programmer
Specialty Bakers, Inc.