Nick_001
Posts: 2
|
| Posted: 10/13/2008, 1:28 AM |
|
Hi
Any assistance for the following problem would be much appreciated.
We changed the contents of an Oracle view and it still compiles correctly and also returns the correct field datatype's as before, however now ASP says that there is a data type mismatch error, although the number data type for field 'AVG_AGE' is still the same as before since we verified this with the .NET Server explorer.
The view is called FR_resolved_per region and listed in the snippet's of code below .
The line of code AGE = CLNG(Recordset("AVG_AGE") * 10) / 10 from the common.asp file is causing the type mismatch error.
It is also fairly urgent that we get a fix for this soon.
Kind regards
Nick
/* Asp code
SQL = "SELECT * FROM FR_RESOLVED_PER_REGION ORDER BY REGION"
Set RecordSet = Connection.Execute(SQL)
While not RecordSet.EOF
REGION = RecordSet("REGION")
QTY = RecordSet("QTY")
AGE = CLNG(Recordset("AVG_AGE") * 10) / 10
/* Oracle View
CREATE OR REPLACE VIEW FR_RESOLVED_PER_REGION ( REGION, AVG_AGE, QTY ) AS SELECT T_REGION REGION, round(AVG(AGE),4) AS AVG_AGE , COUNT(1) AS QTY FROM
(SELECT INITCAP(A.T_REGION) || ' Region' T_REGION,
round( x.DATE_RESOLVED - x.DATE_REQ, 3) AS AGE
FROM FALL_OUT_REQUEST X, ( SELECT DISTINCT * FROM FR_TOWN ) A
WHERE X.DATE_RESOLVED >= TO_DATE('15/12/2003','DD/MM/YYYY')
AND X.LM_REF IN ( SELECT DISTINCT Y.LM_REF
FROM FR_TRAIL Y
WHERE Y.TIMESTAMP >= TO_DATE('15/12/2003','DD/MM/YYYY')
AND STATUS = 'R'
AND Y.USERNAME <> 'SYSTEM')
AND X.TOWN = A.TOWN
AND X.STATUS = 'R'
AND A.T_REGION IS NOT NULL
UNION ALL
SELECT 'Corporate' T_REGION, round( x.DATE_RESOLVED - x.DATE_REQ, 3) AS AGE
FROM FALL_OUT_REQUEST X, ( SELECT DISTINCT * FROM FR_TOWN ) A
WHERE X.DATE_RESOLVED >= TO_DATE('15/12/2003','DD/MM/YYYY')
AND X.LM_REF IN ( SELECT DISTINCT Y.LM_REF
FROM FR_TRAIL Y
WHERE Y.TIMESTAMP >= TO_DATE('15/12/2003','DD/MM/YYYY')
AND STATUS = 'R'
AND Y.USERNAME <> 'SYSTEM')
AND X.TOWN = A.TOWN
AND X.STATUS = 'R'
AND A.T_REGION IS NULL)
GROUP BY T_REGION
|
 |
 |
Edd
Posts: 547
|
| Posted: 10/14/2008, 2:24 PM |
|
I think your view is screwed. There are more columns in the first part of the union than the second and ther field alignments will cause the recordset to return crap.
Look at the results from the record set in a query and determine if they are correct or not.
Cheers - Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
Nick_001
Posts: 2
|
| Posted: 10/15/2008, 12:16 AM |
|
Hi Edd, thanks for your reply
I checked what you said and the first part of the union
SELECT INITCAP(A.T_REGION) || ' Region' T_REGION,
round( x.DATE_RESOLVED - x.DATE_REQ, 3) AS AGE
. . . . .
and the second part
SELECT 'Corporate' T_REGION, round( x.DATE_RESOLVED - x.DATE_REQ, 3) AS AGE
. . . .
both only return two columns each which match correctly and
with identical column headers.
The values in the T_Region field is either Corporate or
a specified . . . Region which is correct. This is also how our previous view worked, which displayed correctly on the Asp
report. The new modified view returns identical results
[field values with same data type] but for some reason shows an error in Asp as an data type mismatch.
This is leaving me feeling perplexed.
Below is the old view, however we had to modify the new view shown in prev email , since it was leaving out the word region at the end of the region name so it could return the correct results. Except now it won't work from the website .
CREATE OR REPLACE VIEW FR_RESOLVED_PER_REGION_PREV ( REGION,
AVG_AGE, QTY ) AS SELECT REGION , round(AVG(AGE),4)AS AVG_AGE , COUNT(1) AS QTY FROM (
SELECT C.REGION, round(DATE_RESOLVED - DATE_REQ, 3) AS AGE
FROM FALL_OUT_REQUEST A, (SELECT DISTINCT LM_REF,USERNAME AS USERNAME ,STATUS FROM FR_TRAIL ) B, USER_REGION C
WHERE DATE_REQ > TO_DATE('31/12/2003','DD/MM/YYYY') AND A.LM_REF = B.LM_REF (+) AND B.STATUS = 'R' AND B.USERNAME = C.USERNAME (+) AND A.STATUS = 'R' AND C.REGION IS NOT NULL
UNION ALL
SELECT 'Corporate' AS REGION , round(DATE_RESOLVED - DATE_REQ , 3) AS AGE
FROM FALL_OUT_REQUEST A, (SELECT DISTINCT LM_REF,USERNAME AS USERNAME ,STATUS FROM FR_TRAIL ) B, USER_REGION C
WHERE DATE_REQ > TO_DATE('31/12/2003','DD/MM/YYYY') AND A.LM_REF = B.LM_REF (+) AND B.STATUS = 'R' AND B.USERNAME
= C.USERNAME (+) AND A.STATUS = 'R' AND C.REGION IS NULL
)
GROUP BY REGION ORDER BY REGION
|
|
|
|
Tuong Do
|
| Posted: 10/16/2008, 3:04 PM |
|
Try changing the line since the value of Agv_age * 10 might be larger than
type long in ASP
AGE = CLNG(Recordset("AVG_AGE") * 10) / 10
To
AGE = ROUND(Recordset("AVG_AGE") , 1 )
"Nick_001" <Nick_001@forum.codecharge> wrote in message
news:648f306a1be71e@news.codecharge.com...
> Hi
>
> Any assistance for the following problem would be much appreciated.
>
> We changed the contents of an Oracle view and it still compiles correctly
> and
> also returns the correct field datatype's as before, however now ASP says
> that
> there is a data type mismatch error, although the number data type for
> field
> 'AVG_AGE' is still the same as before since we verified this with the .NET
> Server explorer.
> The view is called FR_resolved_per region and listed in the snippet's of
> code
> below .
>
> The line of code AGE = CLNG(Recordset("AVG_AGE") * 10) / 10 is causing the
> type mismatch error.
>
> It is also fairly urgent that we get a fix for this soon.
>
> Kind regards
> Nick
>
> /* Asp code
>
> SQL = "SELECT * FROM FR_RESOLVED_PER_REGION ORDER BY REGION"
> Set RecordSet = Connection.Execute(SQL)
>
> While not RecordSet.EOF
> REGION = RecordSet("REGION")
> QTY = RecordSet("QTY")
> AGE = CLNG(Recordset("AVG_AGE") * 10) / 10
>
> /* Oracle View
>
> CREATE OR REPLACE VIEW FR_RESOLVED_PER_REGION ( REGION,
> AVG_AGE, QTY ) AS SELECT REGION , AVG(AGE)AS AVG_AGE , COUNT(1) AS QTY
> FROM (
> SELECT REGION, round(DATE_RESOLVED - DATE_REQ, 3) AS AGE
> FROM FR_WITH_USER
> WHERE DATE_REQ > TO_DATE('31/12/2003','DD/MM/YYYY') AND STATUS = 'R'
> AND REGION IS NOT NULL AND USERNAME <> 'SYSTEM'
>
> UNION ALL
>
> SELECT 'Corporate' AS REGION , round(DATE_RESOLVED - DATE_REQ , 3) AS AGE
> FROM FR_WITH_USER
> WHERE DATE_REQ > TO_DATE('31/12/2003','DD/MM/YYYY') AND STATUS = 'R'
> AND REGION IS NULL AND USERNAME <> 'SYSTEM'
> )
>
> GROUP BY REGION ORDER BY REGION
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
