November 6, 2018 at 6:57 pm
In my code below I have Invoices.InvID = InvoiceTotals.InvoiceID. Do the column names absolutely have to be the same name? In this case, they have the same data types and same values but the name is different. I thought this would still work. Is the error caused by joining columns with two different names or is my error due to something else?
USE
[H+Active]
SELECT *
FROM InvoiceTotals
INNER JOIN Invoices ON Invoices.InvID = InvoiceTotals.InvoiceID
INNER JOIN Customers ON Customers.CustomerID = Invoices.CustomerID
WHERE InvoiceTotals.InvoiceTotal > 150 AND Invoices.Date BETWEEN '2-1-2015' AND '2-28-2015';
November 6, 2018 at 7:03 pm
Columns can have different names. What is the text of the error you are seeing?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 7, 2018 at 2:46 am
I agree with Phil here, I don't see anything wrong with your query. You can compare columns regardless of their names and datatypes (just that with the latter implicit conversions will happen).
For example, the following statements will work fine:
Of course, I don't recommend comparing objects of different datatypes; doing so will make your query non-SARGable (this means that any indexes you have won't be used, thus can/will have a significant impact on performance).
I wouldn't, however, be surprised if the error you're getting in a conversion error, based on your question. Maybe InvID or InvoiceID is a int and the other a (n)varchar? If so, an expression which equates to something like 1 = 'a' is going to fail. That's because 'a' can't be converted to an int (the varchar is implicitly casted to an int, as int has a higher Data Type Precedence than varchar). Perhaps this is what's happening?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 7, 2018 at 7:25 pm
Thom A - Wednesday, November 7, 2018 2:46 AMI agree with Phil here, I don't see anything wrong with your query. You can compare columns regardless of their names and datatypes (just that with the latter implicit conversions will happen).For example, the following statements will work fine:
USE Sandbox;GOCREATE TABLE dbo.Table1 (ID int IDENTITY(1,1),SomeVar varchar(10));CREATE TABLE dbo.Table2 (ID int IDENTITY,fID varchar(5),OtherVar varchar(15));GOINSERT INTO dbo.Table1 (SomeVar)VALUES('asdkjadl'),('sdfjkgajk');INSERT INTO dbo.Table2 (fID,OtherVar)VALUES('1','asdklfgjsadklf'),('1','sdfkhjgsdfhjk'),('2','sdafjksdf#');GOSELECT T1.ID,T1.SomeVar,T2.ID,T2.fID,T2.OtherVarFROM dbo.Table1 T1JOIN dbo.Table2 T2 ON T1.Id = T2.fID;GODROP TABLE dbo.Table1;DROP TABLE dbo.Table2;Of course, I don't recommend comparing objects of different datatypes; doing so will make your query non-SARGable (this means that any indexes you have won't be used, thus can/will have a significant impact on performance).
I wouldn't, however, be surprised if the error you're getting in a conversion error, based on your question. Maybe InvID or InvoiceID is a int and the other a (n)varchar? If so, an expression which equates to something like 1 = 'a' is going to fail. That's because 'a' can't be converted to an int (the varchar is implicitly casted to an int, as int has a higher Data Type Precedence than varchar). Perhaps this is what's happening?
Here is my error message:
Msg 207, Level 16, State 1, Procedure InvoiceTotals, Line 6 [Batch Start Line 0]
Invalid column name 'InvoiceID'.
Msg 207, Level 16, State 1, Procedure InvoiceTotals, Line 9 [Batch Start Line 0]
Invalid column name 'InvoiceID'.
Msg 207, Level 16, State 1, Procedure InvoiceTotals, Line 3 [Batch Start Line 0]
Invalid column name 'InvoiceID'.
Msg 4413, Level 16, State 1, Line 7
Could not use view or function 'InvoiceTotals' because of binding errors.
November 7, 2018 at 8:47 pm
Error looks quite clear.
----------
Could not use view or function 'InvoiceTotals' because of binding errors.
----------
one of your view is invalid because the underlying objects changed - possibly the invoicetotals view.
you will need to identify the issue and fix it - may be as easy as regenerating the view or a bit more complex.
doing individual selects from each of the views used on your original query will help identify which one(s) have the problem.
November 8, 2018 at 1:43 am
frederico_fonseca - Wednesday, November 7, 2018 8:47 PMError looks quite clear.
----------
Could not use view or function 'InvoiceTotals' because of binding errors.
----------one of your view is invalid because the underlying objects changed - possibly the invoicetotals view.
you will need to identify the issue and fix it - may be as easy as regenerating the view or a bit more complex.
doing individual selects from each of the views used on your original query will help identify which one(s) have the problem.
Considering that the only object which has InvoiceID referenced is for the object InvoiceTotals (which is also the object referenced above) seems that that's the problem object. Perhaps you have a SELECT * in there or something, instead of listing your columns, and the definition of the table has changed?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 8, 2018 at 3:27 pm
michael.leach2015 - Tuesday, November 6, 2018 6:57 PM
You need to get a good book on basic data modeling. A data element in a valid data model will always have the same name, no matter where it appears. This is a direct result of the most fundamental principle of formal logic; the Law of Identity. This law is usually stated as "to be is to be something in particular; to be nothing in particular or everything in general, is to be nothing at all." Or the version you see if you are an Ayn Rand fan, is simply "A is A."
Somewhere in your documentation, you should have a thing called a data dictionary to find all of your data elements, their definitions, and their representations. The ISO 11179 naming standards in the metadata committee standards should be used.
However, SQL schemas do not have to be well-designed. It's really nice if they are, there is no programming language on earth that has the "should have been" function in it. Without seeing any DDL, or your data dictionary, this would have to be some other kind of error.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 9, 2018 at 5:43 am
jcelko212 32090 - Thursday, November 8, 2018 3:27 PMmichael.leach2015 - Tuesday, November 6, 2018 6:57 PMYou need to get a good book on basic data modeling. A data element in a valid data model will always have the same name, no matter where it appears. This is a direct result of the most fundamental principle of formal logic; the Law of Identity. This law is usually stated as "to be is to be something in particular; to be nothing in particular or everything in general, is to be nothing at all." Or the version you see if you are an Ayn Rand fan, is simply "A is A."
Somewhere in your documentation, you should have a thing called a data dictionary to find all of your data elements, their definitions, and their representations. The ISO 11179 naming standards in the metadata committee standards should be used.
However, SQL schemas do not have to be well-designed. It's really nice if they are, there is no programming language on earth that has the "should have been" function in it. Without seeing any DDL, or your data dictionary, this would have to be some other kind of error.
Eh, Shakespeare might disagree.
What's in a name? That which we call a rose
By any other word would smell as sweet;
But I know what you're talking about. I'm dealing with a legacy system. In every table, the column that stores the provider number has a slightly different name. Most of the names contain the letters "PRV", but then letters are added to the front or back to create the column name. It looks like maybe the added letters might have something to do with the table name. (Perhaps the original designer didn't know about multi-part naming convention and wanted to make each column name unique for queries and joins? Ugly.)
And a data dictionary is often a desired but unavailable luxury. Some of our systems were created by a long-time employee who knew everything about it, but didn't write much of it down. When he was downsized out, the "documentation" exited the building within his brain.
November 9, 2018 at 6:55 pm
frederico_fonseca - Wednesday, November 7, 2018 8:47 PMError looks quite clear.
----------
Could not use view or function 'InvoiceTotals' because of binding errors.
----------one of your view is invalid because the underlying objects changed - possibly the invoicetotals view.
you will need to identify the issue and fix it - may be as easy as regenerating the view or a bit more complex.
doing individual selects from each of the views used on your original query will help identify which one(s) have the problem.
You were exactly right. I changed a column name in one of the tables that the view queried. Apparently the name change didn't flow down to the view definition. So I entered the design screen for the view, updated the column name in the middle section, then updated the column name in the SQL code in the bottom section.
Thank you.
November 9, 2018 at 10:16 pm
InvoiceTotals.InvoiceID
is what the message indicates the problem is. If this is a table the column name could have been changed or dropped. Or if a view then the same no longer including the column or by aliasing with a new name.
Try your Select but without joining to this table and see if the rest works.
Then do a select top 3 * from just this table to see a sample of the data and full columns.
You can also try the function EXEC sp_describe_first_result_set @tsql =N'Select * from InvoiceTotals';
----------------------------------------------------
November 10, 2018 at 8:30 am
michael.leach2015 - Tuesday, November 6, 2018 6:57 PM
And a data dictionary is often a desired but unavailable luxury. Some of our systems were created by a long-time employee who knew everything about it, but didn't write much of it down. When he was downsized out, the "documentation" exited the building within his brain.
We used to call this "job secure programming" :crazy:
Please post DDL and follow ANSI/ISO standards when asking for help.
November 10, 2018 at 1:42 pm
jcelko212 32090 - Thursday, November 8, 2018 3:27 PMmichael.leach2015 - Tuesday, November 6, 2018 6:57 PMAnd a data dictionary is often a desired but unavailable luxury. Some of our systems were created by a long-time employee who knew everything about it, but didn't write much of it down. When he was downsized out, the "documentation" exited the building within his brain.
We used to call this "job secure programming" :crazy:
I am new to SQL so I'm still unfamiliar with many of the terms and concepts. What to you mean when you say:
1. Post DDL
2. Follow ANSI/ISO standards when asking for
3. In an earlier post you mentioned data dictionary. Where do you find the data dictionary? Or do you create it? If so, how?
November 10, 2018 at 4:00 pm
I am new to SQL so I'm still unfamiliar with many of the terms and concepts. What do you mean when you say:
1. Post DDL
SQL has three sub- languages. The DML is the data manipulation language; it does queries and some other things. The DCL is the data control language; it defines the privileges on the schema that each user has. The DDL is the data definition language; it creates the tables views and other schema objects. Without DDL, we have no idea what the keys are, the datatypes and constraints on the columns or the relationships among the tables or anything else we need to write queries.
2. Follow ANSI/ISO standards when asking for
The SQL programming language is defined by ANSI/ISO standards, pretty much like every other programming language these days. As a professional, you want to know the standards of your trade.
3. In an earlier post, you mentioned data dictionary. Where do you find the data dictionary? Or do you create it? If so, how?
You create a data dictionary. It's a standard programming technique that predates SQL and relational databases. Basically, you document all of the data elements, their names, their constraints and everything else you need to know about them as part of your system. Would you build an airplane without a blueprint?
Please post DDL and follow ANSI/ISO standards when asking for help.
November 11, 2018 at 1:59 pm
ANSI/ISO standards have absolutely nothing to do with how to post a question. They also frequently don't have much to do with high performance code because, if you follow them, you leave out the ability to use the proprietary code "extensions" that many RDBMSs contain and many of them are nasty fast compared to using just ANSI/ISO prescribed code and methods.
Folks will also jump up and down and scream about "portability" of code being paramount. Like all else in this business, "It Depends". I only migrated code one time (Oracle to SQL Server) and even though the Oracle code didn't come close to being ANSI/ISO compliant, it didn't take long to migrate the code. Why would I even think of forsaking the power built into the code "extensions" of SQL server for the sake of "portability"? If you want "portability" use and ORM for C.R.U.D. and use stored procedures (whatever the RDBMS may be) to do any heavy lifting and there's a good chance that you won't ever have to port those to another system. If you do... take advantage of that new system by using what it has to offer.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2018 at 6:01 pm
Jeff Moden - Sunday, November 11, 2018 1:59 PMANSI/ISO standards have absolutely nothing to do with how to post a question.
This is what was confusing me. I'm sure standards apply to writing code, but I don't see how to apply that to posting a question. As far as applying those standards to writing code, what does that exactly involve? Does that mean writing statements and clauses on the same line or separate lines? Does also refer to indenting with a certain number of spaces? What exactly does this involve? So far I have not read anything that said to use ANSI/ISO standards when writing SQL code. Instead, the videos I watch just show what to do, and then I do it.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply