August 6, 2008 at 9:05 am
I'm trying to setup a report that returns account activity for vendors based on an ID that we've assigned each of them. Due to circumstances beyond my control, several of the vendors have multiple IDs. What I need to be able to do is pass the query the parameter like this; select.. where lenderid IN (XXXX,YYYY,ZZZZ,AAAA) I can't figure out how to "format" the expression to list the various values Can someone point me in the right direction?
August 6, 2008 at 2:45 pm
Is LenderID character or numeric? Are the names the same for the "duplicate" lenders? Are you using 2000 or 2005? How are the LenderId's being entered? Will the users running the report know the various LenderId's for a single Lender?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 6, 2008 at 2:51 pm
Is LenderID character or numeric? Are the names the same for the "duplicate" lenders? Are you using 2000 or 2005? How are the LenderId's being entered? Will the users running the report know the various LenderId's for a single Lender?
The Lenderid is numeric and the name is the same for the duplicate lenderids. I'm using 2005. The Lenderids are in a table called lenderid. The users wouldn't know, nor would I want them to have to know the various lenderids for any one lender. I want to present them a picklist with the lender name as the label and then pass the value(s) back to the query as a parameter.
August 6, 2008 at 2:57 pm
Is the LenderId table a mapping table to a master LenderID?
Based on your answers I would just let the user pick the Lender by name and my query would be Select .. From tables where LenderName = @LenderName.
How many rows are we talking about? If it is not a lot your performance should be acceptable and you could try out an index on LenderName if you have some issues.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 6, 2008 at 3:35 pm
It might be useful/helpful if I post one line from the query I use:
,(SELECT SUM(Revenue) FROM Orders o INNER JOIN Appraisers a ON o.AppraiserID = a.AppraiserID WHERE (o.StatusID = 1 OR o.StatusID = 4)AND ASCII(Right(filenumber,1)) = @StartDate AND o.DateCompleted < @EndDate) ) AS 'LenderXYZ Revenue'
This runs just fine. I just wanted the end-user to be able to select, say Lenderxyz from the picklist and be able to "stuff" the three lenderids into a parameter called @lenderid and then the query would take off from there. I've got about 70 lenders to deal with, and most have only one lenderid, but the ones that have multiple ids are killing me on this project.
August 7, 2008 at 3:14 pm
well, heck! I pasted that line in and it's all messed up. Disregard that last posting, I'll try and post the code again later today..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply