I think it is a join issue

[Cpreston]

Hi

I have a report that shows information. I am testing against data on 2 orders. On one order number all the rows and Pack information comes through. When I use another order number I am getting rows missing on row 3 (see below). I have tried the Joins and changing them in all possible ways but cannot get them to show. I know this probably is a big ask but can anyone (with more experience than me)see anything blatantly wrong with the joins at all. (code below). By the way I have adapted an old report which shows the information in row 3, I had to do some changes to make it run with only showing shipref where = 2000. The information in Row 3 is true for the If statement.

I will keep trying but if anyone as any ideas it would be appreciated. Thanks

SELECT "OrderHeader"."OrderNumber", "OrderHeader"."DeliveryAddress", "OrderLine"."LineNumber", "OrderLine"."Quantity", "OrderLine"."Notes", "OrderHeader"."OrderID", "OrderHeader"."CustomerRef", "OrderLine"."LineType", "Product"."ProductType", "Product"."Length", "Product"."Width", "Product"."Thickness", "Product"."Description", "OrderLine"."ManualDescription", "OrderLine"."ManualCode", "Customer"."Name", "OrderHeader"."OrderType", "Product"."ProductCode", "Customer"."CashAccount", "OrderLine"."QuantityTally", "OrderHeader"."DeliveryDate", "OrderHeader"."NoOfPacks", "DinoOrderLineOption"."PackRef", "DinoOrderLineOption"."PackNoOfPieces", "DinoOrderLineOption"."Packlength", "DinoOrderLineOption"."OrderLineItemType", "DinoOrderLineOption"."packvolume", "DinoOrderLineOption"."PackQuantityTally", "OrderLine"."TotalLength", "OrderLine"."InputPerID", "CustomerAddress"."Telephone2", "CustomerProductReference"."CustomerProductDescription", "Product"."ProductGroupID", "ProductPack"."ShippingRef", "ProductGroup"."ParentID"
FROM ((((((("OrderHeader" "OrderHeader" LEFT OUTER JOIN "OrderLine" "OrderLine" ON "OrderHeader"."OrderID"="OrderLine"."OrderID") LEFT OUTER JOIN "Customer" "Customer" ON "OrderHeader"."CustomerID"="Customer"."CustomerID") LEFT OUTER JOIN "HTI Live"."dbo"."CustomerAddress" "CustomerAddress" ON "OrderHeader"."DeliveryAddressID"="CustomerAddress"."CustomerAddressID") LEFT OUTER JOIN "HTI Live"."dbo"."CustomerProductReference" "CustomerProductReference" ON ("OrderHeader"."CustomerID"="CustomerProductReference"."CustomerID") AND ("OrderLine"."ProductID"="CustomerProductReference"."ProductID")) LEFT OUTER JOIN "Product" "Product" ON "OrderLine"."ProductID"="Product"."ProductID") LEFT OUTER JOIN "DinoOrderLineOption" "DinoOrderLineOption" ON "OrderLine"."OrderLineID"="DinoOrderLineOption"."OrderLineID") INNER JOIN ("HTI Live"."dbo"."ProductPack" "ProductPack" RIGHT OUTER JOIN "HTI Live"."dbo"."OrderLineItem" "OrderLineItem" ON "ProductPack"."PackID"="OrderLineItem"."PackID") ON "OrderLine"."OrderLineID"="OrderLineItem"."OrderLineID") INNER JOIN "HTI Live"."dbo"."ProductGroup" "ProductGroup" ON "Product"."ProductGroupID"="ProductGroup"."ProductGroupID"
WHERE "OrderHeader"."OrderNumber"=3251546
ORDER BY "OrderHeader"."OrderID", "OrderLine"."LineNumber", "DinoOrderLineOption"."PackRef"

 

[Cpreston]

Hi

After more investigation I have found it is not the joins but my lack of syntax knowledge.

