Use The Reference Column Name In The Same SELECT Querry
Sep 17, 2007
Hi,
I am writing the following querry.
Code Snippet
Select dbo.fn_EnGiftId(116) AS 'EnGiftId', dbo.fn_DeGiftId(''+ EnGiftId +'') AS 'DeGiftId'
But the above querry giving me the following error When i am executing the querry:
Code Snippet
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'EnGiftId'.
The scenario is I have created two functions 1st is for encryption of GiftId and 2nd is for Decryption of the Encrypted Giftid.
But in the same select querry i want to use the name of column which i have given to the encrypted data in the selct querry i want to use that same column name in the decryption function uin the same querry.
When i am executing the below querry it gives me the required output. So Why not above querry.
Code Snippet
Select dbo.fn_EnGiftId(116) AS 'EnGiftId', dbo.fn_DeGiftId(''+ dbo.fn_EnGiftId(116) +'') AS 'DeGiftId'
i d't want to write the encryption function again and again. Is theer any way to avoid this.?????
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
I have two database on same server. I want write a querry, witch qering the data from 1st database, than create a new table on the 2nd database. But the in clause after the into clause permanently generate error.
Here is the query€™s little part: Select €¦.. into Backup in €™Test.mdb€™ from €¦
I try wrote other ways in €™Test€™ in €™dbo.Test€™ in dbo.Test but none of them work
its only work, when i not want pass for the second database
Hi.every one. I have a problem when I use this querry select replace(Title,'.','')AS Title1, ID from tbl_Sim Where Title1 LIKE '%377%'
And here is my record Title : 0988.613.775 ID : 2
and here is the error: Invalid column name 'Title1'. Can any one show me how to run this querry, I want to TRIM the 'Title' before it can be compared with another condition I am a beginner. Thanks alots!
Hello. Hope you are all well and having a good day. I've got a problem i would like you to help me out with. My company has got a client who sells meat and my senior tech lead has customized the cart to fit their needs and have created a despatch table with a "simple asp form" that allows them to scan their products in with a wi-fi scanner.
The despatch table has the following fields: 1. OrderID 2. Product 3. Quantit 4. sellbydate 5. traceabilitycode and 6. Weight Question 1: how do i create a form in asp that allow a user to view the order no, product and quantity celibate,traceabilitybycode and Weight after scanning the details into the simple form created? Question 2: How can i create an asp form that allows users to search for the Order that populates the orderid, Product, quantity? Question 3: How can i create an asp for that allows users to search for Product with orderid,traceabilitycode and quantity? Question 4: How can i create an asp form that allows users to Trace: Orderid, traceabilitycode, sellbydate and Quantity?
Please find below the source code of both the: despatchLotsRowsSimpleForm.asp that allow them to scan details into the table and the despatchLotsExec.asp:
The despatchExec.asp Page <% ' WebShop 3.0x Shopping Cart ' Developed using ASP ' August-2002 ' Email(E-Mail address blocked: See forum rules) for further information ' (URL address blocked: See forum rules) ' Details: add e-mail to newsletter list %>
I need to check the value of a column being pulled from an embedded select in the WHERE clause of a script but can't figure out how to do it. The script looks like this:
SELECT LIST_ID, NAME, ADDRESS (SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWER
FROM VIEW_LIST
WHERE ANSWER = 'No'
The syntax above works in Oracle but in SQL Server I receive the following error: "Invalid column name 'OHIP'."
My duplicate check runs against the entire column. There might be another test field that has the same value, and that might be valid, so im trying to make sure that when i do the check, i am checking only against the serial number field and not all test fields.
Hi,I have a DataGrid whose DataSource is an SQLDataSource. This SQLDataSource references a SQL Server table with an Identity column, named Id. The SQLDataSource has generated an Update statement for me, but this only works if the Id column is selected for display in the DataGrid, and has Visible = True. If not, then the @original_Id parameter to the generated Update statement has a value of Nothing, and the Update has no effect. So is there a way to enable the Update statement to work, without the user having to see the Id column in the DataGrid? Many thanks,Keith.
in the script task component - i need to refer to Row.Column9.ToString() but the Column9 is coming from a variable. meaning - at run time it can be Column9 or Column7 at other runs. how can i write Row.ColumnName.ToString() in a dynamic way? (aka that the ColumnName is dynamic and not hard coded?)
Row groups: Province Column groups: Project Type Values: Â Count of # of Projects, Sum of Amount ($)
What this displays is the following. How do I go about calculating what's highlighted in yellow? It is Amount of OneProjectType / Amount of TwoProject Type.
I have two tables shown below and I wish to add a calculated column to Table 1.
Table1                                     Table2 ID   Activity                            Activity 1    Activity 2   Activity 3   etc   etc  etc  Total 1   Activity 1                          values        values      values                                 Total value 2   Activity 2                          values        values      values                                 Total value 3   Activity 3                          values        values      values                                 Total value etc
I want each row in the new column to show the corresponding activity divided by the sum of the total. So in the row with Activity 3 I want the formula SUM(Table2[Activity 3])/SUM(Table2[Total]). Also the number of activities will vary.
I am actually wanting to calculate a more complicated formula which calculates the correlation .
How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.
Just encountered something that I wasn't expected, in that a user who has an explicit deny on a column in a table was able to select it when referenced through a view in a schema they have the SELECT permission on. This seems to me to go against the principle that DENY overrides everything when it comes to permissions? Is this how it's meant to work?
Code is below:-
--create test user CREATE USER TestDenyOnViewUser WITHOUT LOGIN GO
--create test schema (authorization dbo - same owner as dbo schema so ownership chaining will apply) CREATE SCHEMA TestDenyOnView AUTHORIZATION dbo
Hello, I have created SSIS package programmatically, I want to add Lookup transformation, How can I add column from reference dataset to the transformation? I have try to add new output column but it gives me an validation error, I write following coed to add new output column to lookup. IDTSOutputColumn90 outputColumn = this.lookup.OutputCollection[0].OutputColumnCollection.New(); outputColumn.Name = col.Name; outputColumn.Description = "Staging table output"; outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent; outputColumn.ErrorOrTruncationOperation = "Copy Column"; outputColumn.SetDataTypeProperties(col.DataType, col.Length, col.Precision, col.Scale, col.CodePage);
Please suggest other way to add column from reference dataset to transformation output.
On my aspx Web page, I want to delete a member from database table 'tblMember', but if this MemberID is used as FK in another table, I want to display a user friendlier message like "You cannot delete this member, ....." I am using Try, Catch blocks in my Web Page.
Currently it display this message: "DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_..._....' The conflict occurred in database '...', table 'tblMembers', column 'MemberID'. The statement has been terminated. "
So how should I precisely trap this error? Does anybody know what Exception is it? or what error number in SQL server?
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section TextReader = new system.io.streamreader(" file name") In the CreateNewOutputRows: dim nextLine as string nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
Hello. I know, you think I'm totally clueless, and you're right. SQL is the most confusing thing for me but I must learn it. So, I am running a query to find out the name of the person who sold the most stoves with the least color purchased. I have ran that query and it works . My problem is that I have to add to that query another column that counts the number of stoves sold, instead of having them displayed one by one. My query is as follows:
SELECT EMPLOYEE.Name,STOVE.Color FROM INV_LINE_ITEM, STOVE, EMPLOYEE, INVOICE WHERE INV_LINE_ITEM.FK_Stovenbr=STOVE.SerialNumber AND INV_LINE_ITEM.FK_InvoiceNbr=INVOICE.InvoiceNbr AND INVOICE.FK_EmpID=EMPLOYEE.EmpID AND STOVE.Color IN (SELECT CAST(MIN(Color) AS CHAR(5)) 'Min Color' FROM STOVE) AND EMPLOYEE.Name IN (Select CAST(MAX(Name) AS CHAR(15)) 'Employee Name' FROM EMPLOYEE) ORDER BY EMPLOYEE.Name, STOVE.Color
Hi! I 'd like to update the database..I 'd like to update the same field, the first update would set all to 'No' and the second update woyuld set specific records to 'Yes': 1) "Update tblDept SET IsTop ='No' 2) "Update tblDept SET IsTop = 'Yes' WHERE id = 200 " Cheers!
I have two tables linked with the colunm name systemId and I need to get all the systems responding to the condition (t1.software & 0000000001) = 1 and if they have any contacts I would like to see them. Here is what I have for the moment however my querry do not return the system info if no contact exist and that is wrong.
select customers.customerId,customers.systemId,customers.country from customers,contacts where (customers.software & 0000000001) = 1 and customers.SystemId = contacts.SystemId order by customerid
I have created a small querry and for some reason my container (datalist) is not reconizing the company name This is also the first time that i have created a querry with two select statements in the sproc.... Does it make a difference if there is two select statements in a querry?? When populating a datalist?Not sure if i need to seperate the two statements some kind of way.. Is this ok to use with datalist? Erik.. ALTER PROCEDURE Get_Information_For_Datalist_Global_ @IDProduct AS INT, @IDCompany AS INT AS SELECT Product.ProductName, Product.ProductID,Product.ProductImage, Product.ProductDescription, SprocsTable.* FROM Product INNER JOIN SprocsTable ON Product.ProductID = SprocsTable.ProductID WHERE Product.ProductID = @IDProduct SELECT Company.CompanyID, Company.CompanyName FROM COMPANY WHERE COMPANY.COMPANYID = @IDCompany
hi. i want to have a text box and command button. i will be entering the clients id in the text box and press the command button to search the clients record. this will be search query. i have done that previously in vb6. but dont know what query to write in vb08. Any help. thks.
There are 3 servers in our group which the Local Enterprise Manager Tool is giving us an error when we try to view local DB table data by right-clicking on the table and selecting return all rows... we get this error I thought this was a permissions error but as far as I know we haven't played with the permissions. This seems to be a common ailment here... does anyone have a fix for the EM tool.
An unexpected error happened during this operation. [Query] - Query Designer encountered a Query error: Unspecified error
Hi,How to run a DTS from query analyzer using some t-sql commands??? Is it possible?? What is the command to run a DTS from QA???and What are the possible ways of running a DTS?? from how many types we can execute a DTS like 2 of it i know from EM directly and from the help of Schedule jobs.
I have 2 SQL store procedure which are suppose to return similar number of data. For an unknown reason one of the procedure is returning lasse data than the other. in order to dig out the reason for that I need to compare the two set of data return by both procedure.
one by one is a huge woek has I have more than 1000 records.
So my question is that what is the easy way to extract data which has been forget or miss ?
For example
my procA return 500 rows my procB return 400 rows
I would like to identify the 100 records which are in ProcA results but not in ProcB results In other words extr^ct the difference between A and B
hi. i have created a database with a table called customer. hav a field which displays the date. how can i add a query on the date picker so that when i choose a date on the calendar i can view the customers details. in other display the record by choosing a particular date.
