November 29, 2007 at 7:33 pm
Hi everyone
I think I have painted myself into a corner here. I have a reporting application, with a lot of dynamic SQL in the code to allow the users lots of flexible choices. I have a WHERE clause builder that is used to provide the WHERE clause for a number of different reports. I have a couple of tables that have identical column names lets say tableA.status and tableB.status. My WHERE clause thus includes 'WHERE tableA.status = ' to avoid the ambiguous col name error that would occurr if I just used 'WHERE status = '
However, when I want to use the same WHERE clause against a view I get 'multi-part identifier cannot be bound' because in the view, tableA.status just appears as 'status'
So - is there some subtle syntax I can use to combine these two? Or do I have to go all the way back and rename the column? Or generate two side by side WHERE clauses, one with qualified column names and one without?
Suggestions welcome!
TIA
pg
November 29, 2007 at 10:23 pm
No, you can alias your columns.
So where you know the column names returned are the same in each table you can us the as to alias the column
I know your doing this in dynamic sql, but this is just the final output example
select Table1.stats as Table1Status, Table2.Status as Table2Status.
From Table1, Table2
Where ........
November 29, 2007 at 10:30 pm
in case of view, u have to use it viewname.column name then it will b ok
November 30, 2007 at 12:20 am
as you have noticed, SQLServer does not work like MSaccess :sick:
As suggested by the previous responders, using columaliasses in your view will be the only solution. (view column names must be unique).
Also keep in mind only columns in the select clause of the view definition will be available for use if you use that view !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 30, 2007 at 5:15 am
Thanks guys. I think my question could have been a bit clearer, but essentially I'm getting the message - 'Yes, you have painted yourself into a corner'
Aliasing the columns doesn't work, because the WHERE clause 'looks behind' the alias to see the actual column name - ie in my example if I were to change the SELECT clause to ' . ..tableA.status AS Astatus. . .', a WHERE clause of the form 'WHERE status = 3' would still be detected as ambiguous, and a WHERE clause of the form 'WHERE Astatus = 3' would fail with 'invalid column name'
Which leaves me with either a rename of the column in the base table followed by a lot of search and replace followed by a lot of bugs, or the horrible kludge of stripping out the table names from the WHERE clause when it's used against the view, which right now is looking like the least worst option.
pg
November 30, 2007 at 5:20 am
This is just a possible solution, but what if you aliased your table names? Like "table1 as a", "table2 as b". Could you work that into your solution by then aliasing your view as "a"?
That's probably a long shot, but without seeing everything it's the best I've got. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply