Avatar billede scarface335 Nybegynder
15. april 2010 - 10:21 Der er 11 kommentarer og
1 løsning

Problemer med sql statement, cannot call method on nvarchar.

Hej.

Først lige lidt om applikationerne jeg arbejder med.

Jeg arbejder i SQL server Business Intelligence Development studio. Hvor jeg er i gang med at configure default OLAP cubes for microsoft Dynamics AX, Efter jeg har redigeret en *Named Query", får jeg følgende fejl. Deferred prepare could not be completed. Statement(s) could not be prepared. Cannot call method on nVarchar. Her er min SQL statement.

SELECT    CUSTTABLE.ACCOUNTNUM, CUSTTABLE.CASHDISC, CUSTTABLE.SHIPCARRIERBLINDSHIPMENT, CUSTTABLE.PARTYTYPE, CUSTTABLE.PARTYID,
                      CUSTTABLE.SHIPCARRIERACCOUNTCODE, CUSTTABLE.PROJPRICEGROUP, CUSTTABLE.GIROTYPEFREETEXTINVOICE, CUSTTABLE.SYNCENTITYID,
                      CUSTTABLE.SYNCVERSION, CUSTTABLE.MEMO, CUSTTABLE.SALESDISTRICTID, CUSTTABLE.CURRENCY, CUSTTABLE.SEGMENTID, CUSTTABLE.SUBSEGMENTID,
                      CUSTTABLE.RFIDITEMTAGGING, CUSTTABLE.RFIDCASETAGGING, CUSTTABLE.RFIDPALLETTAGGING, CUSTTABLE.COMPANYCHAINID, CUSTTABLE.MAINCONTACTID,
                      CUSTTABLE.INTERCOMPANYAUTOCREATEORDERS, CUSTTABLE.IDENTIFICATIONNUMBER, CUSTTABLE.PARTYCOUNTRY, CUSTTABLE.PARTYSTATE,
                      CUSTTABLE.ORGID, CUSTTABLE.SALESGROUP, CUSTTABLE.BLOCKED, CUSTTABLE.ONETIMECUSTOMER, CUSTTABLE.ACCOUNTSTATEMENT,
                      CUSTTABLE.CREDITMAX, CUSTTABLE.MANDATORYCREDITLIMIT, CUSTTABLE.DIMENSION, CUSTTABLE.DIMENSION2_, CUSTTABLE.DIMENSION3_,
                      CUSTTABLE.NAME, CUSTTABLE.VENDACCOUNT, CUSTTABLE.TELEX, CUSTTABLE.PRICEGROUP, CUSTTABLE.MULTILINEDISC, CUSTTABLE.ENDDISC,
                      CUSTTABLE.VATNUM, CUSTTABLE.COUNTRYREGIONID, CUSTTABLE.INVENTLOCATION, CUSTTABLE.DLVTERM, CUSTTABLE.DLVMODE, CUSTTABLE.ADDRESS,
                      CUSTTABLE.MARKUPGROUP, CUSTTABLE.CLEARINGPERIOD, CUSTTABLE.ZIPCODE, CUSTTABLE.STATE, CUSTTABLE.COUNTY, CUSTTABLE.URL,
                      CUSTTABLE.EMAIL, CUSTTABLE.CELLULARPHONE, CUSTTABLE.PHONELOCAL, CUSTTABLE.FREIGHTZONE, CUSTTABLE.PHONE, CUSTTABLE.CREDITRATING,
                      CUSTTABLE.TAXGROUP, CUSTTABLE.STATISTICSGROUP, CUSTTABLE.PAYMMODE, CUSTTABLE.COMMISSIONGROUP, CUSTTABLE.BANKACCOUNT,
                      CUSTTABLE.PAYMSCHED, CUSTTABLE.NAMEALIAS, CUSTTABLE.CONTACTPERSONID, CUSTTABLE.INVOICEADDRESS, CUSTTABLE.TELEFAX,
                      CUSTTABLE.OURACCOUNTNUM, CUSTTABLE.SALESPOOLID, CUSTTABLE.INVOICEACCOUNT, CUSTTABLE.INCLTAX, CUSTTABLE.CUSTITEMGROUPID,
                      CAST(FLOOR(CAST(CUSTTABLE.MODIFIEDDATETIME AS FLOAT)) AS DATETIME) AS MODIFIEDDATETIME,
                      CAST(FLOOR(CAST(CUSTTABLE.CREATEDDATETIME AS FLOAT)) AS DATETIME) AS CREATEDDATETIME, CUSTTABLE.DATAAREAID,
                      CUSTTABLE.NUMBERSEQUENCEGROUP, CUSTTABLE.LANGUAGEID, CUSTTABLE.PAYMDAYID, CUSTTABLE.LINEOFBUSINESSID, CUSTTABLE.RECID,
                      CUSTTABLE.DESTINATIONCODEID, CUSTTABLE.GIROTYPE, CUSTTABLE.SUPPITEMGROUPID, CUSTTABLE.GIROTYPEINTERESTNOTE, CUSTTABLE.CUSTGROUP,
                      CUSTTABLE.TAXLICENSENUM, CUSTTABLE.WEBSALESORDERDISPLAY, CUSTTABLE.PAYMSPEC, CUSTTABLE.BANKCENTRALBANKPURPOSETEXT,
                      CUSTTABLE.BANKCENTRALBANKPURPOSECODE, CUSTTABLE.CITY, CUSTTABLE.STREET, CUSTTABLE.PAGER, CUSTTABLE.SMS, CUSTTABLE.LINEDISC,
                      CUSTTABLE.INTERCOMPANYALLOWINDIRECTCRE80, CUSTTABLE.PBACUSTGROUPID, CUSTTABLE.PACKMATERIALFEELICENSENUM,
                      CUSTTABLE.EINVOICEEANNUM, CUSTTABLE.DLVREASON, CUSTTABLE.FORECASTDMPINCLUDE, CUSTTABLE.GIROTYPECOLLECTIONLETTER,
                      CUSTTABLE.PAYMTERMID, CUSTTABLE.SALESCALENDARID, CUSTTABLE.CUSTCLASSIFICATIONID, CUSTTABLE.INTERCOMPANYDIRECTDELIVERY,
                      CUSTTABLE.SHIPCARRIERACCOUNT, CUSTTABLE.GIROTYPEPROJINVOICE, CUSTTABLE.INVENTSITEID, CUSTTABLE.ORDERENTRYDEADLINEGROUPID,
                      CUSTTABLE.SHIPCARRIERID, CUSTTABLE.SHIPCARRIERFUELSURCHARGE, DIRPARTYTABLE.PREFIX AS DIRPARTYTABLE_PREFIX,
                      DIRPARTYTABLE.PROFESSIONALSUFFIX AS DIRPARTYTABLE_PROFESSIONALSUFFIX, DIRPARTYTABLE.FIRSTNAME AS DIRPARTYTABLE_FIRSTNAME,
                      DIRPARTYTABLE.MIDDLENAME AS DIRPARTYTABLE_MIDDLENAME, DIRPARTYTABLE.LASTNAME AS DIRPARTYTABLE_LASTNAME,
                      DIRPARTYTABLE.PARTYID AS DIRPARTYTABLE_PARTYID, DIRPARTYTABLE.NAMEALIAS AS DIRPARTYTABLE_NAMEALIAS,
                      DIRPARTYTABLE.MEMO AS DIRPARTYTABLE_MEMO, DIRPARTYTABLE.GENERATIONALSUFFIX AS DIRPARTYTABLE_GENERATIONALSUFFIX,
                      DIRPARTYTABLE.NAME AS DIRPARTYTABLE_NAME, DIRPARTYTABLE.SALUTATION AS DIRPARTYTABLE_SALUTATION,
                      CAST(FLOOR(CAST(DIRPARTYTABLE.MODIFIEDDATETIME AS FLOAT)) AS DATETIME) AS DIRPARTYTABLE_MODIFIEDDATETIME,
                      CAST(FLOOR(CAST(DIRPARTYTABLE.CREATEDDATETIME AS FLOAT)) AS DATETIME) AS DIRPARTYTABLE_CREATEDDATETIME,
                      DIRPARTYTABLE.DATAAREAID AS DIRPARTYTABLE_DATAAREAID, DIRPARTYTABLE.RECID AS DIRPARTYTABLE_RECID,
                      DIRPARTYTABLE.PERSONNAMEORDER AS DIRPARTYTABLE_PERSONNAMEORDER, DIRPARTYTABLE.LANGUAGEID AS DIRPARTYTABLE_LANGUAGEID,
                      DIRPARTYTABLE.TYPE AS DIRPARTYTABLE_TYPE, COMMISSIONSALESGROUP.NAME AS COMMISSIONSALESGROUP_NAME,
                      COMMISSIONSALESGROUP.GROUPID AS COMMISSIONSALESGROUP_GROUPID,
                      COMMISSIONSALESGROUP.DATAAREAID AS COMMISSIONSALESGROUP_DATAAREAID, COMMISSIONSALESGROUP.RECID AS COMMISSIONSALESGROUP_RECID,
                      ADDRESSCOUNTRYREGION.COUNTRYREGIONID AS ADDRESSCOUNTRYREGION_COUNTRYREGIONID,
                      ADDRESSCOUNTRYREGION.BANKACCOUNTNUMVALIDATION AS ADDRESSCOUNTRYREGION_BANKACCOUNTNUMVALIDATION,
                      ADDRESSCOUNTRYREGION.CURRENCYCODE AS ADDRESSCOUNTRYREGION_CURRENCYCODE,
                      ADDRESSCOUNTRYREGION.TAXVATNUMFORMATCHECK AS ADDRESSCOUNTRYREGION_TAXVATNUMFORMATCHECK,
                      ADDRESSCOUNTRYREGION.TIMEZONE AS ADDRESSCOUNTRYREGION_TIMEZONE,
                      ADDRESSCOUNTRYREGION.ADDRESSUSEZIPPLUS4 AS ADDRESSCOUNTRYREGION_ADDRESSUSEZIPPLUS4,
                      ADDRESSCOUNTRYREGION.TYPE AS ADDRESSCOUNTRYREGION_TYPE, ADDRESSCOUNTRYREGION.TAXCODE AS ADDRESSCOUNTRYREGION_TAXCODE,
                      ADDRESSCOUNTRYREGION.ISOCODE AS ADDRESSCOUNTRYREGION_ISOCODE,
                      ADDRESSCOUNTRYREGION.ADDRFORMAT AS ADDRESSCOUNTRYREGION_ADDRFORMAT,
                      ADDRESSCOUNTRYREGION.NAME AS ADDRESSCOUNTRYREGION_NAME, ADDRESSCOUNTRYREGION.DATAAREAID AS ADDRESSCOUNTRYREGION_DATAAREAID,
                      ADDRESSCOUNTRYREGION.RECID AS ADDRESSCOUNTRYREGION_RECID,
                      ADDRESSCOUNTRYREGION.INTRASTATCODE AS ADDRESSCOUNTRYREGION_INTRASTATCODE,
                      ADDRESSCOUNTRYREGION.GIROACCOUNTVALIDATIONMETHOD AS ADDRESSCOUNTRYREGION_GIROACCOUNTVALIDATIONMETHOD,
                      CUSTGROUP.CUSTGROUP AS CUSTGROUP_CUSTGROUP, CUSTGROUP.NAME AS CUSTGROUP_NAME,
                      CUSTGROUP.CLEARINGPERIOD AS CUSTGROUP_CLEARINGPERIOD, CUSTGROUP.PAYMTERMID AS CUSTGROUP_PAYMTERMID,
                      CUSTGROUP.TAXGROUPID AS CUSTGROUP_TAXGROUPID, CUSTGROUP.DATAAREAID AS CUSTGROUP_DATAAREAID, CUSTGROUP.RECID AS CUSTGROUP_RECID,
                      CUSTTABLE.CUSTGROUP.PAYMIDTYPE AS CUSTGROUP_PAYMIDTYPE, INVENTSITE.SITEID AS INVENTSITE_SITEID, INVENTSITE.NAME AS INVENTSITE_NAME,
                      INVENTSITE.DIMENSION AS INVENTSITE_DIMENSION, INVENTSITE.DIMENSION2_ AS INVENTSITE_DIMENSION2_,
                      INVENTSITE.DIMENSION3_ AS INVENTSITE_DIMENSION3_, INVENTSITE.DATAAREAID AS INVENTSITE_DATAAREAID, INVENTSITE.RECID AS INVENTSITE_RECID,
                      INVENTSITE.TIMEZONE AS INVENTSITE_TIMEZONE, INVENTSITE.ORDERENTRYDEADLINEGROUPID AS INVENTSITE_ORDERENTRYDEADLINEGROUPID,
                      SMMBUSRELCHAINGROUP.DATAAREAID AS SMMBUSRELCHAINGROUP_DATAAREAID, SMMBUSRELCHAINGROUP.RECID AS SMMBUSRELCHAINGROUP_RECID,
                      SMMBUSRELCHAINGROUP.CHAINID AS SMMBUSRELCHAINGROUP_CHAINID,
                      SMMBUSRELCHAINGROUP.DESCRIPTION AS SMMBUSRELCHAINGROUP_DESCRIPTION,
                      SMMBUSRELSEGMENTGROUP.DATAAREAID AS SMMBUSRELSEGMENTGROUP_DATAAREAID,
                      SMMBUSRELSEGMENTGROUP.RECID AS SMMBUSRELSEGMENTGROUP_RECID,
                      SMMBUSRELSEGMENTGROUP.SEGMENTID AS SMMBUSRELSEGMENTGROUP_SEGMENTID,
                      SMMBUSRELSEGMENTGROUP.DESCRIPTION AS SMMBUSRELSEGMENTGROUP_DESCRIPTION,
                      LINEOFBUSINESS.LINEOFBUSINESSID AS LINEOFBUSINESS_LINEOFBUSINESSID, LINEOFBUSINESS.DESCRIPTION AS LINEOFBUSINESS_DESCRIPTION,
                      LINEOFBUSINESS.DATAAREAID AS LINEOFBUSINESS_DATAAREAID, LINEOFBUSINESS.RECID AS LINEOFBUSINESS_RECID,
                      DESTINATIONCODE.DESTINATIONCODEID AS DESTINATIONCODE_DESTINATIONCODEID, DESTINATIONCODE.DESCRIPTION AS DESTINATIONCODE_DESCRIPTION,
                      DESTINATIONCODE.DATAAREAID AS DESTINATIONCODE_DATAAREAID, DESTINATIONCODE.RECID AS DESTINATIONCODE_RECID
