error in this query "Msg 258, Level 15, State 1, Line 38 Cannot call methods on int." PLZ help

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

SQL

select * from SAl_OrderHeader soh
where (
select  OrderID from (
select
SAL_OrderDetail.Quantity as SalesOrderQuantity
, isnull((
select SUM(Quantity) from SAL_deliveryOrderDetail
where SAL_deliveryOrderDetail.SalesOrderHeaderID = SAL_OrderHeader.OrderID
and SAL_deliveryOrderDetail.SalesOrderDetailID = SAL_OrderDetail.OrderDetailID
and SAL_deliveryOrderDetail.CompanyID = SAL_OrderHeader.CompanyID
and SAL_deliveryOrderDetail.PeriodID = SAL_OrderHeader.PeriodID
and SAL_deliveryOrderDetail.Status = 1

),0 ) as DeliveryOrderQuantity
,isnull  (SAL_OrderDetail.ReversedQuantity , 0 )as Rev_Qty
,SET_DocumentType.DocumentType
, SAL_OrderHeader.ContractNo
,SAL_OrderHeader.ContractDate
,SAL_OrderHeader.OrderID
from SAL_OrderHeader

inner join SAL_OrderDetail on  SAL_OrderDetail.OrderID = SAL_OrderHeader.OrderID
and SAL_OrderDetail.CompanyID = SAL_OrderHeader.CompanyID
and SAL_OrderDetail.PeriodID = SAL_OrderHeader.PeriodID
and SAL_OrderDetail.status = 1
and SAL_OrderHeader.Status = 1

inner join SET_DocumentType on SET_DocumentType.DocumentTypeID =  SAL_OrderHeader.SaleContractTypeID
and SET_DocumentType.CompanyID =  SAL_OrderHeader.CompanyID
and SET_DocumentType.PeriodID =  SAL_OrderHeader.PeriodID
and SET_DocumentType.Status =  1
) as tab

where (tab.SalesOrderQuantity - DeliveryOrderQuantity) - tab.Rev_Qty > 0


).[OrderID] = soh.OrderID

Posted 28-May-14 23:53pm

A.R Farooqui

Updated 28-May-14 23:58pm

v2

Add a Solution

Comments

CHill60 29-May-14 6:01am

That Where clause looks horrendous ... construct it properly and the problem with .[OrderID] should go away

CHill60 29-May-14 6:06am

Ok ... I think I've worked out what you're doing.
Try replacing
select * from SAl_OrderHeader soh where (
with select * from SAl_OrderHeader soh INNER JOIN (
and ).[OrderID] = soh.OrderID
with )X ON X.[OrderID] = soh.OrderID

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

A.R Farooqui · Accepted Answer · 2014-05-29T19:49:00

Problem Solved ... i was putting where clause in place of join ...

select
soh.OrderID
,soh.ContractNo
,soh.ContractDate
,SET_DocumentType.DocumentType
from SAl_OrderHeader soh
inner join (
select * from (
select
SAL_OrderDetail.Quantity as SalesOrderQuantity
, isnull((
select SUM(Quantity) from SAL_deliveryOrderDetail
where SAL_deliveryOrderDetail.SalesOrderHeaderID = SAL_OrderHeader.OrderID
and SAL_deliveryOrderDetail.SalesOrderDetailID = SAL_OrderDetail.OrderDetailID
and SAL_deliveryOrderDetail.CompanyID = SAL_OrderHeader.CompanyID
and SAL_deliveryOrderDetail.PeriodID = SAL_OrderHeader.PeriodID
and SAL_deliveryOrderDetail.Status = 1

),0 ) as DeliveryOrderQuantity
,isnull (SAL_OrderDetail.ReversedQuantity , 0 )as Rev_Qty
,SET_DocumentType.DocumentType
, SAL_OrderHeader.ContractNo
,SAL_OrderHeader.ContractDate
,SAL_OrderHeader.OrderID
,SAL_OrderHeader.CompanyID
,SAL_OrderHeader.PeriodID
from SAL_OrderHeader

inner join SAL_OrderDetail on SAL_OrderDetail.OrderID = SAL_OrderHeader.OrderID
and SAL_OrderDetail.CompanyID = SAL_OrderHeader.CompanyID
and SAL_OrderDetail.PeriodID = SAL_OrderHeader.PeriodID
and SAL_OrderDetail.status = 1
and SAL_OrderHeader.Status = 1

inner join SET_DocumentType on SET_DocumentType.DocumentTypeID = SAL_OrderHeader.SaleContractTypeID
and SET_DocumentType.CompanyID = SAL_OrderHeader.CompanyID
and SET_DocumentType.PeriodID = SAL_OrderHeader.PeriodID
and SET_DocumentType.Status = 1
) as tab

where (tab.SalesOrderQuantity - DeliveryOrderQuantity) - tab.Rev_Qty > 0

) tab2 on tab2.OrderID = soh.OrderID
and tab2.CompanyID = soh.CompanyID
and tab2.PeriodID = soh.PeriodID
and soh.Status = 1

inner join SET_DocumentType on Set_DocumentType.DocumentTypeID = soh.SaleContractTypeID
and Set_DocumentType.CompanyID = soh.CompanyID
and Set_DocumentType.PeriodID = soh.PeriodID
and Set_DocumentType.Status = 1

and Soh.CompanyID =1
and Soh.PeriodID =1

order by cast( Soh.ContractNo as int ) asc