Use The Reference Column Name In The Same SELECT Querry
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.?????
View Complete Forum Thread with Replies
Related Forum Messages:
Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
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
View Replies !
Problem With Select Into Querry
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 How can i pass the result for the 2nd Database
View Replies !
Select +replace Querry Problem..
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!
View Replies !
How To Create SELECT QUERRY FOR &"A CHANGE ORDER FORM&" In ASP?
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 FORM: <!#include file="../includes/settings.asp"> <!#include file="../includes/getSettingKey.asp"> <!#include file="../includes/sessionFunctions.asp"> <!#include file="../includes/databaseFunctions.asp"> <!#include file="../includes/screenMessages.asp"> <!#include file="../includes/currencyFormat.asp"> <% on error resume next dim mySQL, connTemp, rsTemp ' get settings pStoreFrontDemoMode = getSettingKey("pStoreFrontDemoMode") pCurrencySign = getSettingKey("pCurrencySign") pDecimalSign = getSettingKey("pDecimalSign") pCompany = getSettingKey("pCompany") pAuctions = getSettingKey("pAuctions") pAllowNewCustomer = getSettingKey("pAllowNewCustomer") pNewsLetter = getSettingKey("pNewsLetter") pStoreNews = getSettingKey("pStoreNews") pSuppliersList = getSettingKey("pSuppliersList") pRssFeedServer = getSettingKey("pRssFeedServer") %> <b><%=getMsg(10021,"despatch")%></b> <br> <%=getMsg(10024,"Enter despatch ...")%> 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 %> <!--#include file="../includes/settings.asp"--> <!--#include file="../includes/getSettingKey.asp"--> <!--#include file="../includes/databaseFunctions.asp"--> <!--#include file="../includes/stringFunctions.asp"--> <!--#include file="../includes/miscFunctions.asp"--> <!--#include file="../includes/screenMessages.asp"--> <!--#include file="../includes/sendMail.asp"--> <% on error resume next dim mySQL, connTemp, rsTemp, pEmail ' get settings pStoreFrontDemoMode = getSettingKey("pStoreFrontDemoMode") pCurrencySign = getSettingKey("pCurrencySign") pDecimalSign = getSettingKey("pDecimalSign") pCompany = getSettingKey("pCompany") pEmailSender= getSettingKey("pEmailSender") pEmailAdmin= getSettingKey("pEmailAdmin") pSmtpServer= getSettingKey("pSmtpServer") pEmailComponent= getSettingKey("pEmailComponent") pDebugEmail= getSettingKey("pDebugEmail") pGiftRandom= getSettingKey("pGiftRandom") pPercentageToDiscount= getSettingKey("pPercentageToDiscount") pStoreLocation = getSettingKey("pStoreLocation") ' form pidOrder = lcase(getUserInput(request.form("idOrder"),50)) pProduct = lcase(getUserInput(request.form("product"),50)) pQuantity = lcase(getUserInput(request.form("quantity"),50)) pPriceToDiscount = 0 ' insert despatch mySQL="INSERT INTO despatch2 (idOrder, product, quantity ) VALUES ('" &pidOrder& "', '" &pProduct& "', '" &pQuantity& "')" call updateDatabase(mySQL, rstemp, "despatchExec") pBody=getMsg(10025,"Despatch confirmed...") &VBcrlf& getMsg(311,"To opt-out click at:") & " (URL address blocked: See forum rules)="&pEmail response.redirect "redit_message.asp?message="&Server.Urlencode(getMsg(10025,"Despatch confirmed")) call closeDb() %> Thank you for your help in advance.. Regards, philip
View Replies !
Must I Display A Column In DataGrid To Reference It?
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.
View Replies !
Dynamic Reference Of Column In Input0Buffer
hello, 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?) thanks! Arik
View Replies !
Adding Lookup Programmatically:How Can I Add Column From Reference Dataset To The Transformation?
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.
View Replies !
SELECT Column Aliases: Refer To Alias In Another Column?
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
View Replies !
SELECT Column Aliases: Refer To Alias In Another Column?
Using SQL Server 2000. How can I refer to one alias in another column? E.g., (this a contrived example but you get the idea) SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
View Replies !
How To Trap &"DELETE Statement Conflicted With COLUMN REFERENCE Constraint&" Error
Hi, 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? Thanks
View Replies !
Sql Sub Querry
I have a single table and I want to check for the duplicates. On the bases of two column. Namley HiNum, Procode. so if there are duplicates I want to print out those records How I can do that in a script. I tried the following codes but I am getting an error. how I can fix it. Or this is completly wrong? . Select * from Claims Where HiNum in (Select HiNum,Procode,Count(*) as NumberOfRows From Claims Group by HiNumber, Procode Having Count(*) > 1)
View Replies !
I Need Help With A New Querry Again
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 Thank you
View Replies !
Querry Help!!!
Is there a way I can frame the querry for the following scenario?? column1 -------- item1 item2 item3 ... .. Querry: Select datediff(month,item1,item2) and next for item2 and item3... Thanks
View Replies !
2 Updates In The Same Querry!
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!
View Replies !
Insert Querry
Hello Can anyone give me the code to insert date from textbox to database(SQL Server 2000). The date fromat in SQL is dd-mm-yyyy. Rathish
View Replies !
Querry Question
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
View Replies !
Need Help To Build Querry
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 Systems -SystemId -CustomerId -Name ... Contacts -ContactId -SystemId -Name -Tel ...
View Replies !
How To Build My SQL Querry.
Hi. I know this querry: "SELECT TOP 10 * FROM MyTable ORDERBY ..." will give the top 10 rows. But how can I build a querry that can get the rows from 40-50 (ie). Thanks.
View Replies !
Search Querry
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.
View Replies !
Enterprise Mgr Querry Error
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
View Replies !
Generating A Querry Parameters
Hello, Need a bit of help here. In one table i have my production data on some wheels. Each time the wheel is touched, it gets smaller. Then in another table I have the primary data of that wheel, ie price vendor, make model and scrap diameter. What I'd like to figure out is, In one month, what was the wheel size to start off, what is the end size to give me a total size used during that month. If i could get that, then I think I would be able to figure out what the price cost for useage of that wheel was for the month. Any help? Thanks Brian
View Replies !
Editing In Querry Analyzer Not In EM
Hello, In SQL if we want to edit a row in a table then we have two ways to do the same, 1. Execute a command to Update the records in sql through querry analyzer. 2. Go to enterprise manager and then open the table and update a new row. But can't we do the editing in the querry analyzer is there any way to do this. That means i select all the records from my table using the following querry. Select * from MyTable and then performs editing in the querry analyzer without executing any update command and without using the Enterprise manager. Can we do this???????? Bye and TC
View Replies !
Simple Querry Question ??
Dear all, 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 thnaks for your help serge
View Replies !
Running DTS From Querry Analyzer
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.
View Replies !
Date Picker And Querry
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. thx.
View Replies !
Querry 2 Detect Doubles In A Table
Hi, I have a table which contain a field called 'company' of type 'int'. I want to make a querry which returns all doubles ... for example: I have: after querry the return must be: company company: 1 1 3 3 3 3 11 3 7 2 3 1 2 2 1 4 2 5 Thank you, Sebastian
View Replies !
Problem With Ative Directory Querry
Hi, I linked my Active Directory catalog on my local machine as linked server. Next I querry this server, and it works pretty. I have problem in similar situation when I linked AD as linked server on other machine and when I querry AD it yields error: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query " SELECT cn, mail, sn, co, distinguishedName, displayName FROM 'LDAP://DC=<domain>,DC=com' WHERE objectClass = 'user' AND objectCategory = 'Person' " for execution against OLE DB provider "ADSDSOObject" for linked server "(null)". What is wrong?
View Replies !
How To Creat A Database Via SQL Querry Analyzer To A Web Server
I am new to ASP.NET and met a problem about access a database on a Web Server (www.myserver.com). My meaning here that I created a .sql from my localhost to test, but when to upload to my web server on the internet, an error occured: "System.Data.SqlClient.SqlException: SQL Server does not exist or access denied". Anyone here can help me to solve my problem. Waiting for your reply soon. Thanks
View Replies !
Report Designer : BIDS Seems To Hang After Querry
hi, I'm creating my first report. I do "new project" > "report server project". Thereafter I create my shared datasource. Thereafter I try to add a new report. It ask to enter my querry. I've built up my querry previously with the querry analyser in SSMS, and there it runs quite fast. But when i copy-paste the querry in the reporting designer, and i choose to preview it, it takes more then 20 minutes. however, my querry it quite big, but i've written querries ten times larger in my life. (it's about 50lines: 10 querries, union-ed together and in each one i have 4 innerjoins.... all tables are even almost empty, so it's not that indexes are missing or whatever)... is it known that in the report designer querries are much more slower????
View Replies !
Return The Results Of A Select Query In A Column Of Another Select Query.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
View Replies !
Select Another Column Where Max
Hello all.Is there a simpler way to write something like this:select column_a from tablewhere column_b = ( select max( column_b ) from table )ie. find the row where column_b is at it's maximum and return anothercolumn's value?something like (hypothetically)select column_a from tablewhere column_b is maxLeaving aside the issue of whether max( column_b ) is unique ...Thanks.Aaron
View Replies !
Select Column
Is there a way to select a column number without knowing its name? For example I want to select column 3 of a file without knowing it is: Select [column 3] FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=D:ReportsTest;','select * from Report.txt ')
View Replies !
Select Using IN For A Column
Hi, I have the following tables. Code Block CREATE TABLE [Liga] ( [cod_cliente] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [tronco] [int] NULL , [nrtelefone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] GO 1111 10 89543964 1111 9 32659841 1111 8 32569874 1111 7 25469874 CREATE TABLE [Lista] ( [cod_cliente] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [troncos] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] GO '1111' '8,10' For this data what I need to do is return all rows from "Liga" that the value of the column Tronco is in troncos from the table Lista So for example if I query this data the return would be 1111 8 32569874 1111 10 89543964 And so on... How can I do that ?
View Replies !
Column != ALL (SELECT..)?
Here is the code. SELECT info1.categoria FROM ( SELECT DC2.categoria,count(DC2.ref) as quantidade FROM Disco_Categoria as DC2 GROUP BY DC2.categoria ) as info1 WHERE info1.quantidade > 2 AND info1.categoria != ALL ( SELECT DC2.categoria FROM Encomenda_Disco as ED2 INNER JOIN Disco_Categoria as DC2 ON ED2.ref=DC2.ref WHERE ED2.cod = 1 --ED.cod ) What I'm trying to do is to check if a category doesn't exist for a specific delivery (Encomenda). After reading about the predicated ALL, I decided that it was to be used, but it isn't returning the expected result. Someone correct me if I'm wrong (since probably I am ;)) but what I'm asking in the code is to compare info1.categoria with all results of DC2.Categoria to check if there is any match, returning true if there isn't any match at all. It is just not doing it. Can someone please point me at the problem so I can solve this? Thanks in advance.
View Replies !
|
TRACKER