FROM        CUSTTABLE LEFT OUTER JOIN
                      DIRPARTYTABLE ON DIRPARTYTABLE.DATAAREAID = CUSTTABLE.DATAAREAID AND DIRPARTYTABLE.PARTYID = CUSTTABLE.PARTYID LEFT OUTER JOIN
                      COMMISSIONSALESGROUP ON COMMISSIONSALESGROUP.DATAAREAID = CUSTTABLE.DATAAREAID AND
                      COMMISSIONSALESGROUP.GROUPID = CUSTTABLE.SALESGROUP LEFT OUTER JOIN
                      ADDRESSCOUNTRYREGION ON ADDRESSCOUNTRYREGION.DATAAREAID = CUSTTABLE.DATAAREAID AND
                      ADDRESSCOUNTRYREGION.COUNTRYREGIONID = CUSTTABLE.COUNTRYREGIONID LEFT OUTER JOIN
                      CUSTGROUP ON CUSTGROUP.DATAAREAID = CUSTTABLE.DATAAREAID AND CUSTGROUP.CUSTGROUP = CUSTTABLE.CUSTGROUP LEFT OUTER JOIN
                      INVENTSITE ON INVENTSITE.DATAAREAID = CUSTTABLE.DATAAREAID AND INVENTSITE.SITEID = CUSTTABLE.INVENTSITEID LEFT OUTER JOIN
                      SMMBUSRELCHAINGROUP ON SMMBUSRELCHAINGROUP.DATAAREAID = CUSTTABLE.DATAAREAID AND
                      SMMBUSRELCHAINGROUP.CHAINID = CUSTTABLE.COMPANYCHAINID LEFT OUTER JOIN
                      SMMBUSRELSEGMENTGROUP ON SMMBUSRELSEGMENTGROUP.DATAAREAID = CUSTTABLE.DATAAREAID AND
                      SMMBUSRELSEGMENTGROUP.SEGMENTID = CUSTTABLE.SEGMENTID LEFT OUTER JOIN
                      LINEOFBUSINESS ON LINEOFBUSINESS.DATAAREAID = CUSTTABLE.DATAAREAID AND
                      LINEOFBUSINESS.LINEOFBUSINESSID = CUSTTABLE.LINEOFBUSINESSID LEFT OUTER JOIN
                      DESTINATIONCODE ON DESTINATIONCODE.DATAAREAID = CUSTTABLE.DATAAREAID AND
                      DESTINATIONCODE.DESTINATIONCODEID = CUSTTABLE.DESTINATIONCODEID


