SELECT SQL - Some records not matched

[AndrewMozley]

I want to use SELECT-SQL to extract records from one table (of orders) with a look-up for customer name.

SELECT order.ordno, order.prod, order.qty, cust.name FROM order, cust ;
WHERE !order.shipped AND order.accno = cust.accno ;
INTO cursor t_orders

This generally works fine and gives me a cursor of outstanding orders. However I have some valid orders for which order.accno is blank, and I would still like to retrieve these orders, but leave cust.name blank in the t_orders table.

I find that these orders are not being retrieved; How can I specify that I would like such records to be selected, leaving cust.name blank, or possibly some description like “Unassigned customer”?

Thanks. Andrew

 

[Mike Lewis]

Andrew,

How about something like this:

SELECT order.ordno, order.prod, order.qty, cust.name FROM order, cust ;
   WHERE !order.shipped AND ;
   (order.accno = cust.accno OR EMPTY(order.accno)) ;
   INTO cursor t_orders

That should select the correct records.

As for setting the customer name to "unassigned customer", you could do this:

SELECT order.ordno, order.prod, order.qty, ;
PADR(IIF(EMPTY(order.accno), "unassigned customer", cust.name), 30) AS cust_name ;
FROM order, cust ;
   WHERE !order.shipped AND ;
   (order.accno = cust.accno OR EMPTY(order.accno));
   INTO cursor t_orders

You need the PADR() to ensure the cust_name is the correct length. Change 30 to whatever the length of the field should be.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

Andrew,

Looking at your post again, I wonder if the problem is not simply that you want to select orders where the account number is blank, but that those orders don't match a record in the customer table. It amounts to the same thing, I suppose, but it's not exactly what I had in mind.

If that's right, you need to do an outer join -- something like this:

SELECT order.ordno, order.prod, order.qty, ;
PADR(NVL(cust.name, "unassigned customer"), 30) AS cust_name ; 
FROM order LEFT OUTER JOIN cust ON order.accno = cust.accno ;
   WHERE !order.shipped ;
   INTO cursor t_orders

I think that should do what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[AndrewMozley]

It is pretty much as jou say Mike. My example was slightly simplified, so I had better come clean.

I want to read records from table JWIPR and look up two tables to get descriptions, but I still want to use the original JWIPR record even if either of the look-ups fail.

So I first look up table JcHead to find what job I am working on, and then use the contents of the relevant JcHead record to find the description of the contract by looking up the JCNTR table.

The code I have at present is :

SELECT JH_DESC, JH_Account, JH_Cntract, JC_Desc, JW_Cstdoc, JW_desc, JW_TrDate, JW_cstunit, JW_Qty, JW_Value ;
FROM JWIPR LEFT OUTER JOIN JcHEAD ON (JcHead.JH_Cstdoc = JWIPR.JW_CstDoc) ;
LEFT OUTER JOIN JCNTR ON (JcHead.JH_cntract = Jcntr.JC_cntract)) ;
WHERE (JW_TRREF = .zBatPrint) INTO CURSOR tBatch

This command does not actually compile - possibly I am not allowed two "LEFT OUTER JOIN"s ?

I suppose that I could create the cursor by hand and scan the table doing my own look-ups, but I feel that I ought to be using SQL!

Thanks for your help so far.

 

[Mike Lewis]

Andrew,

In what sense does the command "not compile"? What error message are you seeing?

In general, there's no problem in having two outer joins. The limit used to be nine, but I think it's even higher now.

At first glance, the only possible problem I can see in your code is the .zBatPrint in the WHERE clause. Presumably, that's a property of an object, and the whole command is wrapped in a WITH/ENDWITH. I'm not sure if you can do that. It might be worth trying the command without that clause just to see if it solves the problem.

Apart from that, the command looks like it should work.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Your having a closing bracket ")" too much. Syntax error.

Bye, Olaf.

 

[markros]

This line LEFT OUTER JOIN JCNTR ON (JcHead.JH_cntract = Jcntr.JC_cntract)) ; has one extra parenthesis. You don't need to use them at all in your query, they only obscure the statement.

 

[AndrewMozley]

Thank you very much Makros and Olaf. Sorry to have been so slow in coming back. The program did indeed work with your change. I forgot to let you know but am very grateful for your help.

Andrew.