Different SQL statement per label template on the same document 关注

0
Avatar
Seb Atkins

We want one document that contains two templates, only one will print based on whether the data entered (by Barcode Scanner) contains a "-"

Basically they want one label for sales orders and internal transfers but only want to select one document in the print station which i know can be done using the templates feature, the issue we have is that the data is stored in separate tables without any links between the two tables and that the sales order number could also exist in the internal transfer table so the only way i can think to accomplish this is with two separate SQL statements.

When the data is input we can check to see whether it is a sales order or internal transfer as the internal transfer contains a "-" (this gets removed later as it does not contain one in the database)

I have tried a case statement as below (apologies as it is a huge query) but i just get a syntax error... might also be worth noting that this is a progress database being queried using ODBC drivers.

SELECT
CASE
WHEN CHARINDEX('-', '?OrderNo') = 0 THEN
(SELECT "oeeh"."orderno", "oeeh"."ordersuf", "oeeh"."takenby", "arsc"."name", "oeeh"."custpo", "oeeh"."custno", "oeel"."lineno", "oeel"."user1", "icsec"."prod", "icetl"."lotno", "icsel"."opendt", "icetl"."quantity", "icss"."csunperstk", "oeel"."proddesc", "icsp"."descrip", "oeel"."netamt", "oeel"."qtyord", "oeeh"."pickeddt", "icsp"."weight", "icsel"."expiredt", "icsw"."binloc1" AS "binloc1", "icsd"."name" AS "ICSDname", "oeel"."qtyship"
FROM {oj (((((((("LIVENXT"."PUB"."arsc" "arsc" INNER JOIN "LIVENXT"."PUB"."oeeh" "oeeh" ON ("arsc"."cono"="oeeh"."cono") AND ("arsc"."custno"="oeeh"."custno")) INNER JOIN "LIVENXT"."PUB"."oeel" "oeel" ON ((("oeeh"."cono"="oeel"."cono") AND ("oeeh"."custno"="oeel"."custno")) AND ("oeeh"."orderno"="oeel"."orderno")) AND ("oeeh"."ordersuf"="oeel"."ordersuf")) INNER JOIN "LIVENXT"."PUB"."icsd" "icsd" ON ("oeeh"."cono"="icsd"."cono") AND ("oeeh"."whse"="icsd"."whse")) LEFT OUTER JOIN "LIVENXT"."PUB"."icsp" "icsp" ON ("oeel"."cono"="icsp"."cono") AND ("oeel"."shipprod"="icsp"."prod")) LEFT OUTER JOIN "LIVENXT"."PUB"."icsec" "icsec" ON (("oeel"."shipprod"="icsec"."altprod") AND ("oeel"."custno"="icsec"."custno")) AND ("oeel"."cono"="icsec"."cono")) LEFT OUTER JOIN "LIVENXT"."PUB"."icetl" "icetl" ON (((("oeel"."cono"="icetl"."cono") AND ("oeel"."orderno"="icetl"."orderno")) AND ("oeel"."ordersuf"="icetl"."ordersuf")) AND ("oeel"."lineno"="icetl"."lineno")) AND ("oeel"."shipprod"="icetl"."prod")) LEFT OUTER JOIN "LIVENXT"."PUB"."icss" "icss" ON (("oeel"."cono"="icss"."cono") AND ("oeel"."shipprod"="icss"."prod")) AND ("oeel"."icspecrecno"="icss"."icspecrecno")) LEFT OUTER JOIN "LIVENXT"."PUB"."icsw" "icsw" ON (("oeel"."cono"="icsw"."cono") AND ("oeel"."whse"="icsw"."whse")) AND ("oeel"."shipprod"="icsw"."prod")) LEFT OUTER JOIN "LIVENXT"."PUB"."icsel" "icsel" ON (("icetl"."cono"="icsel"."cono") AND ("icetl"."prod"="icsel"."prod")) AND ("icetl"."lotno"="icsel"."lotno")}
WHERE "oeel"."qtyship">0
AND "oeel"."specnstype"<>'l'
AND "oeel"."prodcat" LIKE 'C%'
AND "oeeh"."orderno" = LEFT('?OrderNo' , 7)
AND "oeeh"."ordersuf" = RIGHT('?OrderNo' , 2))
ELSE CHARINDEX('-', '?OrderNo') > 0 THEN
(SELECT "icsw1"."binloc1", "wteh1"."cono", "wteh1"."printeddt", "wteh1"."refer", "wteh1"."shipinstr", "wteh1"."wtno", "wteh1"."wtsuf", "icsp1"."descrip", "wtel1"."lineno", "wtel1"."proddesc", "wtel1"."proddesc2", "wtel1"."qtyship", "wtel1"."shipprod", "icsd1"."name", "icetl1"."lotno", "wteh1"."shipfmwhse", "wtel1"."prodcato", "icetl1"."quantity", "wteh1"."shiptowhse", "icsel1"."opendt", "wtel1"."binloc"
FROM {oj ((((("LIVENXT"."PUB"."icsd" "icsd1" INNER JOIN "LIVENXT"."PUB"."wteh" "wteh1" ON ("icsd1"."cono"="wteh1"."cono") AND ("icsd1"."whse"="wteh1"."shiptowhse")) INNER JOIN "LIVENXT"."PUB"."wtel" "wtel1" ON (("wteh1"."cono"="wtel1"."cono") AND ("wteh1"."wtno"="wtel1"."wtno")) AND ("wteh1"."wtsuf"="wtel1"."wtsuf")) LEFT OUTER JOIN "LIVENXT"."PUB"."icsw" "icsw1" ON (("wtel1"."cono"="icsw1"."cono") AND ("wtel1"."shipprod"="icsw1"."prod")) AND ("wtel1"."shipfmwhse"="icsw1"."whse")) LEFT OUTER JOIN "LIVENXT"."PUB"."icetl" "icetl1" ON (((("wtel1"."wtno"="icetl1"."orderno") AND ("wtel1"."wtsuf"="icetl1"."ordersuf")) AND ("wtel1"."lineno"="icetl1"."lineno")) AND ("wtel1"."cono"="icetl1"."cono")) AND ("wtel1"."shipprod"="icetl1"."prod")) LEFT OUTER JOIN "LIVENXT"."PUB"."icsel" "icsel1" ON (((("wtel1"."shipfmwhse"="icsel1"."whse") AND ("icetl1"."whse"="icsel1"."whse")) AND ("icetl1"."prod"="icsel1"."prod")) AND ("icetl1"."lotno"="icsel1"."lotno")) AND ("icetl1"."cono"="icsel1"."cono")) LEFT OUTER JOIN "LIVENXT"."PUB"."icsp" "icsp1" ON ("icsw1"."cono"="icsp1"."cono") AND ("icsw1"."prod"="icsp1"."prod")}
WHERE "wteh1"."wtno" = LEFT('?OrderNo' , 8) AND
"wteh1"."wtsuf" = RIGHT('?OrderNo' , 2))
END

