Sort On Field Constructed In Query
I want to sort on a field that I construct during a query....how can I solve this?
Basically what i am doing is selecting mediafiles with comments in descending order. (so mediafile with most comments on top)
Here's my (not working) query, because it throws the error that the mediaComments column is unknown....
SELECT *
FROM
(
select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,
m.title,m.usercode,mediaComments=(select count(*) from MediaComments where mediaid=m.mediaid)
FROM Media m
WHERE m.usercode>0
group by m.title,m.usercode
) as Info
WHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View Complete Forum Thread with Replies
Related Forum Messages:
Inserting Data From A Constructed Table
I can do this in an old (~2003) way, but I'm trying to figure out a new (2005) way. What I've got is an e-commerce project in which users select various products from a catalog and add them to a shopping cart. Then they go to a checkout page which displays the current contents of the shopping cart, which are contained in a manually-constructed data table (system.data.datatable). On the checkout page a GridView displays the contents of the data table. At that point they can check out via button click, which launches the following (somewhat simplified) ADO.NET code:1 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) 2 'Some variables 3 Dim intCounter As Integer 'Used to count the loop 4 Dim prmQuantity As New System.Data.SqlClient.SqlParameter() 'A parameter 5 Dim prmProduct As New System.Data.SqlClient.SqlParameter() 'A parameter 6 Dim prmPrice As New System.Data.SqlClient.SqlParameter() 'A parameter 7 Dim InsertCommand As New System.Data.SqlClient.SqlCommand 'The SQL insert command 8 Dim dbConnection As New System.Data.SqlClient.SqlConnection 'The connection to the DB 9 10 'Set the parameters for the SQL statement 11 prmQuantity.ParameterName = "@Quantity" 12 prmQuantity.SqlDbType = Data.SqlDbType.Int 13 prmQuantity.Size = 18 14 prmQuantity.Direction = Data.ParameterDirection.Input 15 16 prmProduct.ParameterName = "@Product" 17 prmProduct.SqlDbType = Data.SqlDbType.VarChar 18 prmProduct.Size = 50 19 prmProduct.Direction = Data.ParameterDirection.Input 20 21 prmPrice.ParameterName = "@Price" 22 prmPrice.SqlDbType = Data.SqlDbType.Int 23 prmPrice.Size = 18 24 prmPrice.Direction = Data.ParameterDirection.Input 25 26 'Create the connection to the database using web.config 27 dbConnection.ConnectionString = ConfigurationManager.ConnectionStrings("MyDB").ConnectionString 28 29 'Various command settings 30 InsertCommand.CommandText = "INSERT INTO [Orders] ([Quantity], [Product], [OrderDate], [ItemPrice]) VALUES (@Quantity, @Product, {fn NOW()}, @Price)" 31 InsertCommand.CommandType = Data.CommandType.Text 32 InsertCommand.Connection = dbConnection 33 34 'Add the rows to the database 35 For intCounter = 0 To objDT.Rows.Count - 1 36 37 'Re-inserts the data rows from objDT 38 objDR = objDT.Rows(intCounter) 39 40 'Set param values 41 prmQuantity.Value = objDR("Quantity") 42 prmProduct.Value = objDR("Product") 43 prmPrice.Value = objDR("Price") 44 45 'Add params to insert command 46 InsertCommand.Parameters.Add(prmQuantity) 47 InsertCommand.Parameters.Add(prmProduct) 48 InsertCommand.Parameters.Add(prmPrice) 49 50 'Open connection 51 InsertCommand.Connection.Open() 52 53 'Execute the insert command 54 InsertCommand.ExecuteNonQuery() 55 56 'Close connection and clear parameters for the next loop 57 InsertCommand.Connection.Close() 58 InsertCommand.Parameters.Clear() 59 60 Next 61 62 Response.Redirect("done.aspx") 63 64 End Sub 65 What I'd like to do is see if I can instead use a simplified approach based on 2.0 controls. Specifically, I was hoping that I could use an SqlDataSource and simply tap into its built-in Insert capabilities. So I tried this alternate procedure:Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)Dim intCounter As Integer 'Used to count the loopFor intCounter = 0 To objDT.Rows.Count - 1DSOrdersNew.InsertParameters("Product") = objDR("Product")DSOrdersNew.InsertParameters("Quantity") = objDR("Quantity")DSOrdersNew.Insert()NextEnd SubWhen I run this I get an error message saying: "Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'."Am I just way out in left field here? I guess I don't mind doing it the old-fashioned way, but it seems like there ought to be a way to do this. Any suggestions would be appreciated. Thanks!
View Replies !
Cant Sort A Text Field!
Hiive got a table and contains a surname text field. Why cant i do a select statement ORDER BY surname. I get an error saying i cant sort a text field! how do i go around it!thanks
View Replies !
Sort A Calculated Field
I have a quick question that i have just a simple calc field. Field.Value-Field.value then i want to sort by the calculated results. The expression is fine i know it is written wrong here but i cannot figure how to sort by the result of the expression. Thanks.
View Replies !
Sort Using A Field Without Displaying In Report?
I am creating a report using report builder and I am using several fields from various entities. Is there a way to sort the data in the report using a field that is not being displayed in the report? The "sort and group" option lists only fields that are selected for output in the report... but is there a way to do sorting using a field that is not displayed in the report?
View Replies !
Sort A Report On A Calculated Field??
OK I have a report that needs an interactive sort on a calculated field. I get the message: "Report items cannot be used in sort expressions" That's the whole reason we purchased SS*S and are putting up a Data Warehouse, so we can rank and analyze our data. Surely there is a way to do this?? Thanks for any advice!
View Replies !
Sort Using A Field Without Displaying In Report?
I am creating a report using report builder and I am using several fields from the entities. Is there a way to sort the data in the report using a field that is not being displayed in the report? The "sort and group" option lists only fields that are selected for output in the report... but is there a way to do sorting using a field that is not displayed in the report?
View Replies !
Can We Apply Interactive Sort On The Group Field?
Hi I am developing report in SSRS using List Item. I develop the report in which some fields are group fields and some are detail fields. I implement the interactive sort on group fields but when i click on the sorting buttons in runtime. It can not sort the group data. Can any one help in this scenerio?
View Replies !
Impossible? Sort On Dynamic Field (in Combination With Row_number)
I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View Replies !
Sort MDX Query
Trying to get data from result of MDX query in dec order: iLoopFrom = cst.Axes(1).Positions.Count - 1 iLoopTo = 0 iLoopStep = -1 im using these lines to read the data from bottom to top but what exactly does Position.Count do? and is the -1 there because the result has headers?? Thanks
View Replies !
How To Sort Data Using SQL Query
Dear All, i need your help, i had created a table student, studentid column with alpha numeric primary key with varchar datatype now my problem: i want to sort the student id accroding to studentid like STU1 STU2 STU3 STU4 STU5 . . . STU9 STU10 STU11 but i’m getting the sorted result like this, how to overcome this problem,guide me PLEASE STU1 STU10 STU11 STU12 . . . STU100 . . . STU1000 STU10000 STU2 STU20 STU200 STU2000 STU20000 STU20001 Thank's In Advances
View Replies !
SQL Server Query Sort
I'm trying to find a way to sort my query like the following: SELECT * FROM tbl_Post WHERE ID = 3 OR ID = 1 OR ID = 4 OR ID = 2 ORDER BY (3,1,4,2) Now if ID is my primary key, the sort by default would be 1, 2, 3, 4. I would like to specify order so that I could return the order as 3, 1, 4, 2 if I wanted to. Is there any way to do this? Thanks,Russ
View Replies !
Sort Result According The Query
I have a query: SELECT * FROM Mobile_Subscriber WHERE (sub_ID IN (17, 2, 19)) The result return: sub_ID sub_name 2 John 17 Alice 19 Eddy But what I want is: sub_ID sub_name 17Alice 2John 19Eddy Is it possible to return the result order by the query with: sub_ID IN (17,2,19)?
View Replies !
Some Sort Of Cross Tab Query In Sql
I have three tables:tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCodetblBookRange has the fields bookRangeID, bookRangeDescriptiontblBookSubject has the fields bookSubjectID, bookSubjectDescriptionso some typical data in tblBook might be:1, 1, 1, B1HBSCI2, 1, 2, B2HBFRE13, 1, 3, B3HBGER4, 2, 1, B4PBSCI5, 2, 2, B5PBFRE6, 2, 3, B6PBGER7, 3, 1, B7CDSCI8, 3, 2, B8CDFRE9, 3, 3, B9CDGER110, 3, 3, B10CDGER211, 1, 2, B11HBFRE2tblBookRange would be:1, HardBack2, PaperBack3, CD RomtblBookSubject would be:1, Science2, French3, GermanI'd like to create a query which will return me the subjects along thetop, the book range down the side, and the bookcodes in the cells, abit like this:BookRange , Science, French, GermanHardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGERPaperBack , B4PBSCI, B5PBFRE, B6PBGERCD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2Does that make any sense? So basically I'd like to get some kind ofdynamic SQL working which will do this kind of thing. I don't want tohard code the subjects in or the book ranges. I get the feeling thatdynamic SQL is the way forward with this and possibly using a cursor ortwo too, but it got quite nasty and convoluted when I tried variousattempts to get it working. (one of the ways I tried included workingout each result in a dynamic script, but it ran out of characters asthere were too many "subjects".)If anyone has any nice but quite dynamic solutions, I'd be delighted tohear.(and I know some of you have already told me you don't like tablesbeginnig with tbl, but I'm not hear for a lecture on namingconventions, I'm hear to learn and share ideas :o) )
View Replies !
Query Sort Question
I have a field called "type" in my "school" table that can have threepossibilities:ElementaryMiddleHighIn my result set, I always want Elementary to come first, Middle tocome second, and High to come third. An alpha sort gets me this:ElementaryHighMiddleSure, I could create a look-up that assigns an integer to the "type"field to get the right sort order (i.e. elementary = 0, middle = 1,high = 2) ... but is there a better way to do this with SQL syntax?Thanks,Ralph NobleJoin Bytes!
View Replies !
SELECT, SORT, And SUM Query
Hello everyone, I Have a table with columns: - CustomerID - DateTransactionCompleted - AmountPaid Illustration data as follow GO INSERT INTO Cus_Tab_Dev_2 VALUES(30,'12-12-2004','18000.00') INSERT INTO Cus_Tab_Dev_2 VALUES(30,'12-15-2004','2000.00') INSERT INTO CUS_Tab_Dev_2 VALUES(30,'1-16-2005','15000.00') INSERT INTO CUS_TAB_DEV_2 VALUES(42,'2-2-2005','12000.00') GO What I want is a report that tells me the Total Sales per month Per Customer in the following manner: Customer ID Month/Year Total Per Month ----------- ----------- ---------------- 30 12-2004 20000.00 30 1-2005 15000.00 42 2-2005 12000.00 This is a homework, and I have been trying to get it to work since this morning. Any help is appreciated. PS: If you do not feel you want/should help. Please don't call me names! I don't ask people to help me with my homework usually. But, I am running out of time on the assignment.
View Replies !
Comparing A Real Datetime To A 'constructed' Datetime
I have the following SQL: select convert(datetime,'04-20-' + right(term,4)) as dt, 'Deposit' as type, a.* from dbo.status_view a where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc. The SQL above returns 2004-04-20 00:00:00.000 Deposit ... Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do: select * from ( select convert(datetime,'04-20-' + right(term,4)) as dt, 'Deposit' as type, a.* from dbo.status_view a ) where dt >= a.submit_date I get the following error: Syntax error converting datetime from character string. Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...
View Replies !
Dynamic Sort Column And Sort Order Not Working
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC, case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC, case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC, case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end, case when @SortColumn = 'FileDataID' then FileDataID end, case when @SortColumn = 'Field2' then FileDesc when @SortColumn = 'VersionNotes' then VersionNotes end case when @SortOrder = 'DESC' then DESC when @SortOrder = 'ASC' then ASC end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
View Replies !
SQL Query Help - Using DropDownLists To Sort (multiple Variants)
Hi Everyone,I am creating a portal and want the user to be able to select four variants from four separate drop down boxes... Such as chapter, story, etc... The user will then be able to click Find and shorten up the gridview list. The below query is what I am using for my current gridview (with custom paging). What I want to do is something like this: Pass a variable such as @Story. If no Story is chosen, then the variable would be *. If a variable is chosen, the the @Story would the StoryID. However, I don't believe SQL recognizes the * in this case. I was thinking it should, but I don't believe it does. I was hoping it would just take it and I could write, SELECT * from Stories where StoryId=@Story, or something like that... and if * was @Story, then it would just select all... or if it was 1, then just stories with a StoryId of 1.Am I totally off base here? Thanks! CREATE PROCEDURE SortAllStories@startRowIndex int,@maximumRows int,@totalRows int OUTPUTASDECLARE @first_id int, @startRow intSET @startRowIndex = (@startRowIndex - 1) * @maximumRowsIF @startRowIndex = 0SET @startRowIndex = 1SET ROWCOUNT @startRowIndexSELECT @first_id = StoriesID FROM Storie ORDER BY StoriesIdPRINT @first_idSET ROWCOUNT @maximumRowsSELECT Stories.StoryId, Chapters.ChapterName, Chapters.EnglishName, Translations.Translation,Stories.Verse, Stories.StoryFROM Chapters, Stories, TranslationsWHERE Chapters.ChapterId=Stories.ChapterId AND Translations.TranslationId = Stories.TranslationId AND Stories.StoryId >= @first_idORDER BY Stories.StoryIdSET ROWCOUNT 0SELECT @totalRows = COUNT(StoryId) FROM Stories
View Replies !
What Is Faster? C++ Vector Sort Or Sort In Database
hi there,I have to fetch values from a database and store them in a vector inmy c++ application.What is faster? if i fetch the values sorted from the database with a'order by' clause in the sql-statement or if i fetch them into thevector unsorted and sort the vector then?Thanx for your helpGreetzJens
View Replies !
Max Of Date Field In Query
I have a table with date fielde.g. SrNo Date 1 1-MAR-2008 2 3-Mar-2008 3 7-Mar-2008 4 10-Mar-2008 5 15-Mar-2008 I need a query to find out Max date which is less than '8-Mar-2008' . i.e i need single output '7-Mar-2008'anybody helpThanks
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
SQL DateTime Field Query
I was wondering if someone could help me here with some reporting I'm trying to do. For website visits, I currently use getdate() to have SQL insert the date and time of the visit in the table. I was wondering, is there a way to grab just the month from the field? I would like to chart this and I need to group the visits by month. I'm not sure how I would go about filtering just the month out of the entire date and time fields.
View Replies !
Empty Field Query
Hi Friends, I have one query that i have to Replace the Empty Value of a filed with some other value how can i do in SQL?? ID Phone Name 1 122 abc 2 xyz 3 444 mmm 4 525 ccc 5 nvb Now i want replace the blank (Empty) filed with some charaters Numbers how can i do that?? any Help Ashu
View Replies !
How Do I Query For Particular Criteria In Every Field In The Db?
I need to query a table for a record only if there is a certain text string in ANY OF THE TABLE'S fields. I know of using the WHERE clause with logical OR (e.g. - WHERE field1 = 'xyz' or field2 = 'xyz' or ...) but there is a limit of 40 expressions with an Access database, and I have 102 fields to look through! That's my dillema. I know I may need to go to MSSQL or Oracle, but I'd rather stay with Access. Is there a syntactical way to express the query I need? Or do I have to look somewhere other than Access for my database needs? Thanks for the advice!
View Replies !
Use Calculated Field In Same Query
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example: SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total Obviously that doesn't work, but what could I do to get that basic thing to work? Thanks!
View Replies !
SQL Query: Missing Field Value
Suppose in a table, there is some data in a field: 1, 2, 3, 5, 6, 8, 9. I need a sql query which will list the missing numbers: 4,7 (Missing digits) Query: Select * From table1 field ------ 1 2 3 5 6 8 9 Expected query:?? filed1 ------ 4 7 Please help me to get a query which will provide my expected data. Thanks. /Fakhrul(mfhossain@gmail.com)
View Replies !
Query Detail As A Field.
I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like: Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,.... From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID Where Order.OrderID=10 so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...." How can I write this query in T-SQL? Thanks
View Replies !
Really Simple Query Needs Another Field
I have written this query in SQL select * from wce_contact where (Mail3Date <= '2007-09-25') AND NOT (Mail1Date is NULL ) AND NOT (Mail2Date is NULL ) AND (Mail4Date is NULL ) AND (Mail5Date is NULL ) AND (Mail6Date is NULL ) AND (Mail7Date is NULL ) AND (Mail8Date is NULL ) AND (Mail9Date is NULL ) AND (Mail10Date is NULL ) AND (Mail11Date is NULL ) AND (Mail12Date is NULL ) AND (Mail13Date is NULL ) AND (Mail14Date is NULL ) AND (Mail15Date is NULL ) AND (Mail16Date is NULL) Little bit of background info - We have a database of over 500k websites with email addresses. We use email marketing to contact these sites and everytime they get mailed i need to fill in the MailDate field. Obv Mail5Date means they have been emailed 5 times etc The above qry is trying to find all contacts that have been mailed 3 times and havent been mailed in the last 42 days. I also have an IDStatus field that allows me not to use the contacts that have opted out our mailing or website shutdown etc I try adding this to the bottom of the query AND (IDstatus is Null) But the query runs and finds 0 contacts which i know isnt the case, as when i use our front end database application it produces results. I was wondering if anybody could possibly help me? Thanks!
View Replies !
Query To Sum The Same Field Twice In The Select Statement
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
View Replies !
DISTINCT SELECT Query With More Than One Field
Hello, I want to do a DISTINCT SELECT query with more than one field, for example a ID field with a Type field, as if both fields make the primary key, like (ID 1 ,Type 1) ,( ID 1, Type 2) and (ID 2, Type 1) is ok but not (ID 1, Type1) and (ID 1,Type 1) if its not possible to do a distinct with more than one then what other techniques are possible to get the duplicate data out. the reason why I want to use distinct is that I can use that query to export that data to where both of these fields make the primary key. Thanks in advance
View Replies !
SQL Query - Search Field For String
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
View Replies !
Help! - Query On Time Field In SQL Server - How?
Hi,I have an orders database and I need to be able to write a querythat groups sales by:1. Date2. Time of day (both am and pm)I currently have a date/time field named "Submitted" that storesjust the date.However, how do I store the time?I know I can store the date/time in the "Submitted" field Icreated. However, because of the time being stored in that field, Ican't execute an easy query such as:select * from database where submitted = '2/10/05'(reason being, the time portion makes each "Submitted" fieldrecord unique, so just the date won't be recognized)I realize there probably is a much better way to do this in theSQL query language that I am not aware of.Two questions:1. Based on what I'm trying to do -- query based on date and groupby number of sales for each hour of the day (am and pm) -- what is thebest approach to store the time?2. What does the query look like that querys by date and groupsorders by time of day (am and pm)?Thanks for your help.J
View Replies !
How To Query By Keyword For Image Field.
If the data type of field is "varchar",we can use "like" to query if it hassome substring.Such as "where custom.valuevariant like '%Verizon%' ", it will query out allrecords that contains string "Verizon".But how to do when data type of field custom.valuevariant is "image"?Thanks
View Replies !
Referencing A Calculated Field In A Query
I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005 SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2 This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it. The only way how to avoid the issue in the SQLExpress seems probably to be - Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss) - Utilize computed columns if possible (no idea on performance impact here) Am I right or is there any other way how to reference a calculated field? Any suggestion is greatly welcomed! Thanks in advance.
View Replies !
Help - Updating A Field In Query Analyzer
Hi All, I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code: USE DBMyDatabase UPDATE TblToBeUpdated SET IDField=TblOther.IDNew WHERE IDField=TblOther.IDOld SELECT Pk, IDField FROM TblToBeUpdated What am I doing wrong? The error code I get is: Server: Msg 107, Level 16, State 3, Line 1 The column prefix 'TblOther' does not match with a table name or alias name used in the query. Server: Msg 107, Level 16, State 1, Line 1 The column prefix 'TblOther' does not match with a table name or alias name used in the query. Thanks. Henry
View Replies !
Query/select The First 10 Characters In A Field
I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B. So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi' I know I've done this before but can't find a good reference. It's something like this when calling a query within a WHILE loop: SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8) Am I close? Any help would be appreciated.
View Replies !
Query To See If An Int Field Starts With A Certain Number
How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!
View Replies !
Need Help With Update Query To Populate New Field
Hi folks: I have added a new field to an established table, and am having trouble figuring out how to populate its values: Two tables are involved: Jobs and Parts There is a one-to-one relationship between each JobID and its PartID Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim. The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time: UPDATE Jobs SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question])) WHERE (JobID = [the job in question]) My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time, Matt
View Replies !
How Do I Use A Parameter In A Dataset Field Value Using An MDX Query?
Let me simplify. The value of my dataset field Dimension_1 is [SOB Year].[Yr Id].[Yr Id].[MEMBER_CAPTION] I want to use parameters for SOB Year and Yr Id like I do on the query. But if I use ="["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].[" & ["& Parameters!dimension_1_column.Value &"]" it returns null for the values. I don't know how to make these parameters I am passing in parameters for the table and column to build an MDX query here is my query. ="SELECT NON EMPTY { [Measures].[Liability Amt] } ON COLUMNS, NON EMPTY { (["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].["& Parameters!dimension_1_column.Value &"].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SOB Clean] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" Anyone know what value I need to use on the dataset field to access the column? Thanks, Roger
View Replies !
|
TRACKER
