Need a query or transaction to export data into a set format
Im trying to export data from a database into a set format so that it can be interpreted by myob correctly. I have the exporting function working great and I have half worked out the sql query but am having trouble with how to put it all together.
The following is a simple example to illustrate what I'm trying to achieve
$sql = "SELECT (CONCAT(PICKUPID,DROPOFFID) AS ITEM), (CONCAT(Rego, Pickup, Dropoff, booker, date) AS Description) FROM booking, myob";
The issue is that I need to pull the pickup and dropoff /ID out of the myob table in reference to what is in the booking table.
booking Table Columns
myob Table Columns
- MYOBID (where MYOBID = PICKUPID OR DROPOFFID)
- Address (where Address = Dropoff or Pickup)
I am hoping to do this in an SQL query or a PL/SQL transaction but I'm having trouble getting my head around it. Any help would be appreciated (hope the questions not to confusing)
*Data in Tables***
Rego , Pickup , Dropoff , Date , booker 123, bris, sydn, 1/2/12, barry
MYOBID , Address Q, bris N, sydn
ITEM , Description QN, 123 bris sydn 1/2/12 barry
Also, I see little advantage in CONCAT'ing your fields at query time, I think you'd better leave the formatting out the output to your application layer. Of course, this is probably an overkill if all you are trying to achieve is a quick-and-dirty one-time export.