How to multi-join with SQL and TXT file? Getting error.
Bartender 10.1
I have a system that kicks out a text file for print jobs, which Commander picks up. I also have a SQL database with some supplemental data which needs to be combined into my labels. I can join one field from the text file to one in the database and populate a separate database field (GTIN) onto the label. The thing is, I need to join two fields from the text file to two separate fields in the database, and the system is throwing this error message.
Any ideas? I could work around this from SQL's end by creating a view that combines the two fields into one, but unless I can do something like that with the text file, I see no way to join two fields from the text file as needed. VB Scripting allows fields to be combined, but that is not a selectable item in the drop down where the joins are being specified.
Ignoring the error does no good; it throws again when I try and print.
-
If you're combining two databases, you only need one join statement. Once joined, you can use any field from either database. The values you join off of need to be equal to each other though i.e. UOM in one database needs to correlate to a UOM item in the other one.
2 -
Hi Adam,
The core issue though is it's not [seemingly] possible to use two fields on the join, that I can find. In other words I see no way to write this:
SELECT DB.GTIN FROM dbo.GTIN DB
INNER JOIN TXT ON TXT.MATNR = DB.SKU AND TXT.UOM = DB.UOMThe designer allows me to specify only one field per join, not two, and although some parts of the designer (e.g. when adding a SQL database source) allow you to write your own quassi-SQL query, there is no way I see in a text file source to combine two fields into one that could then be used in the join.
0 -
That is true, except that he has SKUs that can have different UOMs. He would have to join UOMs too, in order to get the correct record. I can't see of another way, other than a view. I am using a SQL View, which is called from a SP, the SP writes out a text file. Commander than picks up that text file using the label I have set up in the commander. I would be happy to share the SP code... let me know.
--Oops...I guess I misunderstood. The text file you have is coming from another source, not the same as the SQL?
0 -
" The text file you have is coming from another source, not the same as the SQL?"
Yep, and you're absolutely right a view is the way to handle this from SQL. I can combine the fields into one, e.g. SELECT SKU + UOM AS SKU_UOM from the SQL side, but there's no way to combine from the text file side. Bartender also doesn't appear to let me, when using a txt data source, combine two fields as one for the same effect.I spoke to support and it looks simply like there is no way to do what I'm trying to do in Bartender--I'll have to get my txt-creating source to first combine into a single value before I take control of the txt.
Thanks for the response!0 -
Update for anybody who comes along...
Though Bartender doesn't seem to allow a multi-field join, I have somewhat of a workable solution. I'm printing based only on my source text file. I then make a query every single print record to the database for the database field I need. The two main drawbacks of this approach are:
1) Queries the database for every single record; 1000 labels = 1000 queries
2) If there is data in the source file that has no matching data in SQL, I'm still printing it. In my scenario that works for me, but I can see in some it wouldn't, if the data cannot be realistically trusted. In that scenario if the record back from SQL is null, you could use an if/else to make an otherwise hidden image on the label show up, like a big X across the label, that label operators could key off.
Anyway, the basic code for it, which is working on Bartender 10.1 SR4 against SQL Server 2012 involves creating a text field on the template, settings its type to Visual Basic Script > Multi-Line Script, and then this code.
0 -
Update: as Shotaro pointed out in another thread, using the properties of a print template , I can easily set certain labels in a batch to print or not print. So in this scenario I can create a named datasource, which is populated by the VB Script, and then when that datasource value is empty, the label doesn't print.
0
请先登录再写评论。
评论
6 条评论