Håber nogen kan hjælpe.

På forhånd tak.
Avatar billede hrc Mester
15. april 2010 - 10:30 #1
Øhh. Kunne du ikke lave scriptet lidt større..:-)

Det er svært at se når man ikke ved hvordan tabellerne ser ud (dvs. man ved ikke hvilke typer der bruges)

Har du prøvet at undlade nogle af felterne?
Avatar billede scarface335 Nybegynder
15. april 2010 - 10:34 #2
ja, jeg har prøvet at udeladde nogen af felter, der hentes data fra i alt 5 tabeller, der, det er en query fra en default cube til Dynamics AX, skal bre tilpasse den så den virker uden at fjerne for meget.
Avatar billede hrc Mester
15. april 2010 - 10:44 #3
Umiddelbart er det et almindeligt men stort select script. Det burde virke. Spørger måske lidt dumt da jeg ved intet om cubes, men er det den der kommer med fejlen?

Du kan vel køre scriptet i management studio eller lignende, ikke?
Avatar billede scarface335 Nybegynder
15. april 2010 - 10:51 #4
Jeg har lige prøvet at afvikle scriptet i Sql server i managementstudio, og får her fejlen, Cannot call methods on nvarchar.
Avatar billede scarface335 Nybegynder
15. april 2010 - 11:20 #5
Har begrænset den lidt men stadig samme fejl.