3 评论

0
Avatar
Peter Thane
评论操作 固定链接

I'm not sure if you can do that due to the lack of field that you can use to join the two tables together for the BarTender database connection.

I know we have fooled it in the past by adding in a dummy field in each table both with the same value so we could create a "join", but the last time was in an older version of BarTender and not 2016. The rest of the query did the actual selection but the dummies just meant we link the tables together.

If you can get that to work you may want to use multiple layers rather than multiple templates and then setup conditional printing for each layer (from the Layer>Properties>Print Visibility options) so that one layer prints fields for sales orders and the others for internals. 

 

0
Avatar
Travis Truax
评论操作 固定链接

Seb,

    Yeah, that's quite ugly! I don't know much of anything about Progress, but if it was SQL Server or PostgreSQL or something less odd, I would probably consider writing a stored procedure here, but there's usually *some* way to work around stuff like this.

Did you try this query from any kind of "query testing tool"?  It doesn't look functional to me.

  • I'm not sure the CASE statement can be used in this way.
  • Even if it can, you have a condition placed in the ELSE clause, which should likely not be there.

Besides all that, I don't think this query would play nice with BT regardless of whether the query works. BT is going to want a common set of columns to work from. There are probably several options available with BT, depending on the version you have. If the Automation version is available, you could probably have Integration Builder choose between different formats (even if they had the same label name, in different directories).

But if you really want to stick to the approach you're trying (or have no other options):

Since you have 2 separate data structures, you could try other ways for squishing them together. I mentioned the SP above, but you could also UNION two completely separate queries, adding NULL columns where needed, or CROSS JOIN the tables, ultimately ending up with the important part: a common set of fields to work with, even if record # 1 has the first 10 fields NULL and the next 10 populated, and record # 2 vice versa, like this:

orderno, ordersuf, takenby, name, custpo..........binloc1, cono, printeddt, refer, shipinstr......

1001, 6543, "J34", "Jim", "TY6725" ................NULL, NULL, NULL, NULL, NULL.......

NULL, NULL, NULL, NULL, NULL ......................"53268KL", 5200, "2018-03-04", 76543, "Instructions"..........

Then you should be able to choose which template to use depending on whether data exists in a particular field.

 

Trav-

0
Avatar
Seb Atkins
评论操作 固定链接

Hi Guy's

Yeah in the end i went with UNION, made sure the amount of columns and data types were the same in each query. Even though the "Order Numbers" could be in both tables it is unlikely that both will still be active so i have added the stage of the order in to minimise the potential of this happening.

This is working but not ideal.

I tried multiple ways with CASE i think instead of ELSE it should be WHEN i copied the query from where i was testing it (with diffent variations on the syntax) so i think you're right on that.

Thanks for your input on this guys!

 

请先登录再写评论。