Okay i found the solution by rearranging the code as follows:
while select priceDiscGroup
where
priceDiscGroup.Module == ModuleInventCustVend::Cust &&
priceDiscGroup.Type == PriceGroupType::PriceGroup &&
(crtDateFrom >= priceDiscGroup.FromDate ||
priceDiscGroup.FromDate == datenull())
&& (crtDateTo <= priceDiscGroup.ToDate ||
priceDiscGroup.ToDate == datenull())
join promoPriceGroup where
priceDiscGroup.groupId == promoPriceGroup.CustPriceGroup
&& promoPriceGroup.CustAccount==invoiceAccount
join priceDiscTable where
promoPriceGroup.CustPriceGroup==priceDiscTable.AccountRelation
&& priceDiscTable.Module==ModuleInventCustVend::Cust
&& priceDiscTable.AccountCode==TableGroupAll::GroupId
&& priceDiscTable.ItemCode==TableGroupAll::Table
&& priceDiscTable.ItemRelation==this.ItemId
&& (crtDateFrom >= priceDiscTable.FromDate ||
priceDiscTable.FromDate==DateNull())
&& (crtDateTo <= priceDiscTable.ToDate ||
priceDiscTable.ToDate==DateNull())
&& priceDiscTable.Currency==this.CurrencyCode
Thanks for listening
James
_____
From: James Flavell [mailto:[EMAIL PROTECTED]
Sent: 12 August 2008 14:51
To: '[email protected]'
Subject: Invalid date foramt SQL error
Hi everyone,
I think I remember someone posted before some problem in std AX (Ax3.0)
about when you have a join statement. I have the following code which gives
an SQL error:
static void Job19(Args _args)
{
PriceDiscGroup priceDiscGroup;
PromoPriceGroup promoPriceGroup;
FromDate crtDateFrom = systemdateget();
ToDate crtDateTo = crtDateFrom;
;
while select priceDiscGroup
join promoPriceGroup
where (crtDateFrom >= priceDiscGroup.FromDate ||
priceDiscGroup.FromDate == datenull())
&& (crtDateTo <= priceDiscGroup.ToDate ||
priceDiscGroup.ToDate == datenull())
&& priceDiscGroup.Module == ModuleInventCustVend::Cust &&
priceDiscGroup.Type == PriceGroupType::PriceGroup &&
priceDiscGroup.groupId == promoPriceGroup.CustPriceGroup
{
print pricediscgroup.GroupId;
pause;
}
}
SQL error given:
Error Message (13:49:46) Cannot select a record in Price groups
(PriceDiscGroup). Price groups: , .
The SQL database has issued an error.
Info Message (13:49:46) SQL error description: [Microsoft][ODBC SQL Server
Driver]Invalid date format
Info Message (13:49:46) SQL statement: SELECT
A.MODULE,A.TYPE,A.GROUPID,A.NAME,A.INCLTAX,A.TODATE,A.FROMDATE,A.PROMOTION,A
.MODIFIEDDATE,A.MODIFIEDTIME,A.MODIFIEDBY,A.CREATEDDATE,A.CREATEDTIME,A.CREA
TEDBY,A.RECID,B.CUSTACCOUNT,B.TODATE,B.FROMDATE,B.CUSTPRICEGROUP,B.MODIFIEDD
ATE,B.MODIFIEDTIME,B.MODIFIEDBY,B.CREATEDDATE,B.CREATEDTIME,B.CREATEDBY,B.RE
CID FROM PRICEDISCGROUP A(NOLOCK) ,CITPROMOPRICEGROUP B(NOLOCK) WHERE
(A.DATAAREAID=?) AND ((B.DATAAREAID=?) AND ((((((?>=A.FROMDATE) OR
(A.FROMDATE=?)) AND ((?<=A.TODATE) OR (A.TODATE=?))) AND (A.MODULE=?)) AND
(A.TYPE=?)) AND (A.GROUPID=B.CUSTPRICEGROUP))) OPTION(FAST 83)
If just do the code to search on the one table priceDiscGroup then it works
fine!
I hope someone can help me understand (is it something about I cannot have
multiple conditions or use variables on a field from a joined table??? or
maybe I am doing somethign completely wrong in my join???)
Thanks
James
[Non-text portions of this message have been removed]