SELECT    CUSTTABLE.ACCOUNTNUM, CUSTTABLE.CASHDISC, CUSTTABLE.SHIPCARRIERBLINDSHIPMENT, CUSTTABLE.PARTYTYPE, CUSTTABLE.PARTYID,
                      CUSTTABLE.SHIPCARRIERACCOUNTCODE, CUSTTABLE.PROJPRICEGROUP, CUSTTABLE.GIROTYPEFREETEXTINVOICE, CUSTTABLE.SYNCENTITYID,
                      CUSTTABLE.SYNCVERSION, CUSTTABLE.MEMO, CUSTTABLE.SALESDISTRICTID, CUSTTABLE.CURRENCY, CUSTTABLE.SEGMENTID, CUSTTABLE.SUBSEGMENTID,
                      CUSTTABLE.RFIDITEMTAGGING, CUSTTABLE.RFIDCASETAGGING, CUSTTABLE.RFIDPALLETTAGGING, CUSTTABLE.COMPANYCHAINID, CUSTTABLE.MAINCONTACTID,
                      CUSTTABLE.INTERCOMPANYAUTOCREATEORDERS, CUSTTABLE.IDENTIFICATIONNUMBER, CUSTTABLE.PARTYCOUNTRY, CUSTTABLE.PARTYSTATE,
                      CUSTTABLE.SALESGROUP, CUSTTABLE.BLOCKED, CUSTTABLE.ONETIMECUSTOMER, CUSTTABLE.ACCOUNTSTATEMENT,
                      CUSTTABLE.CREDITMAX, CUSTTABLE.MANDATORYCREDITLIMIT, CUSTTABLE.DIMENSION, CUSTTABLE.DIMENSION2_, CUSTTABLE.DIMENSION3_,
                      CUSTTABLE.NAME, CUSTTABLE.VENDACCOUNT, CUSTTABLE.TELEX, CUSTTABLE.PRICEGROUP, CUSTTABLE.MULTILINEDISC, CUSTTABLE.ENDDISC,
                      CUSTTABLE.VATNUM, CUSTTABLE.COUNTRYREGIONID, CUSTTABLE.INVENTLOCATION, CUSTTABLE.DLVTERM, CUSTTABLE.DLVMODE, CUSTTABLE.ADDRESS,
                      CUSTTABLE.MARKUPGROUP, CUSTTABLE.CLEARINGPERIOD, CUSTTABLE.ZIPCODE, CUSTTABLE.STATE, CUSTTABLE.COUNTY, CUSTTABLE.URL,
                      CUSTTABLE.EMAIL, CUSTTABLE.CELLULARPHONE, CUSTTABLE.PHONELOCAL, CUSTTABLE.FREIGHTZONE, CUSTTABLE.PHONE, CUSTTABLE.CREDITRATING,
                      CUSTTABLE.TAXGROUP, CUSTTABLE.STATISTICSGROUP, CUSTTABLE.PAYMMODE, CUSTTABLE.COMMISSIONGROUP, CUSTTABLE.BANKACCOUNT,
                      CUSTTABLE.PAYMSCHED, CUSTTABLE.NAMEALIAS, CUSTTABLE.CONTACTPERSONID, CUSTTABLE.INVOICEADDRESS, CUSTTABLE.TELEFAX,
                      CUSTTABLE.OURACCOUNTNUM, CUSTTABLE.SALESPOOLID, CUSTTABLE.INVOICEACCOUNT, CUSTTABLE.INCLTAX, CUSTTABLE.CUSTITEMGROUPID,
                      CUSTTABLE.DATAAREAID,
                      CUSTTABLE.NUMBERSEQUENCEGROUP, CUSTTABLE.LANGUAGEID, CUSTTABLE.PAYMDAYID, CUSTTABLE.LINEOFBUSINESSID, CUSTTABLE.RECID,
                      CUSTTABLE.DESTINATIONCODEID, CUSTTABLE.GIROTYPE, CUSTTABLE.SUPPITEMGROUPID, CUSTTABLE.GIROTYPEINTERESTNOTE, CUSTTABLE.CUSTGROUP,
                      CUSTTABLE.TAXLICENSENUM, CUSTTABLE.WEBSALESORDERDISPLAY, CUSTTABLE.PAYMSPEC, CUSTTABLE.BANKCENTRALBANKPURPOSETEXT,
                      CUSTTABLE.BANKCENTRALBANKPURPOSECODE, CUSTTABLE.CITY, CUSTTABLE.STREET, CUSTTABLE.PAGER, CUSTTABLE.SMS, CUSTTABLE.LINEDISC,
                      CUSTTABLE.INTERCOMPANYALLOWINDIRECTCRE80, CUSTTABLE.PBACUSTGROUPID, CUSTTABLE.PACKMATERIALFEELICENSENUM,
                      CUSTTABLE.EINVOICEEANNUM, CUSTTABLE.DLVREASON, CUSTTABLE.FORECASTDMPINCLUDE, CUSTTABLE.GIROTYPECOLLECTIONLETTER,
                      CUSTTABLE.PAYMTERMID, CUSTTABLE.SALESCALENDARID, CUSTTABLE.CUSTCLASSIFICATIONID, CUSTTABLE.INTERCOMPANYDIRECTDELIVERY,
                      CUSTTABLE.SHIPCARRIERACCOUNT, CUSTTABLE.GIROTYPEPROJINVOICE, CUSTTABLE.INVENTSITEID, CUSTTABLE.ORDERENTRYDEADLINEGROUPID,
                      CUSTTABLE.SHIPCARRIERID, CUSTTABLE.SHIPCARRIERFUELSURCHARGE, DIRPARTYTABLE.PREFIX,
                      DIRPARTYTABLE.PROFESSIONALSUFFIX , DIRPARTYTABLE.FIRSTNAME ,
                      DIRPARTYTABLE.MIDDLENAME, DIRPARTYTABLE.LASTNAME,
                      DIRPARTYTABLE.PARTYID, DIRPARTYTABLE.NAMEALIAS ,
                      DIRPARTYTABLE.MEMO , DIRPARTYTABLE.GENERATIONALSUFFIX ,
                      DIRPARTYTABLE.NAME, DIRPARTYTABLE.SALUTATION,
                      DIRPARTYTABLE.DATAAREAID, DIRPARTYTABLE.RECID,
                      DIRPARTYTABLE.PERSONNAMEORDER, DIRPARTYTABLE.LANGUAGEID ,
                      DIRPARTYTABLE.TYPE , ADDRESSCOUNTRYREGION.COUNTRYREGIONID ,
                      ADDRESSCOUNTRYREGION.BANKACCOUNTNUMVALIDATION,
                      ADDRESSCOUNTRYREGION.CURRENCYCODE,
                      ADDRESSCOUNTRYREGION.TAXVATNUMFORMATCHECK,
                      ADDRESSCOUNTRYREGION.TIMEZONE ,
                      ADDRESSCOUNTRYREGION.ADDRESSUSEZIPPLUS4 ,
                      ADDRESSCOUNTRYREGION.TYPE , ADDRESSCOUNTRYREGION.TAXCODE,
                      ADDRESSCOUNTRYREGION.ISOCODE ,
                      ADDRESSCOUNTRYREGION.ADDRFORMAT ,
                      ADDRESSCOUNTRYREGION.NAME , ADDRESSCOUNTRYREGION.DATAAREAID ,
                      ADDRESSCOUNTRYREGION.RECID ,
                      ADDRESSCOUNTRYREGION.INTRASTATCODE ,
                      ADDRESSCOUNTRYREGION.GIROACCOUNTVALIDATIONMETHOD ,
                      CUSTGROUP.CUSTGROUP , CUSTGROUP.NAME,
                      CUSTGROUP.CLEARINGPERIOD , CUSTGROUP.PAYMTERMID ,
                      CUSTGROUP.TAXGROUPID , CUSTGROUP.DATAAREAID , CUSTGROUP.RECID ,
                      CUSTTABLE.CUSTGROUP.PAYMIDTYPE
