Microsoft Dynamics GP (Archived)

Range Where not working

(0) Share

Report

Posted on by Mohammed Irfan

185

I have written the following range on RM_OPEN

range clear RM_OPEN;

range table RM_OPEN where physicalname('Document Number' of table RM_OPEN)
+ CH_SPACE + "IN" + "(" + CH_SPACE + "SELECT" + CH_SPACE + physicalname('Apply To Document Number' of table RM_Applied_OPEN)
+ CH_SPACE + "FROM" + CH_SPACE + physicalname(table RM_Applied_OPEN)
+ CH_SPACE + "WHERE" + CH_SPACE + physicalname('Apply From Document Number' of table RM_Applied_OPEN)
+ CH_SPACE + "=" + s_document_number + ")";

warning string(countrecords(table RM_OPEN));

fill window 'Scrolling_Window' table RM_OPEN;

I am trying to achieve this SQL query in dex

select * from RM20101 WHERE DOCNUMBR IN(SELECT APTODCNM FROM RM20201 WHERE APFRDCNM = 'PYMNT000000000080')

this receipt(PYMNT000000000080) has 10 records in rm20201

I am getting following error
"A countrecords operation on table 'RM_OPEN' cannot find the table.

Anyone Suggest whats wrong with this range??

OR

How can i write this in Pass through SQL???

*This post is locked for comments

All responses (4)

Answers (1)

Mohammed Irfan 185 on at

Like (1)

Report

RE: Range Where not working

Siddhartha ,

I have followed the doc and the range is working now,

Almas I guess we should write TABLE name ex. RM20101 as [DATABASE].[dbo].[RM20101], which can be done by inserting the following code in range statement before table nam

'Intercompany ID' of globals + CH_PERIOD + SQL_DEFAULT_OWNER + CH_PERIOD

Thanks Siddhartha and Almas, Appreciate your suggestions.
Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

RE: Range Where not working

It's a good example what Siddhartha shared, you can see that how customer master is subquery by using where clause.
Verified answer

Siddhartha Challa 92 on at

Like (2)

Report

RE: Range Where not working

Irfan ,

I hope this will help

support.microsoft.com/.../how-to-use-a-range-where-clause-that-is-based-on-more-than-one-table-i
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

RE: Range Where not working

Irfan ,

Rang can work on single table at a time. so your code will never work like that.