BAQ: Calculated Field Concatenate Rows to single field


(Brandon Anderson) #42

That’s what I did. I’m stumped.

select 
	[Summary].[PartBin_PartNum] as [PartBin_PartNum],
	[Summary].[Part_PartDescription] as [Part_PartDescription],
	[Summary].[Calculated_Qty] as [Calculated_Qty],
	(Replace(Replace(((select 
	(CAST(FilteredBins.PartBin2_BinNum AS VARCHAR) + ',') as [Calculated_Bins]
from  (select 
	[PartBin2].[PartNum] as [PartBin2_PartNum],
	[PartBin2].[BinNum] as [PartBin2_BinNum]
from Erp.PartBin as PartBin2
where (PartBin2.BinNum like '%402044'  or PartBin2.BinNum like '%402047'))  as FilteredBins
where FilteredBins. = Summary.PartBin_PartNum FOR XML PATH(''))) ,'</Calculated_Bins>',''),'<Calculated_Bins>','')) as [Calculated_Bins]
from  (select 
	[PartBin].[PartNum] as [PartBin_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	(sum(PartBin.OnhandQty)) as [Calculated_Qty]
from Erp.PartBin as PartBin
inner join Erp.Part as Part on 
	PartBin.Company = Part.Company
	and PartBin.PartNum = Part.PartNum
group by [PartBin].[PartNum],
	[Part].[PartDescription])  as Summary

(Brandon Anderson) #43

Actually, it’s working. There were some missing filters so the nulls were valid. I re-added them, probably after being too click happy, and it’s working now. Thanks.


(Dan Godfrey) #44

Has anyone experienced a character limitation when using this concept. What I mean is the results of creating a list into a single column, limits each element in the list to 30 characters. I am doing this to a UD field that is set for a string with a format of x(1000). Neither of my calcFields are set for x(30).


(Calvin Krusen) #45

The field “Products Group Lines” sown below is a concatenated one. And that one is over 80 chars

Do you see it right in the BAQ designer? Or truncated when used in dashboards and/or reports?
Is it possible that there is a new line at the end of every value being concatenated?

One last thing, there’s a weird bug where the first line of a Part description is always padded to 30 characters. Maybe thats related.


(Dan Godfrey) #46

Hi @ckrusen, I think you misunderstood me. I don’t mean the size of the total field, but the size of each element within the list. So between the commas. Here is the results that I am getting.!
image
image


(Calvin Krusen) #47

Hmmm… Is that from the first line of the Part.PartDescription?


(Calvin Krusen) #48

I think you need to look at the calc field that combines the values. Mine is

CAST(OrderDtl1.OrderLine AS VARCHAR) + ','

try changing VARCHAR to VARCHAR(50) (or however long you need it to be)

edit:

That might pad the individual values, so a TRIM might be needed. Maybe:

TRIM(CAST(OrderDtl1.OrderLine AS VARCHAR(100))) + ','

(Dan Godfrey) #49

That was it @ckrusen! I can’t believe I didn’t think of that.


(Anthony Marola) #50

Hi @ckrusen, I followed along exactly like your Order example, but when I try to Analyze results I keep getting error “Severity: Error, Table: , Field: , RowID: , Text: No column name was specified for column 1 of ‘SubQuery2’.”

The fields are all exactly named like your example, so I’m at a loss. I get this error regardless of which tables I try this with, so maybe a bug in 10.0.700?


(Calvin Krusen) #51

Can you post the Query Phrase (shown on the General tab)?


(Anthony Marola) #52

Yup! Here you go:

select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
(REPLACE(REPLACE(((select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
(CAST(OrderHed.OrderNum AS VARCHAR) + ‘,’) as [Calculated_OrderNum]
from Erp.OrderHed as OrderHed
where OrderHed.CustNum = Customer.CustNum FOR XML PATH (’’))),’</Calculated_OrderNum>’,’’),’<Calculated_OrderNum>’,’’)) as [Calculated_Orders]
from Erp.Customer as Customer


(Calvin Krusen) #53

I don’t see a “SubQuery2” in there…

Mine has 2 queries.
image

The Top Level one (named “Main”), does not include the other one. The Top only contains tables.

The second sub query (‘OrderLines’) has two tables
Neither has any table criteria
image

The second query DOES have SubQuery Criteria:

and the following display columns
image

The expression for that calc field is
image

Make sure the SubQuery Criteria with the ‘…FOR XML …’ is the last criteria


(Anthony Marola) #54

I do have SubQuery 2, but it’s not joined to SubQuery 1, unless there’s a step I’m missing? Screenshots attached.


(Calvin Krusen) #55

Try removing the OrderHed_OrderNum column from SubQuery2.


(Calvin Krusen) #56

You also need to add to the SubQuery Criteria for SubQuery2. Basically make the “joins” that you would have had if SubQuery2 was being used in SubQuery1

It might not be obvious, but in mine the Table column referes to the table in the second Sub-Query. (because my main subquuery alread has a table OrderDtl, the one inserted into SubQuery2 was named OrderDtl1. )

So I had to add criteria to connect the SubQ2 to the main one. Note that the tables in the expressions of the Filter Value column refere to OrderDtl And not OrderDtl1 (ignore that one line that does. It is to limit the results of the second SQ, and not used for linking to SQ1)


(Anthony Marola) #57

I added that originally to see if the error message would go away. I removed it and still get the error message.


(Anthony Marola) #58

Isn’t that what I’m doing with the SubQuery Criteria for SubQuery2 here?


(Calvin Krusen) #59

I’m not 100% positive, but I think that SQ Criteria (with the “… XML…”) just ends up inserting the required code in the Query Phrase to make the conactenation. I think you still need have criteria linking it to SQ1.

In my example, SQ Criteria:

  • Lines 1-3 link it to the main query. I think of it like the join to limit which records SQ2 uses to make that concatenated value.
    *Lines 4-6 limit which records of SQ I’m going to use for the concat.
  • Line 7 is the one that builds the code in the Query Phrase

(Anthony Marola) #60

OK I joined OrderHed.CustNum to Customer.CustNum, re-ran the query and got the same error. I’m at a total loss! Appreciate your help thus far…


(Calvin Krusen) #61

I’ll try to duplicate your BAQ…