Ask a Question related to Dreamweaver AppDev, Design and Development.
-
-D- #1
Select Case SQL recordset help
I was given the following SQL statement to drop into DMX and create a
recordset: SELECT CASE 'allotype' CASE 'STRATEGY': strSQL = 'select
STRTGY_ALLOC_AMT ' & _ 'from DIM_STRTGY_ALLOC, DIM_DDO, DIM_FY, ' & _
'DIM_STRTGY ' & _ 'where DIM_STRTGY_ALLOC.STRTGY_ALLOC_DDO_ID =
DIM_DDO.DDO_ID ' & _ 'and DIM_STRTGY_ALLOC.STRTGY_ALLOC_AY = DIM_FY.FY_ID
' & _ 'and DIM_STRTGY_ALLOC.STRTGY_ALLOC_STRTGY_ID = DIM_STRTGY.STRTGY_ID '
& _ 'and DIM_DDO.DDO_DSCR_SHORT = 'ddo' ' & _ 'and DIM_FY.FY_CHAR =
'yr' ' & _ 'and DIM_STRTGY.STRTGY_NBR = 'strategy' ' CASE 'BUDGET
OBJECT': strSQL = 'select BUDG_OBJT_ALLOC_AMT ' & _ 'from
DIM_BUDG_OBJT_ALLOC, DIM_DDO, DIM_FY, ' & _ 'DIM_BUDG_OBJT ' & _ 'where
DIM_BUDG_OBJT_ALLOC.BUDG_OBJT_ALLOC_DDO_ID = DIM_DDO.DDO_ID ' & _ 'and
DIM_BUDG_OBJT_ALLOC.BUDG_OBJT_ALLOC_AY = DIM_FY.FY_ID ' & _ 'and
DIM_BUDG_OBJT_ALLOC.BUDG_OBJT_ALLOC_BUDG_OBJT_ID = DIM_BUDG_OBJT.BUDG_OBJT_ID '
& _ 'and DIM_DDO.DDO_DSCR_SHORT = 'ddo' ' & _ 'and DIM_FY.FY_CHAR =
'yr' ' & _ 'and DIM_BUDG_OBJT.BUDG_OBJT_NBR = 'budgobj' ' & _ CASE
'STAFFING': strSQL = 'select STAFFING_ALLOC_AMT ' & _ 'from
DIM_STAFFING_ALLOC, DIM_DDO, DIM_FY, ' & _ 'DIM_FTE_CLASS ' & _ 'where
DIM_STAFFING_ALLOC.STAFFING_ALLOC_DDO_ID = DIM_DDO.DDO_ID ' & _ 'and
DIM_STAFFING_ALLOC.STAFFING_ALLOC_AY = DIM_FY.FY_ID ' & _ 'and
DIM_STAFFING_ALLOC.STAFFING_ALLOC_FTE_CLASS_ID = DIM_FTE_CLASS.FTE_CLASS_ID '
& _ 'and DIM_DDO.DDO_DSCR_SHORT = 'ddo' ' & _ 'and DIM_FY.FY_CHAR =
'yr' ' & _ 'and DIM_FTE_CLASS.FTE_CLASS_NBR = 'FTEClass' ' END SELECT I'm
passing the the following variable values using a QueryString: ddo =
Request.QueryString('ddo') yr = Request.QueryString('yr') allotype =
Request.QueryString('allotype') strategy = Request.QueryString('strategy')
budgobj = Request.QueryString('BO') FTEClass = Request.QueryString('FTEClass')
I need to re-write the SQL so I can pass in the values from QueryString and
process the data. I need to remove all the VbScript code and just have a clean
SQL statement where I can pass in the values from the QueryString. I'm trying
to adjust the SQL so that when I open the recordset, I would have the
following: rsAlloOrigAmt.Source = 'SELECT CASE... or rsFundOrigAmt.Source =
strSQL I'm having some difficulty ammending this statement. I'm getting a
case syntax error. Thanks, -D-
-D- Guest
-
Complicated "CASE WHEN" query for Dreamweaver Recordset
Hello, I have a table called "categories_ctg" with the following fields: id_ctg, int(11), auto_increment idctg_ctg, int(11)(can be NULL)... -
sql select case statement
Hi, Im trying to put together a sql select case statement for coldfusion - can anyone please check over my code and see if i am going along the... -
recordset.recordcount return allways -1 on select statements
Greetings On my ASP application I want to retrieve the number (Recordset.recordCount property) of records affected by a select statement, but I... -
#23026 [Com]: Make Zend case-sensitive (classes, functions, remove case-insensitive)
ID: 23026 Comment by: nvivo at mandic dot com dot br Reported By: mfischer@php.net Status: Open Bug Type: ... -
RecordSet.Move or RecordSet.AbsolutePosition??
Hi, I'm trying to use either one of these methods to position the cursor in a specific position inside a recordset, but neither one seems to... -
-D- #2 Re: Select Case SQL recordset help
As a follow-up, I've built my recordset like the following: <% Dim
rsAllOrigAmt__ddo rsAlloOrigAmt__ddo = 'xyz' If (Request.QueryString('ddo') <>
'') Then rsAlloOrigAmt__ddo = Request.QueryString('ddo') End If %> <% Dim
rsAlloOrigAmt__yr rsAlloOrigAmt__yr = 'xyz' If (Request.QueryString('yr') <>
'') Then rsAlloOrigAmt__yr = Request.QueryString('yr') End If %> <% Dim
rsAlloOrigAmt__allotype rsAlloOrigAmt__allotype = 'xyz' If
(Request.QueryString('allotype') <> '') Then rsAlloOrigAmt__allotype =
Request.QueryString('allotype') End If %> <% Dim rsAlloOrigAmt__strategy
rsAlloOrigAmt__strategy = 'xyz' If (Request.QueryString('strategy') <> '') Then
rsAlloOrigAmt__strategy = Request.QueryString('strategy') End If %> <% Dim
rsAlloOrigAmt__budgobj rsAlloOrigAmt__budgobj = 'xyz' If
(Request.QueryString('BO') <> '') Then rsAlloOrigAmt__budgobj =
Request.QueryString('BO') End If %> <% Dim rsAlloOrigAmt__FTEClass
rsAlloOrigAmt__FTEClass = 'xyz' If (Request.QueryString('FTEClass') <> '') Then
rsAlloOrigAmt__budgobj = Request.QueryString('FTEClass') End If %> <% Dim
rsAlloOrigAmt Dim rsAlloOrigAmt_numRows SELECT CASE rsAlloOrigAmt__allotype
CASE 'STRATEGY': strSQL = 'select STRTGY_ALLOC_AMT ' & _ 'from
DIM_STRTGY_ALLOC, DIM_DDO, DIM_FY, ' & _ 'DIM_STRTGY ' & _ 'where
DIM_STRTGY_ALLOC.STRTGY_ALLOC_DDO_ID = DIM_DDO.DDO_ID ' & _ 'and
DIM_STRTGY_ALLOC.STRTGY_ALLOC_AY = DIM_FY.FY_ID ' & _ 'and
DIM_STRTGY_ALLOC.STRTGY_ALLOC_STRTGY_ID = DIM_STRTGY.STRTGY_ID ' & _ 'and
DIM_DDO.DDO_DSCR_SHORT = 'rsAlloOrigAmt__ddo' ' & _ 'and DIM_FY.FY_CHAR =
'rsAlloOrigAmt__yr' ' & _ 'and DIM_STRTGY.STRTGY_NBR =
'rsAlloOrigAmt__strategy' ' CASE 'BUDGET OBJECT': strSQL = 'select
BUDG_OBJT_ALLOC_AMT ' & _ 'from DIM_BUDG_OBJT_ALLOC, DIM_DDO, DIM_FY, '
& _ 'DIM_BUDG_OBJT ' & _ 'where
DIM_BUDG_OBJT_ALLOC.BUDG_OBJT_ALLOC_DDO_ID = DIM_DDO.DDO_ID ' & _ 'and
DIM_BUDG_OBJT_ALLOC.BUDG_OBJT_ALLOC_AY = DIM_FY.FY_ID ' & _ 'and
DIM_BUDG_OBJT_ALLOC.BUDG_OBJT_ALLOC_BUDG_OBJT_ID = DIM_BUDG_OBJT.BUDG_OBJT_ID '
& _ 'and DIM_DDO.DDO_DSCR_SHORT = 'rsAlloOrigAmt__ddo' ' & _ 'and
DIM_FY.FY_CHAR = 'rsAlloOrigAmt__yr' ' & _ 'and DIM_BUDG_OBJT.BUDG_OBJT_NBR
= 'rsAlloOrigAmt__budgobj' ' & _ CASE 'STAFFING': strSQL = 'select
STAFFING_ALLOC_AMT ' & _ 'from DIM_STAFFING_ALLOC, DIM_DDO, DIM_FY, ' &
_ 'DIM_FTE_CLASS ' & _ 'where DIM_STAFFING_ALLOC.STAFFING_ALLOC_DDO_ID =
DIM_DDO.DDO_ID ' & _ 'and DIM_STAFFING_ALLOC.STAFFING_ALLOC_AY =
DIM_FY.FY_ID ' & _ 'and DIM_STAFFING_ALLOC.STAFFING_ALLOC_FTE_CLASS_ID =
DIM_FTE_CLASS.FTE_CLASS_ID ' & _ 'and DIM_DDO.DDO_DSCR_SHORT =
'rsAlloOrigAmt__ddo' ' & _ 'and DIM_FY.FY_CHAR = 'rsAlloOrigAmt__yr' '
& _ 'and DIM_FTE_CLASS.FTE_CLASS_NBR = 'rsAlloOrigAmt__budgobj' ' END
SELECT Set rsAlloOrigAmt = Server.CreateObject('ADODB.Recordset')
rsAlloOrigAmt.ActiveConnection = MM_DBConn_STRING rsAlloOrigAmt.Source = strSQL
rsAlloOrigAmt.CursorType = 0 rsAlloOrigAmt.CursorLocation = 2
rsAlloOrigAmt.LockType = 1 rsAlloOrigAmt.Open() rsAlloOrigAmt_numRows = 0 %>
<% If NOT rsAlloOrigAmt.EOF Then Dim y y = Request.QueryString('LoopNum')
Session('ddomenuA' & y) = Request.QueryString('ddo') Session('yrA' & y)
= Request.QueryString('yr') Session('allocationtypeA' & y) =
Request.QueryString('allotype') Session('strategyB' & y) =
Request.QueryString('strategy') Session('BOA' & y) =
Request.QueryString('BO') Session('FTEClassA' & y) =
Request.QueryString('FTEClassA') Session('origBAA' & y) =
rsAlloOrigAmt.Fields.Item('BUDGET_AMT_AMT').Value
Response.Redirect('request.asp?#alloanchor') else %> I think I've built the
recordset correctly, but how can I get the variable value for the QueryString
to not be a string as in the following line: 'and DIM_DDO.DDO_DSCR_SHORT =
'rsAlloOrigAmt__ddo' ' & _ The quotes seem to keep 'rsAlloOrigAmt__ddo'
from being a variable value from the QueryString Thanks, -D-
-D- Guest
-
-D- #3 Re: Select Case SQL recordset help
I corrected the problem...I needed to concatenate the variable values within the SQL string.
Thanks,
-D-
-D- Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