FROM        CUSTTABLE LEFT OUTER JOIN
                      DIRPARTYTABLE ON DIRPARTYTABLE.DATAAREAID = CUSTTABLE.DATAAREAID AND DIRPARTYTABLE.PARTYID = CUSTTABLE.PARTYID LEFT OUTER JOIN
                      ADDRESSCOUNTRYREGION ON ADDRESSCOUNTRYREGION.DATAAREAID = CUSTTABLE.DATAAREAID AND
                      ADDRESSCOUNTRYREGION.COUNTRYREGIONID = CUSTTABLE.COUNTRYREGIONID LEFT OUTER JOIN
                      CUSTGROUP ON CUSTGROUP.DATAAREAID = CUSTTABLE.DATAAREAID AND CUSTGROUP.CUSTGROUP = CUSTTABLE.CUSTGROUP
Avatar billede scarface335 Nybegynder
15. april 2010 - 11:39 #6
Så fandt jeg fejlen, det var linjen(10. nederest i ovenstående)
CUSTTABLE.CUSTGROUP.PAYMIDTYPE der gav fejlen :), men tak for sparingen, smid et svar så får du nogle point.
Avatar billede janus_007 Nybegynder
15. april 2010 - 12:23 #7
Godt du fandt fejlen.

Jeg kan dog lige tilføje at det er stor uskik at uppercase alt på den måde, det gør koden sværtlæselig.
Avatar billede scarface335 Nybegynder
15. april 2010 - 12:29 #8
Det er ikke kode jeg har skrevet men det er generet af scripts eller andet, jeg skulle bare tilpasse så det virkede. smid et svar hvis du vil have point ellers tager jeg dem selv. Og tak for hjælpen. :)
Avatar billede janus_007 Nybegynder
15. april 2010 - 14:32 #9
Statementet er vel genereret på baggrund af nogle kolonner som er defineret, og de kolonner er vel uppercased :-|
Avatar billede hrc Mester
15. april 2010 - 14:59 #10
Jeg lavede scriptet til dette (ultraedit kan bare editere!)

