Need help on construct a Select SQL 2

[Ernesto_Paez]

Hi everyone,
Here is my issue.
i have created a form with a txtbox and search button, so as a user, i want to type a "value in that txtbox" to fetch the records for custno i typed in the txtbox, so i have been trying to figure out the needed "select sql" in the search button but it is complicated for me, so let me give you some insights.
tables names
arcust04.dbf (fields are custno, company,contact, address1. address2, state, zip country, phone and so other fields)

arcadr04 .dbf (fields are custno, company,contact, address1. address2, state, zip country, phone and so other fields)

1-So what i need to accomplish is type a custno in the txtbox and search for a "custno" in "arcust04.dbf first" and find if that "custno" is also in "arcadr04.dbf" if found in both tables, we need to pull from "arcadr04" fields values "custno, company,contact, address1, address2, state, zip country, phone" then show them in a grid, so it could be one one record or more under the same custno in "arcadr04.dbf" , why i need to pull from "arcadr04" ? because this table contains the "Ship to" info based on the fields listed above.

2- if the custno is only found in arcust04, then we need to put in the grid the "company,contact, address1 and the rest of the field values mentioned in 1, from "arcust04".

3- if the custno we are fetching is not in "arcust04" and it is "arcadr04" then we need to pull the needed fields values from arcadr04 in the grid as mentioned in 1
Note: the common fields in both tables has the same structure but both tables do not have all the the same fields, only those mentioned above.

can anyone please help on constructing this SELECT
Thanks so much
Ernesto

 

[IlyaRabyy]