IF
{ProductGroup.ParentID} = 2000
THEN
"Pack: " + {DinoOrderLineOption.PackRef} + " " + {ProductPack.ShippingRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m")
+ " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")
ELSE
"Pack: " + {DinoOrderLineOption.PackRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")


I have found that even though a code may be in the
{ProductGroup.ParentID} = 2000

There may not be an actual ProductPack.ShippingRef in the field, so therefore it is taking out the rest of the information. So how do I get the ELSE to show if the ProductPack.ShippingRef also does not have a code in.

Hope that makes sense

Thanks

 

[Cpreston]

Hi

I now have my code looking like this, no errors but still does not work, any advice please

IF
{ProductGroup.ParentID} = 2000
Then
"Pack: " + {DinoOrderLineOption.PackRef} + " " + {ProductPack.ShippingRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")
ELSE
IF ISNULL ({ProductPack.ShippingRef}) THEN
"Pack: " + {DinoOrderLineOption.PackRef} + " " + {ProductPack.ShippingRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")
ELSE
"Pack: " + {DinoOrderLineOption.PackRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")

 

[kray4660]

Have to explain: "Does not work

 

[Cpreston]

I am getting the same result as the per the image in my first post.

Because the {ProductGroup.ParentID} equals 2000 but there is no code in the field it is taking out all of the row. Where in fact if it is group 2000 and as no code I still need it to display the ELSE line

So I need to somehow get the code to say IF {ProductGroup.ParentID} = 2000 AND IF there is no code in ProductPack.ShippingRef
I still need it to display the ELSE line

Hope that makes sense

 

[kray4660]

You mean like IF ISNULL ({ProductPack.ShippingRef}) AND {ProductGroup.ParentID} = 2000 THEN ?? (Always check for Nulls first in a if statement). Do you do have 'Default Values for Nulls' selected in the formula workshop?

 

[Cpreston]

Hi

Got the code like this now, same issue.

IF ISNULL ({ProductPack.ShippingRef}) AND {ProductGroup.ParentID} = 2000 THEN
"Pack: " + {DinoOrderLineOption.PackRef} + " " + {ProductPack.ShippingRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")
ELSE
"Pack: " + {DinoOrderLineOption.PackRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")


Sorry don't know about default values in formula workshop

 

[Cpreston]

Hi

I think I have solved it now.

For information the code is as below

 

[Cpreston]

Sorry the code is like

IF {ProductGroup.ParentID} = 2000 THEN
"Pack: " + {DinoOrderLineOption.PackRef} + " " + {ProductPack.ShippingRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")
ELSE
"Pack: " + {DinoOrderLineOption.PackRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")

And I also changed the drop down in the workshop to Default values for Nulls

It now works. Thanks for the help and pointing me in the right direction

 

[Cpreston]

Hi

Unfortunately this appears to be knocking out some rows for other products on the report. The code is as below

IF {ProductGroup.ParentID} = 2000 THEN
"Pack: " + {DinoOrderLineOption.PackRef} + " " + {ProductPack.ShippingRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")
ELSE
"Pack: " + {DinoOrderLineOption.PackRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")

But for some reason for some other products it is knocking these out. I have the Dafault Values for Nulls set in the editor, I changed it back but still have some rows missing.

If I revert back to the old code as below, it brings in all lines again, but of course leaves out ProductPack.ShippingRef as we have not added the code in for this then.

"Pack: " + {DinoOrderLineOption.PackRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")


Any ideas please anyone

Thanks

 

[Cpreston]

Hi

After a little investigation we do have certain products that do not have pack references at all (loose products)


These always show when we use the formula

"Pack: " + {DinoOrderLineOption.PackRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")

But for some reason my IF statement code is excluding these. Could someone please help and point me in the right direction. I have tried all sorts with the doing but getting nowhere

Many thanks

 

[Cpreston]

Hi

After even more investigation it appears to be a join issue also, I took out the table Product Pak and also a table orderlineitem.

I re-added them and at a point, using the joins, when I can get the ProductPack.ShippingRef to appear is when the orws where no packreference in the database disappears.

I have tried every which way with the joins but I am going round in circles. Does anyone anywhere have any ideas how I can test Joins or apply further code that makes any rows where no packref is there to appear also.

Many thanks

 

[Cpreston]

Hi

Still cannot et this report to work and slowly losing the will. The code I have is as below now, this is only bringing back 4 rows of data

IF {ProductGroup.ParentID} = 2000 THEN
"Pack: " + {DinoOrderLineOption.PackRef} + " " + {ProductPack.ShippingRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")
ELSE
IF isnull ({ProductPack.ShippingRef}) THEN {DinoOrderLineOption.PackQuantityTally}


If I take out all data and use the code below it brings in all 10 rows of the order, but of course does not have the Shipref pack number showing.

"Pack: " + {DinoOrderLineOption.PackRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")

On our orders the Packref may be null so therefore all I want to see then is the {DinoOrderLineOption.PackQuantityTally

No matter what I try with the code, it keeps bringing back only 4 rows. Really scratching my head on this one , could someone please please help, Thanks

 

[Cpreston]

Hi

Its ok I sorted this out eventually. The final coding was as below and I had to redo 2 joins, thanks

IF not isnull ({ProductPack.ShippingRef}) AND {ProductGroup.ParentID} = 2000 THEN
"Pack: " + {DinoOrderLineOption.PackRef} + " " + {ProductPack.ShippingRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")
ELSE
"Pack: " + {DinoOrderLineOption.PackRef} + " (" + (totext({DinoOrderLineOption.PackNoOfPieces},0) & "pc") + " " + (totext({DinoOrderLineOption.Packlength},1) & "m") + " " + (totext({DinoOrderLineOption.packvolume},3) & "m3)")