473,320 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Ambigiuous column name

347 100+
I'm attempting to do two things in a query, first convert a field to a more readable format and then doing a join so that I can data within a certain time frame. Here's the query that I have so far:

Expand|Select|Wrap|Line Numbers
  1. select moncalladd.opname, moncalladd.schedname, moncalladd.adddate, moncalladd.initials, dateadd(n,moncalladd.Timestamp, '12/31/1899')-([timestamp]/1000)/60/1440 +1 as date
  2. into temptable3
  3. from mdr.dbo.moncalladd inner join mdr.dbo.moncalldelete
  4. on moncalladd.schedname = moncalldelete.schedname
  5.  
what I'd like to do is to add one line to this

where date >= (this value will be a variable that I'll be passing from a web page)

Can someone please assist?

Thank you
Mar 21 '11 #1

✓ answered by ck9663

If the offended column is the one you are trying to add in the WHERE clause, just add an alias name on it.

Happy Coding!!!


~~ CK

5 1737
ck9663
2,878 Expert 2GB
If the offended column is the one you are trying to add in the WHERE clause, just add an alias name on it.

Happy Coding!!!


~~ CK
Mar 22 '11 #2
dougancil
347 100+
Ck,

I actually worked through that and now have a formatting issue. I'm trying to convert my dates in my query from this format:

yyyy/mm/dd to dd/mm/yyyy

Here is the query I have now:

select moncalladd.opname, moncalladd.schedname, moncalladd.adddate, moncalladd.initials, dateadd(n,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440 +1 as oncalldate
from mdr.dbo.moncalladd inner join mdr.dbo.moncalldelete
on moncalladd.schedname = moncalldelete.schedname and moncalladd.timestamp= moncalldelete.timestamp
WHERE dateadd(n,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440+1 >= '2011-03-11'
Mar 22 '11 #3
ck9663
2,878 Expert 2GB
For formatting, just do it on your front-end. If it is really necessary, here, read this.


Happy Coding!!!

~~ CK
Mar 22 '11 #4
dougancil
347 100+
I actually think that I'm going to just force users via regular expression to enter dates a specific way.
Mar 22 '11 #5
ck9663
2,878 Expert 2GB
Yes :)

As a rule of thumb, formatting is always better handled in the front-end. Although I am also guilty of doing that in the back-end only because some functions are readily available in sql-server and will take a lot of coding in the front-end :)

Happy Coding!!!

~~ CK
Mar 23 '11 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Shino | last post by:
Hi, Can anyone help with this error: "ORA-00904: invalid column name"? Thanks! SQL> create view PPFa as 2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID AS StudID 3 FROM...
4
by: mokles | last post by:
Hi All, I am trying to change column name on an existing table. I am using SQL Server 7. As the table is quite big, it is taking quite long time to do it. By the way I could change the column...
7
by: CharlesEF | last post by:
Hi All, I have run into another problem that is eating my lunch. Should be simple but I am having one heck of a time. Please look at this SELECT statement: SELECT FROM States WHERE ] =...
10
by: Colleyville Alan | last post by:
I am trying to turn a short and fat (63 columns) table into one that is tall and skinny (7 columns). Basically, I am trying to create a "reverse crosstab" using a looping structure in VBA along...
6
by: Thomas Mueller | last post by:
Hi, I tried to install phpopenchat but I can't create this table: poc=> CREATE TABLE poc_user_account ( poc(> USER varchar(255) NOT NULL, poc(> PASSWORD varchar(255), poc(> ...
4
by: Ying Lu | last post by:
Hello, Under mysql, we have "desc tablename" to get the detail information about a table. My question is about to get column name, and column type for a specific table under PostgreSQL through...
1
by: imauser | last post by:
I have a database(PostgreSQL) table(about 70k rows).I am developing an ASP webpage and there is a list-box on it which contains the name of the columns of that table. User selects the column name...
1
by: Amaling | last post by:
Hi, I am using pgSQL as database with JSP. I have given coomets to each column in table. I have retieved the column comment using : "select col_description('table_name'::regclass,column+index)"...
5
by: Anne | last post by:
Hello! Here is the statement in question: --STATEMENT A SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM dbo.sourceTable) The problem with Statement A is that 'colX' does not exist...
2
by: Big Daddy | last post by:
For example, if I have a DB table called DownloadPoints with a column named DownloadPointNo, then SqlMetal will create a file with a class called DownloadPoints with an accessor named...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.