2. ASPRunnerPro General questions
  3. [SOLVED]  Complex SQL Views don't Work
This topic is locked
[SOLVED]

 Complex SQL Views don't Work

 		1/13/2010 7:03:09 PM
ASPRunnerPro General questions
P
 paulwebb author

Relatively simple SQL modified manually in [Query Designer/SQL/Results] works fine when hitting the results tab, but does not work when compiled - gives Server Error.

I've had this before a number of times.

A work-round is to put this on the server as a 'view', but I want/need to develop these on client side
Why does this valid SQL work to deliver 'results' , and what can I do to make it work compiled?
declare @vdate datetime

set @vdate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
SELECT

cc.contractid,

cc.name as client,
CASE WHEN (charindex('(',cc.contracttitle)< 2) THEN cc.contracttitle

ELSE left(cc.contracttitle, charindex('(',cc.contracttitle)-1)

END as Project,
(cc.nettvaluegpb - isnull(lc.nettvaluegbp,0)) as change,

((cc.nettvaluegpb - isnull(lc.nettvaluegbp,0)) * cc.vap) as cva,
cc.va,

cc.vap,

cc.fex,

cc.nettvalue,

cc.nettvaluegpb,
cc.toinvoice,

cc.toinvoicegbp,

cc.currencyid,

cc.createddate,

cc.catsales,

cc.contractcategoryid
FROM dbo.bsp_currentcontracts cc

left outer join dbo.SalesContractHistoryData as lc
on cc.contractid = lc.contractid
WHERE (lc.contractid is null)

or ((cc.nettvalue<>lc.nettvalue) AND (lc.valuedate = @vdate))
ORDER BY contractid desc
Sergey Kornilov admin 1/14/2010

You cannot use any TSQL statements in SQL query. It won't work in ASPRunnerPro.
Try the following:



SELECT

cc.contractid,

cc.name as client,
CASE WHEN (charindex('(',cc.contracttitle)< 2) THEN cc.contracttitle

ELSE left(cc.contracttitle, charindex('(',cc.contracttitle)-1)

END as Project,
(cc.nettvaluegpb - isnull(lc.nettvaluegbp,0)) as change,

((cc.nettvaluegpb - isnull(lc.nettvaluegbp,0)) * cc.vap) as cva,
cc.va,

cc.vap,

cc.fex,

cc.nettvalue,

cc.nettvaluegpb,
cc.toinvoice,

cc.toinvoicegbp,

cc.currencyid,

cc.createddate,

cc.catsales,

cc.contractcategoryid
FROM dbo.bsp_currentcontracts cc

left outer join dbo.SalesContractHistoryData as lc
on cc.contractid = lc.contractid
WHERE (lc.contractid is null)

or ((cc.nettvalue<>lc.nettvalue) AND (lc.valuedate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))
ORDER BY contractid desc
P
 		paulwebb author 1/18/2010

Thanks for solving the last problem - I now have a UNION ALL query that again works in the results tab, but not when complied. Surely UNION is ANSI and supported?
Or is there a better way to do what I'm trying to do?
SELECT

'Nov-09' as [month],

sum(mc.nettvaluegbp - isnull(lc.nettvaluegbp,0)) as [change],

sum((mc.nettvaluegbp - isnull(lc.nettvaluegbp,0)) mc.vap) as [cva]

FROM dbo.SalesContractHistoryData mc

left outer join (select from dbo.SalesContractHistoryData where valuedate = '2009/10/31') as lc

on lc.contractid = mc.contractid

where mc.valuedate = '2009/11/30'

and ((lc.contractid is null) or (mc.nettvalue<>isnull(lc.nettvalue,0)))
UNION ALL
SELECT

'Dec-09' as [month],

sum(mc.nettvaluegbp - isnull(lc.nettvaluegbp,0)) as [change],

sum((mc.nettvaluegbp - isnull(lc.nettvaluegbp,0)) mc.vap) as [cva]

FROM dbo.SalesContractHistoryData mc

left outer join (select from dbo.SalesContractHistoryData where valuedate = '2009/11/30') as lc

on lc.contractid = mc.contractid

where mc.valuedate = '2009/12/31'

and ((lc.contractid is null) or (mc.nettvalue<>isnull(lc.nettvalue,0)))
P
 		paulwebb author 1/18/2010

I should add ... it 'works' (ie, no error), but only gives me the first SELECT line
R
 		rascasse83 1/19/2010

Hi not sure why you create this statement in your front-end rather than building a view in your database ?

Performance-wise a view running on your database server will be much faster.
J
 		Jane 1/20/2010

Hi,
unfortunately ASPRunnerpro do not support UNION.

As workaround create view in the database directly and then use this view in the project.
P
 		paulwebb author 1/21/2010

Thanks - I concluded that this wouldn't work after looking at the output generated.

I was trying to do this in front end, but have now done it on the server as a view.

Thanks for the support.