compute clause

Question

Post reply

compute clause

sukhoi971

SSCarpal Tunnel

Points: 4833
More actions
May 28, 2004 at 4:46 am

#161443

i have a query written in a stored procedure and in it there is a compute by clause , now how do i access
this compute by clause value ?? at client side iam using ADO 2.7
becoz there is no name for the compute clause
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 1

sorry i forgot to paste the query

here is my query

-----------

SELECT '1'=1,RMCPL_PACK_SIZEApr,RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_NAME AS sample,

RMCPL_EXPECTED_DT,ss.RMCPL_SAMPLE_NAME as APPROVEDSAMP,

BALQTY=(RMCPL_REQUISITION.RMCPL_QTY_APPROVED-sum(isnull(DispatchQty,0))),

RMCPL_REQUISITION.RMCPL_QTY_APPROVED,RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD,

RMCPL_EMP_MASTER.RMCPL_EMP_NAME AS Emp,RMCPL_REQUISITION.RMCPL_PACK_SIZE,

RMCPL_REQUISITION.RMCPL_QTY_REQUIRED, RMCPL_REQUISITION.RMCPL_DATE,RMCPL_REQUISITION.RMCPL_TID

FROM RMCPL_REQUISITION INNER JOIN RMCPL_SAMPLE_MASTER

ON RMCPL_REQUISITION.RMCPL_SAMPLE_CD = RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD

INNER JOIN RMCPL_EMP_MASTER ON RMCPL_REQUISITION.RMCPL_EMP_CD = RMCPL_EMP_MASTER.RMCPL_EMP_CD

inner join rmcpl_sample_master ss on RMCPL_REQUISITION.RMCPL_SAMPLE_CDApr =ss.RMCPL_SAMPLE_CD

left join SampleDispatchDetails dd on RMCPL_REQUISITION.RMCPL_TID=dd.RMCPL_TID

where RMCPL_REQUISITION.RMCPL_QTY_APPROVED IS not NULL AND RMCPL_REQUISITION.RMCPL_EXPECTED_DT

is not null group by RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_NAME , RMCPL_EXPECTED_DT,ss.RMCPL_SAMPLE_NAME ,

RMCPL_REQUISITION.RMCPL_QTY_APPROVED,RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD,

RMCPL_EMP_MASTER.RMCPL_EMP_NAME ,RMCPL_REQUISITION.RMCPL_PACK_SIZE,RMCPL_PACK_SIZEApr,

RMCPL_REQUISITION.RMCPL_QTY_REQUIRED, RMCPL_REQUISITION.RMCPL_DATE,RMCPL_REQUISITION.RMCPL_TID

having sum(isnull(DispatchQty,0))< RMCPL_REQUISITION.RMCPL_QTY_APPROVED

order by 1

compute sum(1) by 1

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

noeld SSC Guru Points: 96590 More actions · Answer 2

I am really sorry to be the carrier of bad news but the compute by clause produces NON RELATIONAL Results therefore forget about ADO

Now if the output is to be used by a report remove the compute by and handle that on the report. there are also the "With ROLLUP / CUBE" statements which can help you probably but I don't have representative data of what you want.

HTH

* Noel

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Are you sure, I thought you were geenrated multiple recordset in that case and could use .NextRecordset to move thru them. Sorry nothing to test with here to be sure.

noeld SSC Guru Points: 96590 More actions · Answer 4

Tell you what... it's a very bad habit that I have to not check BOL sometimes. I remenber when in our shop it was decided not to use compute by if favor of WITH ROLLUP but I stand corrected by Antares686!!! you do get 2 recorsets per group on the output its probably something hard to handle with a common reporting tool but not with ADO on middle tier.

The real reason was because it is a backward compatible feature not because of the relational nature of the set!

Thank you for the correction it helped me too with the that not very often used feature (in my case)

* Noel