Query Error

I’m writing a BAQ (Had Query, my fault) and everytime i try to add the customer table it gives me a Bad SQL error. I go and check th even viewer and find:

System.Data.SqlClient.SqlException (0x80131904): Invalid column name ‘null’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Ice.Blaq.Execution.QueryExecutor.ExecuteAndFillDataSetInternal(IDbConnection dbconn, QueryInfo queryInfo, DataSet resultDataset, Action2 perfLogger) in c:\_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 116 at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass2_0.<ExecuteAndFillDataSet>b__0(IDbConnection dbconn) in c:\_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 35 at Ice.Blaq.Execution.QueryExecutionHelper.DoJobWithObject[TObj](Func2 theJob) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 192
ClientConnectionId:4a4cae34-b6d7-498d-99e0-c7a31d5f209d
Error Number:207,State:1,Class:16

can you please post the query?

select
[OrderHed].[Company] as [OrderHed_Company],
[ShipHead].[PackNum] as [ShipHead_PackNum],
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
[OrderHed].[ShipViaCode] as [OrderHed_ShipViaCode],
[ShipHead].[ShipPerson] as [ShipHead_ShipPerson],
[OrderHed].[EntryPerson] as [OrderHed_EntryPerson],
[ShipHead].[ShipLog] as [ShipHead_ShipLog],
[ShipHead].[LabelComment] as [ShipHead_LabelComment],
[OrderDtl].[ShipComment] as [OrderDtl_ShipComment],
[ShipHead].[Invoiced] as [ShipHead_Invoiced],
[OrderHed].[ShipToNum] as [OrderHed_ShipToNum],
[ShipHead].[ReadyToInvoice] as [ShipHead_ReadyToInvoice],
[OrderHed].[CustNum] as [OrderHed_CustNum],
[OrderHed].[Plant] as [OrderHed_Plant],
[ShipHead].[TrackingNumber] as [ShipHead_TrackingNumber],
(OrderHed.OrderNum) as [Calculated_LegalNumber],
[OrderHed].[VoidOrder] as [OrderHed_VoidOrder],
(‘false’) as [Calculated_ExternalDeliveryNote],
[ShipHead].[ExternalID] as [ShipHead_ExternalID],
(‘false’) as [Calculated_ICReceived],
(’:’) as [Calculated_XRefPackNum],
[OrderHed].[BTCustNum] as [OrderHed_BTCustNum],
[OrderHed].[BTConNum] as [OrderHed_BTConNum],
[ShipHead].[ShipStatus] as [ShipHead_ShipStatus],
[ShipHead].[ShipGroup] as [ShipHead_ShipGroup],
[ShipHead].[PkgCode] as [ShipHead_PkgCode],
[ShipHead].[PkgClass] as [ShipHead_PkgClass],
[ShipHead].[Weight] as [ShipHead_Weight],
[OrderHed].[ResDelivery] as [OrderHed_ResDelivery],
[OrderHed].[SatDelivery] as [OrderHed_SatDelivery],
[OrderHed].[SatPickup] as [OrderHed_SatPickup],
[OrderRel].[VerbalConf] as [OrderRel_VerbalConf],
[OrderRel].[Hazmat] as [OrderRel_Hazmat],
[OrderRel].[DocOnly] as [OrderRel_DocOnly],
[OrderRel].[RefNotes] as [OrderRel_RefNotes],
[OrderRel].[ApplyChrg] as [OrderRel_ApplyChrg],
[OrderRel].[ChrgAmount] as [OrderRel_ChrgAmount],
[OrderRel].[COD] as [OrderRel_COD],
[OrderRel].[CODFreight] as [OrderRel_CODFreight],
[OrderRel].[CODCheck] as [OrderRel_CODCheck],
[OrderRel].[CODAmount] as [OrderRel_CODAmount],
[OrderRel].[GroundType] as [OrderRel_GroundType],
[OrderRel].[NotifyFlag] as [OrderRel_NotifyFlag],
[OrderRel].[NotifyEMail] as [OrderRel_NotifyEMail],
[OrderRel].[DeclaredIns] as [OrderRel_DeclaredIns],
[OrderRel].[DeclaredAmt] as [OrderRel_DeclaredAmt],
[ShipHead].[MFTransNum] as [ShipHead_MFTransNum],
[ShipHead].[MFCallTag] as [ShipHead_MFCallTag],
[ShipHead].[MFPickupNum] as [ShipHead_MFPickupNum],
[ShipHead].[MFDiscFreight] as [ShipHead_MFDiscFreight],
[ShipHead].[MFTemplate] as [ShipHead_MFTemplate],
[ShipHead].[MFUse3B] as [ShipHead_MFUse3B],
[ShipHead].[MF3BAccount] as [ShipHead_MF3BAccount],
[ShipHead].[MFDimWeight] as [ShipHead_MFDimWeight],
[ShipHead].[MFZone] as [ShipHead_MFZone],
[ShipHead].[MFFreightAmt] as [ShipHead_MFFreightAmt],
[ShipHead].[MFOtherAmt] as [ShipHead_MFOtherAmt],
[ShipHead].[MFOversized] as [ShipHead_MFOversized],
[OrderRel].[ServSatDelivery] as [OrderRel_ServSatDelivery],
[OrderRel].[ServSatPickup] as [OrderRel_ServSatPickup],
[OrderRel].[ServSignature] as [OrderRel_ServSignature],
[OrderRel].[ServAlert] as [OrderRel_ServAlert],
[OrderRel].[ServPOD] as [OrderRel_ServPOD],
[OrderRel].[ServAOD] as [OrderRel_ServAOD],
[OrderRel].[ServHomeDel] as [OrderRel_ServHomeDel],
[OrderRel].[DeliveryType] as [OrderRel_DeliveryType],
[OrderRel].[ServDeliveryDate] as [OrderRel_ServDeliveryDate],
[OrderRel].[ServPhone] as [OrderRel_ServPhone],
[OrderRel].[ServInstruct] as [OrderRel_ServInstruct],
[OrderRel].[ServRelease] as [OrderRel_ServRelease],
[OrderRel].[ServAuthNum] as [OrderRel_ServAuthNum],
[OrderRel].[ServRef1] as [OrderRel_ServRef1],
[OrderRel].[ServRef2] as [OrderRel_ServRef2],
[OrderRel].[ServRef3] as [OrderRel_ServRef3],
[OrderRel].[ServRef4] as [OrderRel_ServRef4],
[OrderRel].[ServRef5] as [OrderRel_ServRef5],
[BOLDetail].[BOLNum] as [BOLDetail_BOLNum],
[BOLDetail].[BOLLine] as [BOLDetail_BOLLine],
[ShipHead].[CommercialInvoice] as [ShipHead_CommercialInvoice],
[ShipHead].[ShipExprtDeclartn] as [ShipHead_ShipExprtDeclartn],
[ShipHead].[CertOfOrigin] as [ShipHead_CertOfOrigin],
[ShipHead].[LetterOfInstr] as [ShipHead_LetterOfInstr],
[ShipHead].[HazardousShipment] as [ShipHead_HazardousShipment],
[ShipHead].[IntrntlShip] as [ShipHead_IntrntlShip],
[OrderHed].[PayFlag] as [OrderHed_PayFlag],
[OrderHed].[PayAccount] as [OrderHed_PayAccount],
[OrderHed].[PayBTAddress1] as [OrderHed_PayBTAddress1],
[OrderHed].[PayBTAddress2] as [OrderHed_PayBTAddress2],
[OrderHed].[PayBTCity] as [OrderHed_PayBTCity],
[OrderHed].[PayBTState] as [OrderHed_PayBTState],
[OrderHed].[PayBTZip] as [OrderHed_PayBTZip],
[OrderHed].[PayBTCountry] as [OrderHed_PayBTCountry],
[OrderHed].[FFAddress1] as [OrderHed_FFAddress1],
[OrderHed].[FFAddress2] as [OrderHed_FFAddress2],
[OrderHed].[FFCity] as [OrderHed_FFCity],
[OrderHed].[FFState] as [OrderHed_FFState],
[OrderHed].[FFZip] as [OrderHed_FFZip],
[OrderHed].[FFCountry] as [OrderHed_FFCountry],
[OrderHed].[FFContact] as [OrderHed_FFContact],
[OrderHed].[FFCompName] as [OrderHed_FFCompName],
[OrderHed].[FFPhoneNum] as [OrderHed_FFPhoneNum],
[ShipHead].[ChangedBy] as [ShipHead_ChangedBy],
[ShipHead].[ChangeDate] as [ShipHead_ChangeDate],
[ShipHead].[ChangeTime] as [ShipHead_ChangeTime],
[OrderHed].[FFID] as [OrderHed_FFID],
[OrderHed].[IndividualPackIDs] as [OrderHed_IndividualPackIDs],
[OrderHed].[FFAddress3] as [OrderHed_FFAddress3],
[OrderHed].[DeliveryConf] as [OrderHed_DeliveryConf],
[OrderHed].[AddlHdlgFlag] as [OrderHed_AddlHdlgFlag],
[OrderHed].[NonStdPkg] as [OrderHed_NonStdPkg],
[OrderHed].[FFCountryNum] as [OrderHed_FFCountryNum],
[OrderHed].[PayBTAddress3] as [OrderHed_PayBTAddress3],
[OrderHed].[PayBTPhone] as [OrderHed_PayBTPhone],
[ShipHead].[WayBillNbr] as [ShipHead_WayBillNbr],
(OrderHed.ShipViaCode) as [Calculated_FreightedShipViaCode],
[OrderHed].[UPSQuantumView] as [OrderHed_UPSQuantumView],
[OrderHed].[UPSQVShipFromName] as [OrderHed_UPSQVShipFromName],
[OrderHed].[UPSQVMemo] as [OrderHed_UPSQVMemo],
[ShipHead].[PkgLength] as [ShipHead_PkgLength],
[ShipHead].[PkgWidth] as [ShipHead_PkgWidth],
[ShipHead].[PkgHeight] as [ShipHead_PkgHeight],
[OrderHed].[EDIReady] as [OrderHed_EDIReady],
[ShipHead].[PhantomPack] as [ShipHead_PhantomPack],
[ShipHead].[ReplicatedFrom] as [ShipHead_ReplicatedFrom],
[ShipHead].[ReplicatedStat] as [ShipHead_ReplicatedStat],
[ShipHead].[PkgSizeUOM] as [ShipHead_PkgSizeUOM],
[ShipHead].[WeightUOM] as [ShipHead_WeightUOM],
[OrderHed].[UseOTS] as [OrderHed_UseOTS],
[OrderHed].[TranDocTypeID] as [OrderHed_TranDocTypeID],
[ShipHead].[DocumentPrinted] as [ShipHead_DocumentPrinted],
(orderhed.ordernum) as [Calculated_OTSOrderNum],
[ShipHead].[TaxCalculated] as [ShipHead_TaxCalculated],
[ShipHead].[TaxCalcDate] as [ShipHead_TaxCalcDate],
[OrderHed].[CurrencyCode] as [OrderHed_CurrencyCode],
[OrderHed].[Rounding] as [OrderHed_Rounding],
[OrderHed].[Rpt1Rounding] as [OrderHed_Rpt1Rounding],
[OrderHed].[Rpt2Rounding] as [OrderHed_Rpt2Rounding],
[OrderHed].[Rpt3Rounding] as [OrderHed_Rpt3Rounding],
[OrderHed].[OrderAmt] as [OrderHed_OrderAmt],
[OrderHed].[DocOrderAmt] as [OrderHed_DocOrderAmt],
[OrderHed].[Rpt1OrderAmt] as [OrderHed_Rpt1OrderAmt],
[OrderHed].[Rpt2OrderAmt] as [OrderHed_Rpt2OrderAmt],
[OrderHed].[Rpt3OrderAmt] as [OrderHed_Rpt3OrderAmt],
[OrderHed].[TaxRegionCode] as [OrderHed_TaxRegionCode],
[OrderHed].[TotalWHTax] as [OrderHed_TotalWHTax],
[OrderHed].[DocTotalWHTax] as [OrderHed_DocTotalWHTax],
[OrderHed].[Rpt1TotalWHTax] as [OrderHed_Rpt1TotalWHTax],
[OrderHed].[Rpt2TotalWHTax] as [OrderHed_Rpt2TotalWHTax],
[OrderHed].[Rpt3TotalWHTax] as [OrderHed_Rpt3TotalWHTax],
[OrderHed].[TotalSATax] as [OrderHed_TotalSATax],
[OrderHed].[DocTotalSATax] as [OrderHed_DocTotalSATax],
[OrderHed].[Rpt1TotalSATax] as [OrderHed_Rpt1TotalSATax],
[OrderHed].[Rpt2TotalSATax] as [OrderHed_Rpt2TotalSATax],
[OrderHed].[Rpt3TotalSATax] as [OrderHed_Rpt3TotalSATax],
[OrderHed].[TotalTax] as [OrderHed_TotalTax],
[OrderHed].[DocTotalTax] as [OrderHed_DocTotalTax],
[OrderHed].[TotalDiscount] as [OrderHed_TotalDiscount],
[OrderHed].[Rpt1TotalDiscount] as [OrderHed_Rpt1TotalDiscount],
[OrderHed].[Rpt2TotalDiscount] as [OrderHed_Rpt2TotalDiscount],
[OrderHed].[Rpt3TotalDiscount] as [OrderHed_Rpt3TotalDiscount],
[OrderHed].[DocTotalDiscount] as [OrderHed_DocTotalDiscount],
[OrderHed].[ShipToCustNum] as [OrderHed_ShipToCustNum],
[ShipHead].[DeviceUOM] as [ShipHead_DeviceUOM],
[ShipHead].[ManifestSizeUOM] as [ShipHead_ManifestSizeUOM],
[ShipHead].[ManifestWtUOM] as [ShipHead_ManifestWtUOM],
[ShipHead].[ManifestWeight] as [ShipHead_ManifestWeight],
[ShipHead].[ManifestLength] as [ShipHead_ManifestLength],
[ShipHead].[ManifestWidth] as [ShipHead_ManifestWidth],
[ShipHead].[ManifestHeight] as [ShipHead_ManifestHeight],
[OrderHed].[RateGrpCode] as [OrderHed_RateGrpCode],
[OrderHed].[InPrice] as [OrderHed_InPrice],
[ShipHead].[PBHoldNoInv] as [ShipHead_PBHoldNoInv],
[ShipHead].[ReconcileQty] as [ShipHead_ReconcileQty],
[OrderRel].[ScheduleNumber] as [OrderRel_ScheduleNumber],
[ShipHead].[CounterASN] as [ShipHead_CounterASN],
[OrderHed].[OurBank] as [OrderHed_OurBank],
[OrderHed].[ERSOrder] as [OrderHed_ERSOrder],
[OrderHed].[AutoPrintReady] as [OrderHed_AutoPrintReady],
[OrderRel].[ShipOvers] as [OrderRel_ShipOvers],
[ShipHead].[WIPackSlipCreated] as [ShipHead_WIPackSlipCreated],
[ShipHead].[SysRevID] as [ShipHead_SysRevID],
[ShipHead].[SysRowID] as [ShipHead_SysRowID],
[ShipHead].[AGAuthorizationCode] as [ShipHead_AGAuthorizationCode],
[ShipHead].[AGAuthorizationDate] as [ShipHead_AGAuthorizationDate],
[ShipHead].[AGCarrierCUIT] as [ShipHead_AGCarrierCUIT],
[ShipHead].[AGCOTMark] as [ShipHead_AGCOTMark],
[ShipHead].[AGDocumentLetter] as [ShipHead_AGDocumentLetter],
[ShipHead].[AGInvoicingPoint] as [ShipHead_AGInvoicingPoint],
[ShipHead].[AGLegalNumber] as [ShipHead_AGLegalNumber],
[ShipHead].[AGPrintingControlType] as [ShipHead_AGPrintingControlType],
[ShipHead].[AGTrackLicense] as [ShipHead_AGTrackLicense],
[OrderHed].[DispatchReason] as [OrderHed_DispatchReason],
[ShipHead].[AGShippingWay] as [ShipHead_AGShippingWay],
[ShipHead].[OurSupplierCode] as [ShipHead_OurSupplierCode],
[ShipHead].[ASNPrintedDate] as [ShipHead_ASNPrintedDate],
[ShipHead].[EDIShipToNum] as [ShipHead_EDIShipToNum],
[ShipHead].[MXIncoterm] as [ShipHead_MXIncoterm],
[ShipHead].[CreatedOn] as [ShipHead_CreatedOn],
[ShipHead].[DigitalSignature] as [ShipHead_DigitalSignature],
[ShipHead].[SignedOn] as [ShipHead_SignedOn],
[ShipHead].[SignedBy] as [ShipHead_SignedBy],
[ShipHead].[FirstPrintDate] as [ShipHead_FirstPrintDate],
[ShipHead].[DocCopyNum] as [ShipHead_DocCopyNum],
(’:’) as [Calculated_AutoInvoiceMessage]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
and OrderDtl.OrderNum = OrderRel.OrderNum
and OrderDtl.OrderLine = OrderRel.OrderLine
left outer join Erp.ShipHead as ShipHead on
OrderHed.CustNum = ShipHead.CustNum
and OrderHed.ShipToNum = ShipHead.ShipToNum
left outer join Erp.BOLHead as BOLHead on
OrderHed.CustNum = BOLHead.CustNum
and OrderHed.ShipToNum = BOLHead.ShipToNum
left outer join Erp.BOLDetail as BOLDetail on
BOLHead.Company = BOLDetail.Company
and BOLHead.BOLNum = BOLDetail.BOLNum
where (OrderHed.OrderNum = @OrderNum)