Not sure a single one SQL SELECT statement can be constructed directly ("by frontal attack" as they say ;-)) in this case, but what about using macro-substitutions? (Not the best practice by many's pinion, but works nonetheless.)
Provided you've validated the User's data entry in that your text box, it could be something like this (not tested)

LOCAL lcCustID, lcFrom, lcFlds, lcWhere
lcCustID = THIS.txtBox.Value
lcFrom = IIF(SEEK(m.lcCustID, "ARCADR04", "CustID"), "ARCADR04", "ARCUST04")
lcFlds = "custno, company,contact, address1, address2, state, zip country, phone" + IIF(lcFrom=="ARCADR04", ", shipto", "")
lcWhere = lcFrom + ".CustNo = '" + lcCustID + "'"

SELECT &lcFlds FROM &lcFrom WHERE &lcWhere INTO CURSOR C_CUSTOMER NOFILTER

or something along these lines.

HTH.


Regards,

Ilya

 

[Ernesto_Paez]

Hi Ilya,
Thanks a lot for the effort really appreciated.

1-but the values that are showing for fields "company contact address1 and so on" are from ARCUST04 and should not be because i entered manually from the command window custno="631" and this custno is found in both arcust04 and ARCADR04 tables, so if the custno is in both tables, then we need the values for fields "company contact address1 and so on" from ARCADR04.dbf and it is not happening.

so if the custno it is not found in ARCADR04.dbf, then we need to obtain values for fields "company contact address1 and so on" from ARCUST04

if the custno value it is not in ARCUST04.DBF then we should find it on ARCADR04.dbf and then pull the fields values "company contact address1 and so on" from ARCADR04.dbf
Thanks a lot

 

[Olaf Doschke]

Actually it's a case for renormalizing your data. When you have attributes for some customers in place A and for others in place B, establish a place C where you pull this together and then remove that from both place a and b.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Ernesto_Paez]

Hello Olaf,
These tables belongs to an accounting software that was done in vfp 5.0, the account software is SBT PRO 5.0 so ARCUST04.DBF it is to mantain the custnos, and several other fields but table ARCADR04 is to mantain the custno and then several fields including in those common fields the the addresses where the product will be "SHIP TO" , in other words they use this last table to have the same custno as many SHIP TO address the customer has.
Thanks a lot
Ernesto

 

[mjcmkrsr]

Hi,

You may try something like below

LOCAL lcCustID

lcCustID = THISFORM.txtBox.Value

IF INDEXSEEK(lcCustID, .F., "ARCUST04", "CustID");
     SELECT T1.custno, T1.company ... NVL(T2.ShipTo, "NoShipping") ;
          FROM ARCUST04 T1 ;
          LEFT JOIN ARCADR04 T2 ON T1.CustId == T2.CustID ;
          WHERE T1.CustId = lcCustId ;
          INTO CURSOR CSR_CUSTOMER NOFILTER 

ELSE
     SELECT T1.custno, T1.company ... T1.ShipTo ;
          FROM ARCADR04 T1 ;
          WHERE T1.CustId == lcCustId ;
          INTO CURSOR CSR_CUSTOMER NOFILTER 

ENDIF

hth
MarK

 

[Olaf Doschke]

I'm sorry you're not under control of this as it's a database as given by the SBT application. I know it's often used by VFP developers as you have easy hands on the DBFs, it's quite famous, nevertheless, this overlap in fields cries for a third table and each table pointing there.

If I'd manage customers with their address, an address automatically is something complex enough in itself, that it'd stretch several tables and then have one ID of its main record in the customer table as his main residence address. Delivery or shipping addresses are not different in composition, just in their meaning. So there'd be an address type. And so maybe I'd even put all addresses outside of the customers main record and have a customeraddresses n:m cross-reference table with customerid, addressid, addresstype. And a customerdefaultshippingaddresses table which has the one customer, addressid combination for each customer, that you're after.

So when you register, the first address you'll enter will be your residence adddress that's legally binding for invoices and then you may use exactly that as shipping address, but more important, as long as I only know that it'd be the one in customerdefaultshippingaddresses and uf you give an alternative shipping address that'll add in as one row of customeraddresses. And when you want that to become the derfauilt shipping address that'd also update your customerdefaultshippingaddresses record. And in all of this, addresses would be their own table structure.

Anyway, that's not the route you can take, therefore this becomes an unnecessarily complex query.

Now to the way you describe what you want, you'd want to involve one more table, like tha main customer table. Because anytime you want to know some info about an entity, you start in the main head table of that entity that has a guaranteed record of the entity once the database knows. As you say records could exist in only arcust04 or arcadr04, that makes both of these detail tables with optional data and therefore you can't solve your problem only querying with them, even though you only pick fields of some of them.

This really is straightforward thinking. So let's assume the main customer table of SBT is cust.dbf then you need to start from cust and left join arcadr04 and arcust04, you'd take NVL(arcadr04.field, arcust04.field) for all the fields you wnat, as you prioritize arcadr04 and only want to take arcust04 when there is no match in arcadr04.

The way you thought about that is way too complex, first you say you only want arcadr04, if there is the same custno in arcust04, which would ask for an inner join, but then you say there are cases there is no arcust04 and only arcadr04 for a custno. All you're saying with this is that neither table is the main customer table, then don't start in any of these two, start in customers. You want to join both detail tables on the same level, bot joins start in customer, and then you want to prioritize arcadr04. That's all there is to this. Now the only problem is when you have n>1 records in arcust04 and m>1 records in arcadr04 you get all n*m combinations of address data, which makes this structure so unfortunate. I have the feeling arcust04 will either have 0 or 1 record per customer, so the only problem would be m records in arcadr04 shipping addresses. I'd look for a field that tells you which of these is the default, alternatively maybe the newest of them, so you finally end up with only one address.

Bye, Olaf.




Olaf Doschke Software Engineering

https://www.doschke.name

 

[TheProgrammerGuy]

Can't see why you need SQL... Just relate arcust04 into arcard04 and use your EOF() function to check for records in each file. To check for records in arccar04 not in arccust04 you will need to open arccard04 in 2 work areas. For ex:

*-- Setup in form
USE arcard04 ORDER custno IN 0
USE arcard04 ORDER custno AGAIN IN 0 ALIAS arcard04_2
SELECT 0
USE arcust04 ORDER custno
SET RELATION TO custno INTO arcard04

*-- In your textbox Valid (this would not work as interactive because you will want to type in the entire number before searching)
SELECT arcust04
SEEK ALLTRIM(this.value)
IF FOUND()
  IF EOF("arcard04")
    *-- It is not in arcard04, do whatever
  ELSE
    *-- It is in arcard04, get your data
  ENDIF
ELSE  && IT IS NOT IN ARCUST04
  SELECT arcard04_2
  IF SEEK(ALLTRIM(this.value))
    *-- It is in arcard04 but not in arccust04
  ENDIF
ENDIF

There will be any number of other ways to do this, but my point is that you are over-complicating the issue.
Keep it simple.

Larry (TPG)

 

[Mike Lewis]

Sorry for the lack of indentation, the "Submit Post" button seems to remove my spaces.

It's not the Submit Post button that does that. It's because you didn't format the text as program code.

When adding program code to a forum post, always surround it with [ignore]

[/ignore] tags. Either type the tags manually, or use the Code button on the toolbar. That way, your code will be much easier to read, because it will be mono-spaced and - importantly - all the indentation will be preserved.

To prove it, look at your own code, Larry, and compare it with that of Mark and Ilya (above).

Would all forum uses please take note of this. It will make all our lives that little bit easier.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I second Mike. And Larry, when you edit your post, you will see your spaces/tabs again, they are only lost when HTML is rendered as paragraphs. So when you then select your code and click on the code icon it'll surround your code with code tags [ignore]

yourcode

[/ignore] and then it'll stay formatted.

And on the topic, yes, basically just wanting to find the one or other record would only need two seeks, but I assume as the final goal is to populate a grid, this isn't just about one customer. Overall you can work with relations from the main table to further tables and display columns or the NVL()-expression I gave as control sources, too, that'll just mean they become readonly. And expressions that are more than a simple field name should be put in brackets, ie. you open customer, arcadr04, arcust04 and set relations, set the grid recordsource to customers an grid columns to expression, that can be NVL, that can also be IIF(!EOF('arcadr04'),arcadr04.field,arcust04.field) to prioritize arcadr04, if found.

But you need a customer table.

I assume so, but if SBT really has a design where the main data of a customer is not in a single table but either in one or the other only, then you could still simply create a cursor with the union of all custnos as your own main parent table to bind to the grid and then pull in the one or other or both and more tables with relations.

I'd prefer a single cursor, created readwrite and having IDs in the cusrsor you could even allow editing and later storing changes back via update or replace, scatter/gether, whatever. In the best case when the grid cursor is a view cursor of course with TABLEUPDATE().

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

There, it obviously worked.

Also, another very simply approach, would be to UNION the two tables to list all addresses you find in both.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Ernesto_Paez]