select    custtable.accountnum,
          custtable.cashdisc,
          custtable.shipcarrierblindshipment,
          custtable.partytype,
          custtable.partyid,
          custtable.shipcarrieraccountcode,
          custtable.projpricegroup,
          custtable.girotypefreetextinvoice,
          custtable.syncentityid,
          custtable.syncversion,
          custtable.memo,
          custtable.salesdistrictid,
          custtable.currency,
          custtable.segmentid,
          custtable.subsegmentid,
          custtable.rfiditemtagging,
          custtable.rfidcasetagging,
          custtable.rfidpallettagging,
          custtable.companychainid,
          custtable.maincontactid,
          custtable.intercompanyautocreateorders,
          custtable.identificationnumber,
          custtable.partycountry,
          custtable.partystate,
          custtable.orgid,
          custtable.salesgroup,
          custtable.blocked,
          custtable.onetimecustomer,
          custtable.accountstatement,
          custtable.creditmax,
          custtable.mandatorycreditlimit,
          custtable.dimension,
          custtable.dimension2_,
          custtable.dimension3_,
          custtable.name,
          custtable.vendaccount,
          custtable.telex,
          custtable.pricegroup,
          custtable.multilinedisc,
          custtable.enddisc,
          custtable.vatnum,
          custtable.countryregionid,
          custtable.inventlocation,
          custtable.dlvterm,
          custtable.dlvmode,
          custtable.address,
          custtable.markupgroup,
          custtable.clearingperiod,
          custtable.zipcode,
          custtable.state,
          custtable.county,
          custtable.url,
          custtable.email,
          custtable.cellularphone,
          custtable.phonelocal,
          custtable.freightzone,
          custtable.phone,
          custtable.creditrating,
          custtable.taxgroup,
          custtable.statisticsgroup,
          custtable.paymmode,
          custtable.commissiongroup,
          custtable.bankaccount,
          custtable.paymsched,
          custtable.namealias,
          custtable.contactpersonid,
          custtable.invoiceaddress,
          custtable.telefax,
          custtable.ouraccountnum,
          custtable.salespoolid,
          custtable.invoiceaccount,
          custtable.incltax,
          custtable.custitemgroupid,
          cast(floor(cast(custtable.modifieddatetime as float)) as datetime) as modifieddatetime,
          cast(floor(cast(custtable.createddatetime as float)) as datetime) as createddatetime,
          custtable.dataareaid,
          custtable.numbersequencegroup,
          custtable.languageid,
          custtable.paymdayid,
          custtable.lineofbusinessid,
          custtable.recid,
          custtable.destinationcodeid,
          custtable.girotype,
          custtable.suppitemgroupid,
          custtable.girotypeinterestnote,
          custtable.custgroup,
          custtable.taxlicensenum,
          custtable.websalesorderdisplay,
          custtable.paymspec,
          custtable.bankcentralbankpurposetext,
          custtable.bankcentralbankpurposecode,
          custtable.city,
          custtable.street,
          custtable.pager,
          custtable.sms,
          custtable.linedisc,
          custtable.intercompanyallowindirectcre80,
          custtable.pbacustgroupid,
          custtable.packmaterialfeelicensenum,
          custtable.einvoiceeannum,
          custtable.dlvreason,
          custtable.forecastdmpinclude,
          custtable.girotypecollectionletter,
          custtable.paymtermid,
          custtable.salescalendarid,
          custtable.custclassificationid,
          custtable.intercompanydirectdelivery,
          custtable.shipcarrieraccount,
          custtable.girotypeprojinvoice,
          custtable.inventsiteid,
          custtable.orderentrydeadlinegroupid,
          custtable.shipcarrierid,
          custtable.shipcarrierfuelsurcharge,
          dirpartytable.prefix as dirpartytable_prefix,
          dirpartytable.professionalsuffix as dirpartytable_professionalsuffix,
          dirpartytable.firstname as dirpartytable_firstname,
          dirpartytable.middlename as dirpartytable_middlename,
          dirpartytable.lastname as dirpartytable_lastname,
          dirpartytable.partyid as dirpartytable_partyid,
          dirpartytable.namealias as dirpartytable_namealias,
          dirpartytable.memo as dirpartytable_memo,
          dirpartytable.generationalsuffix as dirpartytable_generationalsuffix,
          dirpartytable.name as dirpartytable_name,
          dirpartytable.salutation as dirpartytable_salutation,
          cast(floor(cast(dirpartytable.modifieddatetime as float)) as datetime) as
            dirpartytable_modifieddatetime,
          cast(floor(cast(dirpartytable.createddatetime as float)) as datetime) as
            dirpartytable_createddatetime,
          dirpartytable.dataareaid as dirpartytable_dataareaid,
          dirpartytable.recid as dirpartytable_recid,
          dirpartytable.personnameorder as dirpartytable_personnameorder,
          dirpartytable.languageid as dirpartytable_languageid,
          dirpartytable.type as dirpartytable_type,
          commissionsalesgroup.name as commissionsalesgroup_name,
          commissionsalesgroup.groupid as commissionsalesgroup_groupid,
          commissionsalesgroup.dataareaid as commissionsalesgroup_dataareaid,
          commissionsalesgroup.recid as commissionsalesgroup_recid,
          addresscountryregion.countryregionid as
            addresscountryregion_countryregionid,
          addresscountryregion.bankaccountnumvalidation as
            addresscountryregion_bankaccountnumvalidation,
          addresscountryregion.currencycode as addresscountryregion_currencycode,
          addresscountryregion.taxvatnumformatcheck as
            addresscountryregion_taxvatnumformatcheck,
          addresscountryregion.timezone as addresscountryregion_timezone,
          addresscountryregion.addressusezipplus4 as addresscountryregion_addressusezipplus4,
          addresscountryregion.type as addresscountryregion_type,
          addresscountryregion.taxcode as addresscountryregion_taxcode,
          addresscountryregion.isocode as addresscountryregion_isocode,
          addresscountryregion.addrformat as addresscountryregion_addrformat,
          addresscountryregion.name as addresscountryregion_name,
          addresscountryregion.dataareaid as addresscountryregion_dataareaid,
          addresscountryregion.recid as addresscountryregion_recid,
          addresscountryregion.intrastatcode as addresscountryregion_intrastatcode,
          addresscountryregion.giroaccountvalidationmethod as
            addresscountryregion_giroaccountvalidationmethod,
          custgroup.custgroup as custgroup_custgroup,
          custgroup.name as custgroup_name,
          custgroup.clearingperiod as custgroup_clearingperiod,
          custgroup.paymtermid as custgroup_paymtermid,
          custgroup.taxgroupid as custgroup_taxgroupid,
          custgroup.dataareaid as custgroup_dataareaid,
          custgroup.recid as custgroup_recid,
          custtable.custgroup.paymidtype as custgroup_paymidtype,
          inventsite.siteid as inventsite_siteid,
          inventsite.name as inventsite_name,
          inventsite.dimension as inventsite_dimension,
          inventsite.dimension2_ as inventsite_dimension2_,
          inventsite.dimension3_ as inventsite_dimension3_,
          inventsite.dataareaid as inventsite_dataareaid,
          inventsite.recid as inventsite_recid,
          inventsite.timezone as inventsite_timezone,
          inventsite.orderentrydeadlinegroupid as inventsite_orderentrydeadlinegroupid,
          smmbusrelchaingroup.dataareaid as smmbusrelchaingroup_dataareaid,
          smmbusrelchaingroup.recid as smmbusrelchaingroup_recid,
          smmbusrelchaingroup.chainid as smmbusrelchaingroup_chainid,
          smmbusrelchaingroup.description as smmbusrelchaingroup_description,
          smmbusrelsegmentgroup.dataareaid as smmbusrelsegmentgroup_dataareaid,
          smmbusrelsegmentgroup.recid as smmbusrelsegmentgroup_recid,
          smmbusrelsegmentgroup.segmentid as smmbusrelsegmentgroup_segmentid,
          smmbusrelsegmentgroup.description as smmbusrelsegmentgroup_description,
          lineofbusiness.lineofbusinessid as lineofbusiness_lineofbusinessid,
          lineofbusiness.description as lineofbusiness_description,
          lineofbusiness.dataareaid as lineofbusiness_dataareaid,
          lineofbusiness.recid as lineofbusiness_recid,
          destinationcode.destinationcodeid as destinationcode_destinationcodeid,
          destinationcode.description as destinationcode_description,
          destinationcode.dataareaid as destinationcode_dataareaid,
          destinationcode.recid as destinationcode_recid