is this the one that works?

Is this in a BAQ?

Yes, and yes. Just updated my verbage in the opening.

when does it fail. guessing the link on the customer table is not correct

Thanks,

When I add the customer table to this. I have linked it to everything I can on this baq and get the error above. Doesn’t matter how it is linked. But if I do this on a different BAQ it works. So something is off somehow.

can you post the query with the table added.

thanks,

Can you post a screen shot of the designer with the join highlighted?

1 Like

select
[OrderHed].[Company] as [OrderHed_Company],
[ShipHead].[PackNum] as [ShipHead_PackNum],
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
[OrderHed].[ShipViaCode] as [OrderHed_ShipViaCode],
[ShipHead].[ShipPerson] as [ShipHead_ShipPerson],
[OrderHed].[EntryPerson] as [OrderHed_EntryPerson],
[ShipHead].[ShipLog] as [ShipHead_ShipLog],
[ShipHead].[LabelComment] as [ShipHead_LabelComment],
[OrderDtl].[ShipComment] as [OrderDtl_ShipComment],
[ShipHead].[Invoiced] as [ShipHead_Invoiced],
[OrderHed].[ShipToNum] as [OrderHed_ShipToNum],
[ShipHead].[ReadyToInvoice] as [ShipHead_ReadyToInvoice],
[OrderHed].[CustNum] as [OrderHed_CustNum],
[OrderHed].[Plant] as [OrderHed_Plant],
[ShipHead].[TrackingNumber] as [ShipHead_TrackingNumber],
(OrderHed.OrderNum) as [Calculated_LegalNumber],
[OrderHed].[VoidOrder] as [OrderHed_VoidOrder],
(‘false’) as [Calculated_ExternalDeliveryNote],
[ShipHead].[ExternalID] as [ShipHead_ExternalID],
(‘false’) as [Calculated_ICReceived],
(’:’) as [Calculated_XRefPackNum],
[OrderHed].[BTCustNum] as [OrderHed_BTCustNum],
[OrderHed].[BTConNum] as [OrderHed_BTConNum],
[ShipHead].[ShipStatus] as [ShipHead_ShipStatus],
[ShipHead].[ShipGroup] as [ShipHead_ShipGroup],
[ShipHead].[PkgCode] as [ShipHead_PkgCode],
[ShipHead].[PkgClass] as [ShipHead_PkgClass],
[ShipHead].[Weight] as [ShipHead_Weight],
[OrderHed].[ResDelivery] as [OrderHed_ResDelivery],
[OrderHed].[SatDelivery] as [OrderHed_SatDelivery],
[OrderHed].[SatPickup] as [OrderHed_SatPickup],
[OrderRel].[VerbalConf] as [OrderRel_VerbalConf],
[OrderRel].[Hazmat] as [OrderRel_Hazmat],
[OrderRel].[DocOnly] as [OrderRel_DocOnly],
[OrderRel].[RefNotes] as [OrderRel_RefNotes],
[OrderRel].[ApplyChrg] as [OrderRel_ApplyChrg],
[OrderRel].[ChrgAmount] as [OrderRel_ChrgAmount],
[OrderRel].[COD] as [OrderRel_COD],
[OrderRel].[CODFreight] as [OrderRel_CODFreight],
[OrderRel].[CODCheck] as [OrderRel_CODCheck],
[OrderRel].[CODAmount] as [OrderRel_CODAmount],
[OrderRel].[GroundType] as [OrderRel_GroundType],
[OrderRel].[NotifyFlag] as [OrderRel_NotifyFlag],
[OrderRel].[NotifyEMail] as [OrderRel_NotifyEMail],
[OrderRel].[DeclaredIns] as [OrderRel_DeclaredIns],
[OrderRel].[DeclaredAmt] as [OrderRel_DeclaredAmt],
[ShipHead].[MFTransNum] as [ShipHead_MFTransNum],
[ShipHead].[MFCallTag] as [ShipHead_MFCallTag],
[ShipHead].[MFPickupNum] as [ShipHead_MFPickupNum],
[ShipHead].[MFDiscFreight] as [ShipHead_MFDiscFreight],
[ShipHead].[MFTemplate] as [ShipHead_MFTemplate],
[ShipHead].[MFUse3B] as [ShipHead_MFUse3B],
[ShipHead].[MF3BAccount] as [ShipHead_MF3BAccount],
[ShipHead].[MFDimWeight] as [ShipHead_MFDimWeight],
[ShipHead].[MFZone] as [ShipHead_MFZone],
[ShipHead].[MFFreightAmt] as [ShipHead_MFFreightAmt],
[ShipHead].[MFOtherAmt] as [ShipHead_MFOtherAmt],
[ShipHead].[MFOversized] as [ShipHead_MFOversized],
[OrderRel].[ServSatDelivery] as [OrderRel_ServSatDelivery],
[OrderRel].[ServSatPickup] as [OrderRel_ServSatPickup],
[OrderRel].[ServSignature] as [OrderRel_ServSignature],
[OrderRel].[ServAlert] as [OrderRel_ServAlert],
[OrderRel].[ServPOD] as [OrderRel_ServPOD],
[OrderRel].[ServAOD] as [OrderRel_ServAOD],
[OrderRel].[ServHomeDel] as [OrderRel_ServHomeDel],
[OrderRel].[DeliveryType] as [OrderRel_DeliveryType],
[OrderRel].[ServDeliveryDate] as [OrderRel_ServDeliveryDate],
[OrderRel].[ServPhone] as [OrderRel_ServPhone],
[OrderRel].[ServInstruct] as [OrderRel_ServInstruct],
[OrderRel].[ServRelease] as [OrderRel_ServRelease],
[OrderRel].[ServAuthNum] as [OrderRel_ServAuthNum],
[OrderRel].[ServRef1] as [OrderRel_ServRef1],
[OrderRel].[ServRef2] as [OrderRel_ServRef2],
[OrderRel].[ServRef3] as [OrderRel_ServRef3],
[OrderRel].[ServRef4] as [OrderRel_ServRef4],
[OrderRel].[ServRef5] as [OrderRel_ServRef5],
[BOLDetail].[BOLNum] as [BOLDetail_BOLNum],
[BOLDetail].[BOLLine] as [BOLDetail_BOLLine],
[ShipHead].[CommercialInvoice] as [ShipHead_CommercialInvoice],
[ShipHead].[ShipExprtDeclartn] as [ShipHead_ShipExprtDeclartn],
[ShipHead].[CertOfOrigin] as [ShipHead_CertOfOrigin],
[ShipHead].[LetterOfInstr] as [ShipHead_LetterOfInstr],
[ShipHead].[HazardousShipment] as [ShipHead_HazardousShipment],
[ShipHead].[IntrntlShip] as [ShipHead_IntrntlShip],
[OrderHed].[PayFlag] as [OrderHed_PayFlag],
[OrderHed].[PayAccount] as [OrderHed_PayAccount],
[OrderHed].[PayBTAddress1] as [OrderHed_PayBTAddress1],
[OrderHed].[PayBTAddress2] as [OrderHed_PayBTAddress2],
[OrderHed].[PayBTCity] as [OrderHed_PayBTCity],
[OrderHed].[PayBTState] as [OrderHed_PayBTState],
[OrderHed].[PayBTZip] as [OrderHed_PayBTZip],
[OrderHed].[PayBTCountry] as [OrderHed_PayBTCountry],
[OrderHed].[FFAddress1] as [OrderHed_FFAddress1],
[OrderHed].[FFAddress2] as [OrderHed_FFAddress2],
[OrderHed].[FFCity] as [OrderHed_FFCity],
[OrderHed].[FFState] as [OrderHed_FFState],
[OrderHed].[FFZip] as [OrderHed_FFZip],
[OrderHed].[FFCountry] as [OrderHed_FFCountry],
[OrderHed].[FFContact] as [OrderHed_FFContact],
[OrderHed].[FFCompName] as [OrderHed_FFCompName],
[OrderHed].[FFPhoneNum] as [OrderHed_FFPhoneNum],
[ShipHead].[ChangedBy] as [ShipHead_ChangedBy],
[ShipHead].[ChangeDate] as [ShipHead_ChangeDate],
[ShipHead].[ChangeTime] as [ShipHead_ChangeTime],
[OrderHed].[FFID] as [OrderHed_FFID],
[OrderHed].[IndividualPackIDs] as [OrderHed_IndividualPackIDs],
[OrderHed].[FFAddress3] as [OrderHed_FFAddress3],
[OrderHed].[DeliveryConf] as [OrderHed_DeliveryConf],
[OrderHed].[AddlHdlgFlag] as [OrderHed_AddlHdlgFlag],
[OrderHed].[NonStdPkg] as [OrderHed_NonStdPkg],
[OrderHed].[FFCountryNum] as [OrderHed_FFCountryNum],
[OrderHed].[PayBTAddress3] as [OrderHed_PayBTAddress3],
[OrderHed].[PayBTPhone] as [OrderHed_PayBTPhone],
[ShipHead].[WayBillNbr] as [ShipHead_WayBillNbr],
(OrderHed.ShipViaCode) as [Calculated_FreightedShipViaCode],
[OrderHed].[UPSQuantumView] as [OrderHed_UPSQuantumView],
[OrderHed].[UPSQVShipFromName] as [OrderHed_UPSQVShipFromName],
[OrderHed].[UPSQVMemo] as [OrderHed_UPSQVMemo],
[ShipHead].[PkgLength] as [ShipHead_PkgLength],
[ShipHead].[PkgWidth] as [ShipHead_PkgWidth],
[ShipHead].[PkgHeight] as [ShipHead_PkgHeight],
[OrderHed].[EDIReady] as [OrderHed_EDIReady],
[ShipHead].[PhantomPack] as [ShipHead_PhantomPack],
[ShipHead].[ReplicatedFrom] as [ShipHead_ReplicatedFrom],
[ShipHead].[ReplicatedStat] as [ShipHead_ReplicatedStat],
[ShipHead].[PkgSizeUOM] as [ShipHead_PkgSizeUOM],
[ShipHead].[WeightUOM] as [ShipHead_WeightUOM],
[OrderHed].[UseOTS] as [OrderHed_UseOTS],
[OrderHed].[TranDocTypeID] as [OrderHed_TranDocTypeID],
[ShipHead].[DocumentPrinted] as [ShipHead_DocumentPrinted],
(orderhed.ordernum) as [Calculated_OTSOrderNum],
[ShipHead].[TaxCalculated] as [ShipHead_TaxCalculated],
[ShipHead].[TaxCalcDate] as [ShipHead_TaxCalcDate],
[OrderHed].[CurrencyCode] as [OrderHed_CurrencyCode],
[OrderHed].[Rounding] as [OrderHed_Rounding],
[OrderHed].[Rpt1Rounding] as [OrderHed_Rpt1Rounding],
[OrderHed].[Rpt2Rounding] as [OrderHed_Rpt2Rounding],
[OrderHed].[Rpt3Rounding] as [OrderHed_Rpt3Rounding],
[OrderHed].[OrderAmt] as [OrderHed_OrderAmt],
[OrderHed].[DocOrderAmt] as [OrderHed_DocOrderAmt],
[OrderHed].[Rpt1OrderAmt] as [OrderHed_Rpt1OrderAmt],
[OrderHed].[Rpt2OrderAmt] as [OrderHed_Rpt2OrderAmt],
[OrderHed].[Rpt3OrderAmt] as [OrderHed_Rpt3OrderAmt],
[OrderHed].[TaxRegionCode] as [OrderHed_TaxRegionCode],
[OrderHed].[TotalWHTax] as [OrderHed_TotalWHTax],
[OrderHed].[DocTotalWHTax] as [OrderHed_DocTotalWHTax],
[OrderHed].[Rpt1TotalWHTax] as [OrderHed_Rpt1TotalWHTax],
[OrderHed].[Rpt2TotalWHTax] as [OrderHed_Rpt2TotalWHTax],
[OrderHed].[Rpt3TotalWHTax] as [OrderHed_Rpt3TotalWHTax],
[OrderHed].[TotalSATax] as [OrderHed_TotalSATax],
[OrderHed].[DocTotalSATax] as [OrderHed_DocTotalSATax],
[OrderHed].[Rpt1TotalSATax] as [OrderHed_Rpt1TotalSATax],
[OrderHed].[Rpt2TotalSATax] as [OrderHed_Rpt2TotalSATax],
[OrderHed].[Rpt3TotalSATax] as [OrderHed_Rpt3TotalSATax],
[OrderHed].[TotalTax] as [OrderHed_TotalTax],
[OrderHed].[DocTotalTax] as [OrderHed_DocTotalTax],
[OrderHed].[TotalDiscount] as [OrderHed_TotalDiscount],
[OrderHed].[Rpt1TotalDiscount] as [OrderHed_Rpt1TotalDiscount],
[OrderHed].[Rpt2TotalDiscount] as [OrderHed_Rpt2TotalDiscount],
[OrderHed].[Rpt3TotalDiscount] as [OrderHed_Rpt3TotalDiscount],
[OrderHed].[DocTotalDiscount] as [OrderHed_DocTotalDiscount],
[OrderHed].[ShipToCustNum] as [OrderHed_ShipToCustNum],
[ShipHead].[DeviceUOM] as [ShipHead_DeviceUOM],
[ShipHead].[ManifestSizeUOM] as [ShipHead_ManifestSizeUOM],
[ShipHead].[ManifestWtUOM] as [ShipHead_ManifestWtUOM],
[ShipHead].[ManifestWeight] as [ShipHead_ManifestWeight],
[ShipHead].[ManifestLength] as [ShipHead_ManifestLength],
[ShipHead].[ManifestWidth] as [ShipHead_ManifestWidth],
[ShipHead].[ManifestHeight] as [ShipHead_ManifestHeight],
[OrderHed].[RateGrpCode] as [OrderHed_RateGrpCode],
[OrderHed].[InPrice] as [OrderHed_InPrice],
[ShipHead].[PBHoldNoInv] as [ShipHead_PBHoldNoInv],
[ShipHead].[ReconcileQty] as [ShipHead_ReconcileQty],
[OrderRel].[ScheduleNumber] as [OrderRel_ScheduleNumber],
[ShipHead].[CounterASN] as [ShipHead_CounterASN],
[OrderHed].[OurBank] as [OrderHed_OurBank],
[OrderHed].[ERSOrder] as [OrderHed_ERSOrder],
[OrderHed].[AutoPrintReady] as [OrderHed_AutoPrintReady],
[OrderRel].[ShipOvers] as [OrderRel_ShipOvers],
[ShipHead].[WIPackSlipCreated] as [ShipHead_WIPackSlipCreated],
[ShipHead].[SysRevID] as [ShipHead_SysRevID],
[ShipHead].[SysRowID] as [ShipHead_SysRowID],
[ShipHead].[AGAuthorizationCode] as [ShipHead_AGAuthorizationCode],
[ShipHead].[AGAuthorizationDate] as [ShipHead_AGAuthorizationDate],
[ShipHead].[AGCarrierCUIT] as [ShipHead_AGCarrierCUIT],
[ShipHead].[AGCOTMark] as [ShipHead_AGCOTMark],
[ShipHead].[AGDocumentLetter] as [ShipHead_AGDocumentLetter],
[ShipHead].[AGInvoicingPoint] as [ShipHead_AGInvoicingPoint],
[ShipHead].[AGLegalNumber] as [ShipHead_AGLegalNumber],
[ShipHead].[AGPrintingControlType] as [ShipHead_AGPrintingControlType],
[ShipHead].[AGTrackLicense] as [ShipHead_AGTrackLicense],
[OrderHed].[DispatchReason] as [OrderHed_DispatchReason],
[ShipHead].[AGShippingWay] as [ShipHead_AGShippingWay],
[ShipHead].[OurSupplierCode] as [ShipHead_OurSupplierCode],
[ShipHead].[ASNPrintedDate] as [ShipHead_ASNPrintedDate],
[ShipHead].[EDIShipToNum] as [ShipHead_EDIShipToNum],
[ShipHead].[MXIncoterm] as [ShipHead_MXIncoterm],
[ShipHead].[CreatedOn] as [ShipHead_CreatedOn],
[ShipHead].[DigitalSignature] as [ShipHead_DigitalSignature],
[ShipHead].[SignedOn] as [ShipHead_SignedOn],
[ShipHead].[SignedBy] as [ShipHead_SignedBy],
[ShipHead].[FirstPrintDate] as [ShipHead_FirstPrintDate],
[ShipHead].[DocCopyNum] as [ShipHead_DocCopyNum],
(’:’) as [Calculated_AutoInvoiceMessage]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
and OrderDtl.OrderNum = OrderRel.OrderNum
and OrderDtl.OrderLine = OrderRel.OrderLine
left outer join Erp.ShipHead as ShipHead on
OrderHed.CustNum = ShipHead.CustNum
and OrderHed.ShipToNum = ShipHead.ShipToNum
left outer join Erp.BOLHead as BOLHead on
OrderHed.CustNum = BOLHead.CustNum
and OrderHed.ShipToNum = BOLHead.ShipToNum
left outer join Erp.BOLDetail as BOLDetail on
BOLHead.Company = BOLDetail.Company
and BOLHead.BOLNum = BOLDetail.BOLNum
left outer join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.BTCustNum = Customer.CustNum
where (OrderHed.OrderNum = @OrderNum)

I posted the wrong one first, I have edited it to be correct.

Did you add any calculated fields or just the customer table?

Just the customer table. If I take it off, it works. When i add it, I get the error above.

I am at a loss. I ran that same query minus the calculated fields and had no problem. I would suggest creating a baq with just the customer table and seeing if there is any bad data in that. Column name ‘Null’ is what it is saying the problem is, but i dont know how that would have been possible.

you are aware that your join to the shiphead could bring back multiple items per order, unless you never ship to the same customer location twice.

check out ShipDtl to link to order rel

back to your issue

close out of the baq screen come back in
add table

Are you sure you want BTCustNum vs CustNum on the join

I have it restricted to a parameter on the ordernum. I haven’t had any duplicates at this time, but it is something I am watching for. I closed out the screen and it still gave me the error. So I opened up a new BAQ and started putting it together piece by piece. Interestingly, it gave me the same error when I added in the ShipHed_ExternalID. SO I replaced that with a calc field and the error stopped. From my understanding, the way we ship, this field never gets used. Still researching and hoping that stays the case. Not sure what happened to cause it to give an error the way it did, but it did.