Hello Guys,
Thanks for all your suggestions
here is what i did
in form load event
i have this

select custno, company, contact, address1, address2,city, state,zip,;
	country,phone From arcadr04 Where .F.	Into Cursor commoncustn Readwrite

in the click event of search cmdbutton then i add this

Local lccustno ,lnnrecs
lccustno = Alltrim(Thisform.txtcustno.Value)

If Empty(m.lccustno)
	Messagebox("You Need To Enter a Custmer Number")
	Thisform.txtcustno.SetFocus
	Thisform.txtcustno.Value=""
	Retu
Else

	* Get from arcad04
	Select custno, company, contact, address1, address2,city, state,zip,;
	country,phone From arcadr04 ;
	where custno = m.lccustno;
	into Cursor commoncustn Readwrite
	If _Tally <= 0  && no match, get from arcadr04
		Select custno, company, contact, address1, address2,city, state,zip,;
		country,phone From arcust04 ;
		where custno = m.lccustno;
		into Cursor commoncustn2 NOFILTER
		If _Tally > 0
			Select commoncustn
			Append From Dbf('commoncustn2')
			Use In Select('commoncustn2')
			Go Top
		Else
			Messagebox('No Match Found!',64,'Oppppssss!')
			return(.F.)
		Endif
	Endif

by the way i really appreciate all your suggestions and then it took me to do this.
in a trail and error ,i just wanted to use "SELECT" instead of using the "SEEK and SET ORDER TO" ,as i am sure you guys prefer that too, unless it is not possible, at the same time,i am not an expert at all, i prefer to ask you as i know you can guide me better,anyway it is working as expected, as Olaf, says, yes the OUTPUT could be one or more records, never 0 record unless the custno typed is incorrect or do not exist in either table.
if you still believe i took the wrong path, please advise me.
Thanks to all that participated.

 

[Olaf Doschke]

You simply could do a UNION, as said. And as you already create your grid curos in the form load, you can insert data into it, instead of recreating it (recreating a cursor poses problems with the grid).

Insert Into commoncustn ;
Select custno, company, contact, address1, address2,city, state,zip,;
	country,phone From arcadr04 ;
	where custno = m.lccustno ;
Union All (;
Select custno, company, contact, address1, address2,city, state,zip,;
	country,phone From arcust04 ;
	where custno = m.lccustno ;
)

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Ernesto_Paez]

Olaf,
I can try that too, actually i decided remove the grid and just added the needed txtboxes for the fields in the "select" and added a navigation buttons, like next and previous and so on so the form opens, the use type the custno, once found the records fill the txtbox if reccount>=2, then i make visible the navigation button, otherwise only once record found and displayed and not navigation button, so i can apply your code.
let me know if i am missing something in what i posted.
Thanks