Joining Two Databases
Hi everyone,
I'm quite new to Bartender despite having the program for quite some time. I am currently trying to create a label with information from two databases(MySQL and PervasiveSQL), using two ODBC Data Sources to create my connection to the two Databases.
I am able to create a label with information from multiple tabels within PervasiveSQL using Left Outer Joins. I've used a custom query similar to this (table1 LEFT OUTER JOIN table2 ON tabel1.field1 = tabel2.field1) where 'field1' is the same data on both tables. Both tables are PervasiveSQL tables.
On another label I am able to retrieve data from a MySQL table. I would like to be able to retrieve data from both databases and print this information on a single barcode label.
Is it possible to create a query that grabs informaiton from two different databases.
I've tried
SELECT database1.table1.field1 FROM (database1.table1 LEFT OUTER JOIN database2.table2 ON database1.table1.field2 = database2.table2.field2) where table1.field2='value'
Needless to say this returned a Database Error.
Any help would be much appreciated.
Regards
Teo Balgos
I'm quite new to Bartender despite having the program for quite some time. I am currently trying to create a label with information from two databases(MySQL and PervasiveSQL), using two ODBC Data Sources to create my connection to the two Databases.
I am able to create a label with information from multiple tabels within PervasiveSQL using Left Outer Joins. I've used a custom query similar to this (table1 LEFT OUTER JOIN table2 ON tabel1.field1 = tabel2.field1) where 'field1' is the same data on both tables. Both tables are PervasiveSQL tables.
On another label I am able to retrieve data from a MySQL table. I would like to be able to retrieve data from both databases and print this information on a single barcode label.
Is it possible to create a query that grabs informaiton from two different databases.
I've tried
SELECT database1.table1.field1 FROM (database1.table1 LEFT OUTER JOIN database2.table2 ON database1.table1.field2 = database2.table2.field2) where table1.field2='value'
Needless to say this returned a Database Error.
Any help would be much appreciated.
Regards
Teo Balgos
0
-
The Multi-Database Joins tab, located in the Database Connection Setup dialog, allows you to join multiple databases, rather than multiple tables within a database. A join tells BarTender how data is related. In order to make sense of multiple databases, they must be associated.
* Open the Database Connection Setup Menu in BarTender. Right click on "All Databases" and select "Add database". Select the name of your Database and table. You can add your two database MySQL Table 1 and PervasiveSQL Table2
* Now select the "All Databases" Node on this Tree and select the "Multi-Database Joins" tab on the right side
The join can be configured as the below :
The "Left Table" can be MySQL Table1. Choose "Field 1 " as the "Left Field". The "Right Table" will be PervasiveSQL Table2 and the "Right Field" will be Field 2 (assume this is the associated data to join these two database table)
If you now select the "Browse" tab, do you get any error messages or is there a record displayed?
** All multi-database joins within BarTender are [b]inner joins[/b]. This means that BarTender will use only records for which the data in the left field and right fields are exact matches in both content and datatype
If you need additional help, you can contact local support office for more personalized assistance for the problems. Support contact information: http://www.seagullscientific.com/aspx/technical-support.aspx0 -
[quote name='Susan C - Seagull Support' timestamp='1309875166' post='165']
The Multi-Database Joins tab, located in the Database Connection Setup dialog, allows you to join multiple databases, rather than multiple tables within a database. A join tells BarTender how data is related. In order to make sense of multiple databases, they must be associated.
* Open the Database Connection Setup Menu in BarTender. Right click on "All Databases" and select "Add database". Select the name of your Database and table. You can add your two database MySQL Table 1 and PervasiveSQL Table2
* Now select the "All Databases" Node on this Tree and select the "Multi-Database Joins" tab on the right side
The join can be configured as the below :
The "Left Table" can be MySQL Table1. Choose "Field 1 " as the "Left Field". The "Right Table" will be PervasiveSQL Table2 and the "Right Field" will be Field 2 (assume this is the associated data to join these two database table)
If you now select the "Browse" tab, do you get any error messages or is there a record displayed?
** All multi-database joins within BarTender are [b]inner joins[/b]. This means that BarTender will use only records for which the data in the left field and right fields are exact matches in both content and datatype
If you need additional help, you can contact local support office for more personalized assistance for the problems. Support contact information: [url="http://www.seagullscientific.com/aspx/technical-support.aspx"]http://www.seagullsc...al-support.aspx[/url]
[/quote]
Hi Susan
I was able to create a link using the Multi Database Join and using a very simple select query returned the information that I was looking for.
However I am not able to do the same with my actual label where there is a custom query that I"ve created to gather data and calculations from multiple tables that I needed.
My custom PervasiveSQL query looks like this,
SELECT DISTINCT [b]stknumbs[/b].job_no, [b]stknumbs[/b].lot_no, [b]stknumbs[/b].quantity, [b]stknumbs[/b].trans_date, [b]stkdetls[/b].look_up, [b]desph[/b].cust_order, [b]cusaddrs[/b].name, [b]despd[/b].noitems,
If([b]JOBFILE[/b].QUOTE_SUFFIX='A' AND [b]JOBFILE[/b].STATUS IN ('PTT','INP'),'04','00') as JOBStatus,
Round([b]PALLSTD[/b].WEIGHT_KG-[b]PALLSTD[/b].PALLET_KG/[b]PRODSTD[/b].NO_PER_PALLET*[b]STKNUMBS[/b].QUANTITY,0) as NW,
Round([b]PALLSTD[/b].WEIGHT_KG-[b]PALLSTD[/b].PALLET_KG/[b]PRODSTD[/b].NO_PER_PALLET*[b]STKNUMBS[/b].QUANTITY +[b]PALLSTD[/b].PALLET_KG,0) as GW,
(([b]STKNUMBS[/b].QUANTITY*0.1)/0.1) as BCQuantity,
(LTrim(RTRim([b]stkdetls[/b].desc1))+' '+LTrim(RTRim([b]stkdetls[/b].desc2))) as Description,
LTrim(RTRim([b]desph[/b].comment2)) as PalletsDelivered
FROM (((((((([b]STKNUMBS[/b] LEFT OUTER JOIN [b]PRODSTD[/b] ON [b]STKNUMBS[/b].PART = [b]PRODSTD[/b].PART)
LEFT OUTER JOIN [b]STKDETLS[/b] ON [b]STKNUMBS[/b].PART = [b]STKDETLS[/b].PART)
LEFT OUTER JOIN [b]DESPH[/b] ON [b]STKNUMBS[/b].DELIVERY = [b]DESPH[/b]."DEL#" AND [b]STKNUMBS[/b].JOB_NO = [b]DESPH[/b]."JOB#")
LEFT OUTER JOIN [b]CUSADDRS[/b] ON DESPH.[b]CUSTOMER[/b] = [b]CUSADDRS[/b].CUSTOMER)
LEFT OUTER JOIN [b]DESPD[/b] ON [b]DESPH[/b]."DEL#"=[b]DESPD[/b]."DELIVERY#")
LEFT OUTER JOIN [b]PALLSTD[/b] ON [b]PRODSTD[/b].PACKING_SPEC_NO = [b]PALLSTD[/b].SPECNO)
LEFT OUTER JOIN [b]JOBFILE[/b] ON [b]STKNUMBS[/b].JOB_NO = [b]JOBFILE[/b].NUMBER)
WHERE "[b]STKNUMBS[/b]"."JOB_NO" = '?job_no' and "[b]STKNUMBS[/b]"."LOT_NO" = '?pallet'
I've tried to include the following
LEFT OUTER JOIN cocheader ON STKNUMBS.JOB_NO = cocheader.jobno)
LEFT INNER JOIN cocheader.jobno ON STKNUMBS.JOB_NO = cocheader.jobno)
Both returned Error 37000: Syntax error or access violation
PervasiveSQL tables are;
[b]STKNUMBS
[/b][b]PRODSTD[/b]
[b]STKDETLS[/b]
[b]DESPH[/b]
[b]CUSADDRS[/b]
[b]DESPD[/b]
[b]JOBFILE[/b]
The MySQL table name that I needed additonal information from is;
[b]COCHEADER
[/b]How do I create a custom Database Join?
Regards
Teo0 -
Teo,
I'm not familiar with PrevasiveSQL, but if I was trying to solve that problem with SQLServer, I would wrap most of that logic in a view and leave it server side. I'd query against the view from BarTender sending just the selection criteria I wanted. Thus, I'd have something like the statement below for BarTender and the rest handled from the DB,
[code]SELECT *
FROM aView
WHERE aView."JOB_NO" = '?job_no' and aView."LOT_NO" = '?pallet'
[/code]
I understand this doesn't answer your question directly, but it would allow the more complex SQL statement to stay in the environment best suited and allow your BarTender logic to be simplified. Best of luck finding your solution.
~S
[quote name='tej_gee' timestamp='1309936104' post='169']
Hi Susan
I was able to create a link using the Multi Database Join and using a very simple select query returned the information that I was looking for.
However I am not able to do the same with my actual label where there is a custom query that I"ve created to gather data and calculations from multiple tables that I needed.
My custom PervasiveSQL query looks like this,
SELECT DISTINCT [b]stknumbs[/b].job_no, [b]stknumbs[/b].lot_no, [b]stknumbs[/b].quantity, [b]stknumbs[/b].trans_date, [b]stkdetls[/b].look_up, [b]desph[/b].cust_order, [b]cusaddrs[/b].name, [b]despd[/b].noitems,
If([b]JOBFILE[/b].QUOTE_SUFFIX='A' AND [b]JOBFILE[/b].STATUS IN ('PTT','INP'),'04','00') as JOBStatus,
Round([b]PALLSTD[/b].WEIGHT_KG-[b]PALLSTD[/b].PALLET_KG/[b]PRODSTD[/b].NO_PER_PALLET*[b]STKNUMBS[/b].QUANTITY,0) as NW,
Round([b]PALLSTD[/b].WEIGHT_KG-[b]PALLSTD[/b].PALLET_KG/[b]PRODSTD[/b].NO_PER_PALLET*[b]STKNUMBS[/b].QUANTITY +[b]PALLSTD[/b].PALLET_KG,0) as GW,
(([b]STKNUMBS[/b].QUANTITY*0.1)/0.1) as BCQuantity,
(LTrim(RTRim([b]stkdetls[/b].desc1))+' '+LTrim(RTRim([b]stkdetls[/b].desc2))) as Description,
LTrim(RTRim([b]desph[/b].comment2)) as PalletsDelivered
FROM (((((((([b]STKNUMBS[/b] LEFT OUTER JOIN [b]PRODSTD[/b] ON [b]STKNUMBS[/b].PART = [b]PRODSTD[/b].PART)
LEFT OUTER JOIN [b]STKDETLS[/b] ON [b]STKNUMBS[/b].PART = [b]STKDETLS[/b].PART)
LEFT OUTER JOIN [b]DESPH[/b] ON [b]STKNUMBS[/b].DELIVERY = [b]DESPH[/b]."DEL#" AND [b]STKNUMBS[/b].JOB_NO = [b]DESPH[/b]."JOB#")
LEFT OUTER JOIN [b]CUSADDRS[/b] ON DESPH.[b]CUSTOMER[/b] = [b]CUSADDRS[/b].CUSTOMER)
LEFT OUTER JOIN [b]DESPD[/b] ON [b]DESPH[/b]."DEL#"=[b]DESPD[/b]."DELIVERY#")
LEFT OUTER JOIN [b]PALLSTD[/b] ON [b]PRODSTD[/b].PACKING_SPEC_NO = [b]PALLSTD[/b].SPECNO)
LEFT OUTER JOIN [b]JOBFILE[/b] ON [b]STKNUMBS[/b].JOB_NO = [b]JOBFILE[/b].NUMBER)
WHERE "[b]STKNUMBS[/b]"."JOB_NO" = '?job_no' and "[b]STKNUMBS[/b]"."LOT_NO" = '?pallet'
I've tried to include the following
LEFT OUTER JOIN cocheader ON STKNUMBS.JOB_NO = cocheader.jobno)
LEFT INNER JOIN cocheader.jobno ON STKNUMBS.JOB_NO = cocheader.jobno)
Both returned Error 37000: Syntax error or access violation
PervasiveSQL tables are;
[b]STKNUMBS
[/b][b]PRODSTD[/b]
[b]STKDETLS[/b]
[b]DESPH[/b]
[b]CUSADDRS[/b]
[b]DESPD[/b]
[b]JOBFILE[/b]
The MySQL table name that I needed additonal information from is;
[b]COCHEADER
[/b]How do I create a custom Database Join?
Regards
Teo
[/quote]0
请先登录再写评论。
评论
3 条评论