[development-axapta] RE: Invalid date foramt SQL error

[James Flavell]

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: 'development-axapta@yahoogroups.com'
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]