Need to do an OUTER JOIN

Issues related to label design (working with databases, data processing, RFID encoding etc.) and printing (from NiceLabel Express, NiceLabel Pro, NiceForm and NicePrint)

Moderators: Georges, milos, NiceLabel Support Team

Post Reply
guy
Newcomer
Posts: 3
Joined: Thu Jun 19, 2008 2:27 pm

Need to do an OUTER JOIN

Post by guy » Thu Jun 19, 2008 2:34 pm

Hi

I have a label that pulls data from 5 tables. One of the tables contains related data for only some of the labels to be printed. I would like to have the data print when it is available but leave the area blank when there is no related records in the table. I would normally do this with an OUTER JOIN in my SQL SELECT. I have been unable to get this to work with NiceLabel. Is there a solution I am missing?

I am using 5.1 and DataBase is an ODBC to a FoxPro DB.

Thanks

User avatar
lpatty
NiceLabel
NiceLabel
Posts: 60
Joined: Mon Sep 11, 2006 4:15 pm
Location: Milwaukee, WI
Contact:

Post by lpatty » Mon Jun 23, 2008 11:27 pm

Send the label to your local support group (http://www.nicelabel.com/Contact) so they can see exactly what you have created so far. This may be an easier way to troubleshoot the issue.

guy
Newcomer
Posts: 3
Joined: Thu Jun 19, 2008 2:27 pm

Post by guy » Tue Jul 01, 2008 3:44 pm

I don't believe this is a troubleshooting issue. I am asking how to do an 'OUTER JOIN'.

Something like SELECT table1.field1,table2.field2 FROM table1 LEFT OUTER JOIN table2 ON table1.field3 = table2.field3

This will return all records in table1 and populate the field2 output with any matching records in table2.

Thanks

alesp
NiceLabel Development
NiceLabel Development
Posts: 153
Joined: Tue Sep 26, 2006 1:26 pm
Location: Kranj, Slovenia
Contact:

Post by alesp » Thu Jul 03, 2008 9:44 pm

I am afraid that NiceLabel does not provide this functionality but you could use NiceForm to get data from the database and then set variable values on a label.

You can do the following in NiceForm:

1. Create new form
2. Go to "Data" menu and select "Database Access...". "Database Access Functions" dialog box will open.
3. Click the "New..." button. "Database Access" dialog box will open. Here you can browse for the database file (if you are using MS Access database or some other file based database) or define the connection to the database.
4. Once you have selected table, select "Use SQL statement" option and click the "SQL..." button. "SQL" window will open and here you can enter and test your SQL statement.
5. Once you have defined database you can use "Table" object to display data and connect it to variables that can be used to set variable data in NiceLabel.

I hope this helps.

Regards,

Ales

JOH
Enthusiast
Posts: 14
Joined: Tue Jun 01, 2010 9:39 pm

Re: Need to do an OUTER JOIN

Post by JOH » Mon Nov 14, 2011 11:12 am

Hi,

I need some advice on how to use SQL to pull data from several tables in NiceForm.

Let's assume simple example:

Table Customer:
c_id c_name
123 CUST A
456 CUST B

Table CustomerOrder:
Oid OrderNumber c_id OrderQty
1 L12 123 22
2 B44 456 92

Table Orderline:
Oid OrderNumber DelDate ArtNo
1 L12 2011-11-14 ART1
2 B44 2011-11-17 ART4

How to I "connect" to the tables I'd like to use? Via the "Name" and "Table"? How / where is the 'Sheet1$' used when using Excel?

Any examples available using Excel and or Access?

Thanks in advance.

Miso
NiceLabel
NiceLabel
Posts: 332
Joined: Tue Oct 24, 2006 6:12 pm
Location: Kranj, Slovenia
Contact:

Re: Need to do an OUTER JOIN

Post by Miso » Mon Nov 14, 2011 12:30 pm

Hello,

In NiceForm you have two options. First is to create only one database access in NiceForm and define a custom SQL statement for that database access which will pull the data from all the sheets and join it how you like with SQL statement.

Second option would be to create a separate database access to each sheet in NiceForm and use "Filter" functionality of the NiceForm database access to set the relationship between databases.

I attached two forms and a simple excel file (I used first two of your tables) to demonstrate both methods.

Hope this helps,
Regards

Miso
database.zip
(6.49 KiB) Downloaded 180 times

JOH
Enthusiast
Posts: 14
Joined: Tue Jun 01, 2010 9:39 pm

Re: Need to do an OUTER JOIN

Post by JOH » Mon Nov 14, 2011 2:04 pm

Miso,

Thanks for fast reply, brilliant.

Another one...

How would the SQL statement look like when using an ODBC connection to the database, assuming two tables, Customer and CustomerOrder?

Like this?

select CustomerOrder.OrderNumber,
Customer.c_name
from Customer, CustomerOrder
where Customer.c_id = CustomerOrder.c_id

Thx,
JOH

Miso
NiceLabel
NiceLabel
Posts: 332
Joined: Tue Oct 24, 2006 6:12 pm
Location: Kranj, Slovenia
Contact:

Re: Need to do an OUTER JOIN

Post by Miso » Tue Nov 15, 2011 7:22 am

Hello,

Yes, I believe that should do it.
Regards,

Miso

Post Reply