from custtable
left outer join dirpartytable        on dirpartytable.dataareaid = custtable.dataareaid
                                    and dirpartytable.partyid = custtable.partyid
left outer join commissionsalesgroup  on commissionsalesgroup.dataareaid = custtable.dataareaid
                                    and commissionsalesgroup.groupid = custtable.salesgroup
left outer join addresscountryregion  on addresscountryregion.dataareaid = custtable.dataareaid
                                    and addresscountryregion.countryregionid = custtable.countryregionid
left outer join custgroup            on custgroup.dataareaid = custtable.dataareaid
                                    and custgroup.custgroup = custtable.custgroup
left outer join inventsite            on inventsite.dataareaid = custtable.dataareaid
                                    and inventsite.siteid = custtable.inventsiteid
left outer join smmbusrelchaingroup  on smmbusrelchaingroup.dataareaid = custtable.dataareaid
                                    and smmbusrelchaingroup.chainid = custtable.companychainid
left outer join smmbusrelsegmentgroup on smmbusrelsegmentgroup.dataareaid=custtable.dataareaid
                                    and smmbusrelsegmentgroup.segmentid = custtable.segmentid
left outer join lineofbusiness        on lineofbusiness.dataareaid = custtable.dataareaid
                                    and lineofbusiness.lineofbusinessid = custtable.lineofbusinessid
left outer join destinationcode      on destinationcode.dataareaid = custtable.dataareaid
                                    and destinationcode.destinationcodeid = custtable.destinationcodeid
Avatar billede scarface335 Nybegynder
16. april 2010 - 08:45 #11
Som jeg skrev tidliger Janus, var det en Dynamics AX database, her opretter du ikke selv tabellerne men det gør programmet automatisk når du synkronisere datadictionary :)
Avatar billede scarface335 Nybegynder
30. juni 2010 - 07:43 #12
lukket
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester