|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Need Help on Query |
| Author |
Message |
| Pete |
| |
| Group | : Members |
| Posts | : 17 |
| Joined | : 2007-01-06 |
|
| Profile |
|
| Posted : 2007-10-24 07:02:09 |
I have been running a SSIS to transfer records from Quickbooks to a SQL table. I am wondering if there is a better way. On a machine that has QODBC V8 installed ( and connected to Quickbooks Files), im trying to get invoice data direct from the QODBC into a Data Dynamic report. Against a SQL database the query is
SELECT InvoiceLine.TxnDate, InvoiceLine.RefNumber, Customer.FullName, Customer.CompanyName, InvoiceLine.BillAddressAddr1, InvoiceLine.BillAddressAddr2, InvoiceLine.BillAddressAddr3, InvoiceLine.BillAddressAddr4, InvoiceLine.BillAddressCity, InvoiceLine.BillAddressPostalCode, InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity, InvoiceLine.InvoiceLineRate, InvoiceLine.InvoiceLineAmount, TaxCode.Tax1Rate , Invoice.subTotal, Invoice.Tax1Total FROM QB.dbo.Customer Customer, QB.dbo.InvoiceLine InvoiceLine, QB.dbo.TaxCode TaxCode , QB.dbo.Invoice Invoice WHERE InvoiceLine.CustomerRefFullName = Customer.FullName AND InvoiceLine.InvoiceLineTaxCodeRefFullName = TaxCode.Name AND ((InvoiceLine.RefNumber='" & InvoiceNumber & "')) AND Invoice.RefNumber = InvoiceLine.RefNumber
QB is the SQL database, how can i execute this query direct from the linked server ( linked server QODBC ) using the Openquery command ??
Any help would be most appricated |
|
| |
i really do need help, this computer stuff is like double dutch
|
| | Top | |
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-10-24 12:02:09 |
|
|
|
| Pete |
| |
| Group | : Members |
| Posts | : 17 |
| Joined | : 2007-01-06 |
|
| Profile |
|
| Posted : 2007-10-24 18:20:07 |
Hi Tom,
I have had a read through the suggested link but am still at a loss as on how to get a joined query to work... below is a very simple example ...
Select *
From
OPENQUERY(QODBC, 'SELECT a.name, b.ClientFullName
FROM CUSTOMER a,
LEFT JOIN INVOICE b
a.name = b.ClientFullName
')
any ideas ?
|
|
| |
i really do need help, this computer stuff is like double dutch
|
| | Top | |
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-10-25 11:57:54 |
Basically you simply put your query within the OpenQuery like this:
SELECT * FROM OPENQUERY(QODBC, 'SELECT InvoiceLine.TxnDate, InvoiceLine.RefNumber,
Customer.FullName, Customer.CompanyName, InvoiceLine.BillAddressAddr1,
InvoiceLine.BillAddressAddr2, InvoiceLine.BillAddressAddr3,
InvoiceLine.BillAddressAddr4, InvoiceLine.BillAddressCity, InvoiceLine.BillAddressPostalCode,
InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity, InvoiceLine.InvoiceLineRate,
InvoiceLine.InvoiceLineAmount, TaxCode.Tax1Rate , Invoice.subTotal, Invoice.Tax1Total
FROM Customer, InvoiceLine, TaxCode
WHERE InvoiceLine.CustomerRefFullName = Customer.FullName
AND InvoiceLine.InvoiceLineTaxCodeRefFullName = TaxCode.Name
AND InvoiceLine.RefNumber="1000"') |
|
|
|
|