AnimaSola AnimaSola - 2 days ago 5
SQL Question

U-SQL build error, equijoin have different types

I'm trying to create a USQL job and defined my columns from the CSVs they will be retrieved from, however I'm always having issues on the JOIN portion, because the columns I am matching are of a different type. This is weird because I have defined them to be the same type. See screenshot of where the issue lies:

enter image description here

Here is the complete USQL:

@guestCheck =
EXTRACT GuestCheckID int,
POSCheckGUID Guid,
POSCheckNumber int?,
OwnerEmployeeID int,
CreatedDateTime DateTime?,
ClosedDateTime DateTime?,
TicketReference string,
CheckAmount decimal?,
POSTerminalID int,
CheckState string,
LocationID int?,
TableID int?,
Covers int?,
PostedDateTime DateTime?,
OrderChannelID int?,
MealPeriodID int?,
RVCLocationID int?,
ReopenedTerminalID int?,
ReopenedEmployeeID int?,
ReopenedDateTime DateTime?,
ClosedBusDate int?,
PostedBusDate int?,
BusHour byte?,
TaxExempt bool?,
TaxExemptReference string
FROM "/GuestCheck/GuestCheck-incomplete.csv"
USING Extractors.Csv();

@guestCheckAncillaryAmount =
EXTRACT CheckAncillaryAmountID int,
GuestCheckID int,
GuestCheckItemID int?,
AncillaryAmountTypeID int,
Amount decimal,
FirstDetail int?,
LastDetail int?,
IsReturn bool?,
ReturnReasonID int?,
AncillaryReasonID int?,
AncillaryNote string,
ClosedBusDate int?,
PostedBusDate int?,
BusHour byte?,
LocationID int?,
RVCLocationID int?,
IsDelisted bool?,
Exempted bool?
FROM "/GuestCheck/GuestCheckAncillaryAmount.csv"
USING Extractors.Csv();

@ancillaryAmountType =
EXTRACT AncillaryAmountTypeID int,
AncillaryAmountCategoryID int,
CustomerID int,
CheckTitle string,
ReportTitle string,
Percentage decimal,
FixedAmount decimal,
IncludeOnCheck bool,
AutoCalculate bool,
StoreAtCheckLevel bool?,
DateTimeModified DateTime?,
CheckTitleToken Guid?,
ReportTitleToken Guid?,
DeletedFlag bool,
MaxUsageQty int?,
ApplyToBasePriceOnly bool?,
Exclusive bool,
IsItem bool,
MinValue decimal,
MaxValue decimal,
ItemGroupID int?,
LocationID int,
ApplicationOrder int?,
RequiresReason bool,
Exemptable bool?
FROM "/GuestCheck/AncillaryAmountType.csv"
USING Extractors.Csv();

@read =
SELECT t.POSCheckGUID,
t.POSCheckNumber,
t.CheckAmount,
aat.AncillaryAmountTypeID,
aat.CheckTitle,
gcd.Amount
FROM @guestCheck AS t
LEFT JOIN
@guestCheckAncillaryAmount AS gcd
ON t.GuestCheckID == gcd.GuestCheckID
LEFT JOIN
@ancillaryAmountType AS aat
ON gcd.AncillaryAmountTypeID == aat.AncillaryAmountTypeID
WHERE aat.AncillaryAmountCategoryID IN(2, 4, 8);

OUTPUT @read
TO "/GuestCheckOutput/output.csv"
USING Outputters.Csv();

Answer

Indeed, U-SQL is strongly typed, and int and int? are different types. You would need to cast in an intermediate rowset:

@ancillaryAmountType2 =
SELECT (int?) aat.AncillaryAmountTypeID AS AncillaryAmountTypeID,
       aat.AncillaryAmountCategoryID,
       aat.CheckTitle
FROM @ancillaryAmountType AS aat;

Or, better, use dimensional modeling best practice, and avoid nullable "dimensions" for the reasons stated in http://blog.chrisadamson.com/2013/01/avoid-null-in-dimensions.html.

Comments