• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8143
  • Last Modified:

How to create paramaters for a query that cannot be represented graphically?

How to create paramaters for a query that cannot be represented graphically?  I have 2 querys that run super fast in SQL.  I want to use these in excel(MS QUery), when combine these two queries using Union All the query is not longer represented graphically and will not allow me to use a Parameter =?
Is there a work around for this?
  • 8
  • 5
1 Solution
Rory ArchibaldCommented:
You can't use MSQuery for that - you will have to use ADO and pass the parameters to the procedure that way. We'd need more details of the SP and the parameters to be more specific.
kgittingerAuthor Commented:
I can copy my SQL if that will help
Do you have time to help trouble shoot?
What version of Excel are you using? You can paste your SQL directly into the data properties in Excel 2007. If you're using 2007 let me know and I'll walk you through it.  On earlier versions of Excel, I have overcome this problem in the past by creating a view based on the SQL select and then using MS Query to query that view.

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

kgittingerAuthor Commented:
I am using Excel 2007
Thanks so much.
It's a bit of a work-round, but it works.....

In Excel 2007, Go to the Data tab on the toolbar.

Click on the "From Other Data Sources" tool in the "Get External Data" ribbon.

Select "Microsoft Query" (I'm sure you've done this much before...)

Select your data source in the "Chose Data Source" and press OK.

This opens MS Query.

Close the "Add Tables Dialog" - this leaves you with a blank MS Query Screen.

Click "View" on the menubar and select 'SQL'

Paste your sql in the resulting window.

Replace any variable parameters you've got with hard-coded criteria (don't worry, we'll replace these later).

Click OK.

You'll get a dialog to say that the query cannot be displayed graphically. Click OK.

You should see your results set in the query window.

Click "file" on the menubar and return the data to Excel.

Excel will prompt you for where you want the data inserted. Select a cell and press OK.

Your data (with the hard-coded parameters) should now be in Excel.

On the Data tab, click on "Connections". This will list all of the data connections you have in the spreadsheet - presumably only one will be on the list for this example.

Select the connection by clicking on its name and press the "Properties" button.

Click on the "definition" tab. Your SQL is now displayed In the "Command Text" window. You can now edit this to replace your hard-coded selection criteria with the variable "?" and the query should run as expected.

Did you know you could also point this variable to a cell in your spreadsheet?

I hope this helps.




kgittingerAuthor Commented:
Unfortunately I receive [Microsoft][ODBC SQL Server Driver] Invalid paramter number
[Microsoft][ODBC SQL Server Driver] Invalid Descriptor Index.

Yes, it this all works, I want to have the parmeter typed into my spreadsheet.

How far did you get - i.e. when did you receive the error message(s)
kgittingerAuthor Commented:
I added the ? in for my two parameters and hit OK  Thank you so much for helping me with this.  It is so close...
kgittingerAuthor Commented:
Here is my SQL  I have entered employer_code = ? in two places...
SELECT Table_Payments_And_Repayments_Ver2.employer_code, 
Table_Payments_And_Repayments_Ver2.claimreimbursement_dt AS 'Transaction _Date', 
Table_Payments_And_Repayments_Ver2.claimreimbursement_paid_amt AS 'Transaction_Amount', 
Table_Payments_And_Repayments_Ver2.paymentdetail_actualmethod_type_lookup AS 'Transaction_Type', 
Table_Payments_And_Repayments_Ver2.claimreimbursement_claim_nbr AS 'Claim_Number'
FROM TPA4.dbo.Table_Payments_And_Repayments_Ver2 Table_Payments_And_Repayments_Ver2
where (Table_Payments_And_Repayments_Ver2.employer_code = ?) and 
	Datepart(yy,Table_Payments_And_Repayments_Ver2.planyear_end_dt) = 2008

Union all
SELECT Table_Debitcard_Transactions_Ver2.employer_code, 
Table_Debitcard_Transactions_Ver2.debitcardtransaction_settlement_dt AS 'Transaction_Date', 
  when debitcardtransaction_type_lookup  = 'Refund' 
then -1 * debitcardtransaction_transaction_amt
  else debitcardtransaction_transaction_amt
END Transaction_Amount, 
Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup AS 'Transaction Type', 
Table_Debitcard_Transactions_Ver2.claim_claim_nbr AS 'Claim_Number'
FROM TPA4.dbo.Table_Debitcard_Transactions_Ver2 Table_Debitcard_Transactions_Ver2
WHERE (Table_Debitcard_Transactions_Ver2.employer_code = ?) 
and datepart(yy,Table_Debitcard_Transactions_Ver2.planyear_end_dt) = 2008 
and (Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup =' SettledTransaction' 
Or Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup='Refund' 
or Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup='Forecpost'
or Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup='SettledTransaction')

Open in new window

kgittingerAuthor Commented:
I was able to get this to work for the first occurance, but not the second (in the union all select)  WHen I add just the ? to the (Table_Debitcard_Transactions_Ver2.employer_code = ?)  in teh bottom portion , I receive unable to derive parameter information when parameter marker is a funciotn argument.  Do you have a suggestion for overcoming this?

Thanks a million!

Take a look at http://support.microsoft.com/kb/821789. This refers to an earlier problem with Excel and provides a registry tweak to fix it.

It may be worth a try.

I'm sure I don't have to tell you to be very careful when editing the registry......

kgittingerAuthor Commented:
This is what I asked for although I was unsuccessful in implementing with a union query

Thanks for the information!
Thanks for the points but I don't think you should give up yet.

You could use your union query (with no parameters) to create a view of your data. You could then query this view from Excel - with the ability to pass it the parameter (as I think if I've read your SQL correctly - there will now only be one on 'employer_code') as described above. I think it's worth a try....
kgittingerAuthor Commented:
Great. I will try to create the view!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now