T-SQL: Group By Clause In Union ALL Statement
Sep 19, 2010
I've got a query which returns all invoices and credit notes for a given client, in a loyalty structure query where I need the total value of the points the client achieved through invoices, minus those that was forfeited by credit notes. Now I start my query like this (There are more unions in the query, this is just the first part)
[Code]...
View 3 Replies
ADVERTISEMENT
Aug 26, 2009
I have a table DailyTransaction in MsAccess as under: DTDate, DTTime, CustId, Description, Quantity, Price, Amount, AccountNumber, Name I want to extend following query with a Group By clause:I require to display all records in a Listview Group By CustId.Then I require to display all records in a Listview Group By AccountName.e how I can extend following query with Group By clause to achieve the required results
View 4 Replies
Jun 7, 2011
Here's what I want to do:
Dim queryX = From m In db.Master
Where m.Field = value
Group Join d In db.Detail
On m.Id Equals d.MasterId Into Group
Where d.Field = value
In English, I want to join the master and detail tables, specifying conditions on each. But this causes the following compiler error:
"Name 'd' is either not declared or not in the current scope."
It works if I put this same condition in functional form:
Group Join d In db.Detail.Where(Function(x) x.Field = value)
but I think this is more typing, harder to understand, and introduces that irritating dummy variable. I really would prefer to use the query comprehension syntax. Is there a way to accomplish this?
View 1 Replies
Jun 12, 2011
"INSERT INTO Images (PicName) VALUES (" & TextBox1.Text & ") WHERE ImageID = " & Summary..if i run the statement without the WHERE clause it works fine, with it i get the error ' Missing ; at end of SQL statement'
View 4 Replies
Nov 28, 2009
I'm using a select statement to select data from an Access DB but I'm using. I can select the data perfectly but when I use the Where clause it doesn't select any data and the data I'm looking is in the DB.
View 13 Replies
Nov 28, 2010
I design a database in datbase.mdb which have a table name Fuel.The primary key Column Name is ID which data type is AutoNumber (Long Integer)User select the ID number and i run he following statement but error occurEID= combo1.text.trim()userid= Convert.ToInt64(EID)Strsql= Select a1,a2 from Fuel where ID= 'userID'ERROR is Creteria DATA TYPE MISMATCH or No record foundPlease
View 4 Replies
Aug 17, 2011
I design a database in datbase.mdb which have a table name Fuel.The primary key Column Name is ID which data type is AutoNumber (Long Integer)User select the ID number and i run the following statement but error occur
EID= combo1.text.trim()
userid= Convert.ToInt64(EID)
Strsql= Select a1,a2 from Fuel where ID= 'userID'
ERROR is Creteria DATA TYPE MISMATCH or No record found?how i pass the where clause creteria if number datatype?and In accesss i read AutoNumber is Long Integer i also convert into INT64 but error is still there.
View 2 Replies
Jan 24, 2012
I have a month and a year dropdownlist which give me the following string 01/2012 The date format in my db is "2012-01-01 00:00:00" but when I select an individual date and put it in a message box it converts to "01/01/2012"
I've altered my select statement below to reflect the converted date. However Im still not given the correct details. Any ideas? Is there a particular format that I need to use when dealing with a timestamp field? Can I even use the "Right" function in a select statement?
Dim newRecordDate As String = val1 & "/" & ComboBox2.SelectedValue Dim sql2 As String = "Select CatA, CatB, CatC, Cost, Currency, MarketingCode, Comment, RecordDate from vw_tblP_Usage_Details where puid = '" & puid & "' right(RecordDate, 7) = '" & newRecordDate & "'"
View 1 Replies
Aug 19, 2010
i want to use a if, then statement in a where clause in linq to sql. Is this possible? I'm trying to achieve the following:
[Code]...
I'm trying to only add the And If if intLink_pk > -1. If it = -1, then i don't want the and clause at all. Is this posssible within the linq to sql itself?I understand i can write 2 entirely different selects, but if I can't get this done, I may have to write about 50 of them as I want to use multiple Ands if certain conditions are met on the form, such as chk boxes etc.
View 3 Replies
Oct 18, 2011
Say I have this in VB.NET:
Dim executeB As Boolean
Select Case myVariable
Case "a"
'some code
Case "b"
'some code
Case Else
End Select
If myVariable is "a", control will go into the first case statement. Now let's say if myVariable = "a", but inside a's case block, I find that executeB is true, is it then possible to jump to the second case?
View 5 Replies
Sep 10, 2010
I'm building a dynamic query in my ASP.NET MVC project by the following: [code]Where basically the strWhere is a string that I build up a dynamic filter depending on what the user selects to search on.This works great until I need to add a date comparison to the date clause.[code]Which will end up as:"SELECT VALUE userInfos FROM MyDBEntities.UserInformations AS userInfos Where userInfos.BirthDate <= 10/09/1992"..But when i try to execute the query with the ToList whenever a date is in the where string i get the following error:The argument types 'Edm.DateTime' and 'Edm.Int32' are incompatible for this operation. Near WHERE predicate, line 1, column 103.
View 2 Replies
Apr 15, 2012
In my datagrid my sql statement uses a have clause to get set information and i am having countless errors
My code is as shown below, were am i going wrong
con.Open()
cmd.Connection = con
Dim da As New SqlDataAdapter("SELECT MONTH(Head.SalesDate) AS Month, Detail.ProductID, Detail.Qty, SUM (Detail.Qty) AS TotalSold, SUM(Detail.Qty / 30) AS Average FROM Head INNER JOIN
[CODE]...
View 7 Replies
May 12, 2009
i'm doing a desktop application using VS2005 with MSSQL 2005. i have a problem with the where clause in the update statement while selecting the listbox item. this is the code for the listbox in load page:
[Code]....
and this is my update query in the delete button event handler: mySQL = "update Appointment set recordStatus = 'deleted'" how do i put in the where clause in when i have 4 columns in the listbox? so can i do this with listbox or have to do with datagridview?
View 1 Replies
May 4, 2012
I am trying to read data from an excel sheet but there is an exception saying that no value given for one or more required parameters which I don't understand. Here is my code upto where the error is pointing to:
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
[code]....
The exception points to that last line MyCommand.Fill(DtSet) which shows me that there is a problem with my select statement. Previously my code reads the data and displays it without the where clause in the select but when I added the where statement so that it can only read data from rows where the column I has a value equal to 176, it instead gives an exception that shows that some required parameter is missing a value?
View 1 Replies
May 15, 2011
What are 'unions' in C plus used for? I have some old C++ files that I'm trying to update,and not sure how I can convert these to VB:
struct txyz {
union {
struct {
float_t Time;
float_t X;
float_t Y;
[Code]...
View 2 Replies
Feb 3, 2010
I am trying to union to queries to create an XML file. The query itself works as T-SQL, but my implementation as LINQ-TO-XML fails with "Could not translate expression..." error.Am I asking the wrong question? Is this approach flat wrong? I am new to LINQ. How do I create a single XML from 2 queries?
[code]...
View 2 Replies
Jan 19, 2010
I know how to call the Union extension method, e.g.
Dim r = productFirstChars.Union(customerFirstChars)
However I do I do this with the Linq syntax, e.g.
from productFirstChars select ?
View 1 Replies
May 3, 2011
I'm trying to match up a specific group name and see if it exists for the currently logged in user using Active Directory roles. If the Group Name exists for the Current User, I want that group name to be displayed in a drop down list. Example: If current user is in BIG Group, display BIG in drop down list.Problem: All I am getting is SIDs and I'm not able to get anything to match up to the group name and nothing will show up in the drop down list.I also get the following Error:Error: Object variable or WIth block variable not set.How do I fix this?? here is the code I am using:
Private Sub GetMarketingCompanies()
' code to populate marketing company drop down list based on the current logged in users active directory group that
[code].....
View 3 Replies
Jan 31, 2011
Enumerate if an Active Directory group's member is user or another group
View 1 Replies
Nov 18, 2010
When the user has typed in some information I would like to output possible matches in a descending order, so if someone types in a full first name and a full surname, it should be listed above a result where just the surname matches.I've done something similar in SQL before which worked perfectly, but this time I'd like to do it in LINQ.
Firstname, Surname, City, Country as string variables.
Dim DataEnum As IEnumerable(Of frmTelephone.clsPerson) = alPerson.OfType(Of frmTelephone.clsPerson)()
[code].....
View 4 Replies
May 20, 2011
i have a Problem with UNION for LINQ to SQL I have my LINQ query
Dim dc As New ContainerDB.DataClassesDataContext()
Dim test = (From container In dc.GetTable(Of tbl_container)() Where container.pkContainerID = iPkContainerID).Union( _
From containerHist In dc.GetTable(Of tbl_containerHIST)() Where containerHist.pkContainerID = iPkContainerID)
[Code]...
View 1 Replies
Aug 3, 2010
Is it possible to create sql union like filter in dataview
View 3 Replies
Jun 20, 2012
I need to return the same results from linq as I get using SQL
SELECT DISTINCT Area, Region
FROM dbo.Regions
Union
SELECT null,'All'
Order by 1,2
I use this for showing an "All" selecting in a combo box
My current linq points to a view that does the union for me but I want to know how to do it right in linq. I can't figure out how to union a row for the null,'All' values.
Public Shared Function GetRegions(ByVal Area As String) As IEnumerable
Dim DB As New SiteDBDataContext
Return From values In DB.uv_Areas _
[Code].....
View 1 Replies
Jan 14, 2011
I want a combined Ranges. How do I combine two ranges into one?
Dim range1 as Excel.Range = osheetTemperatureData.Range("A7:"A10")
Dim range2 as Excel.Range = osheetTemperatureData.Range("C7:"C10")
Dim range3 as Excel.Range = range1 + range2 '????
View 1 Replies
Oct 4, 2010
I have the below C code I want to convert to VB. How do I convert the macro and union over?
c--------------
#define bf_N16
#define S(x,i)(bf_S[i][x.w.byte##i])
[Code]....
View 1 Replies
Jun 13, 2011
I tried to experiment with the ability of .NET to support unions using the code below,but it causes a System. ExecutionengineException in .NET 2.0 and FatalExecutionEngineError in .NET 4.0 with the message:
The runtime has encountered a fatal error. The address of the error was at 0x738b3138, on thread 0x1080. The error code is 0xc0000005. This error may be a bug in the CLR or in the unsafe or non-verifiable portions of user code. Common sources of this bug
include user marshaling errors for COM-interop or PInvoke, which may corrupt the stack.I agree that this code should not work, but I didn't expect this kind of exception. Is this a .NET bug?
Class POLine
Public price As Decimal
Public VendorItem As String[code].....
View 3 Replies
Apr 5, 2011
I'm trying to create a list of all my objects from several lists of objects using Union.
Return Chart.AnnotativeNodes.Union( _
Chart.DecisionNodes.Union( _
Chart.EndNodes.Union( _
Chart.StartNodes.Union(Chart.WorkCenterNodes))))
The above line gets an error because I can't union List(of AnnotativeNode) with List(of DecisionNode). Each list defined like List(of EndNode) or List(of StartNode), but each class inherits from the base type Node.Is there a possible way to union these to get a result of IEnumerable(of Node)?
View 1 Replies
Mar 11, 2011
I have been given a project to do were i have to create a credit union application in VB2008, what i need to create this application etc like how many labels textboxs etc obviously i need to create it but all am asking is if you could tell me were to start as i am lost and never really created anything in vb 2008.
Here is the spec:
The client is prompted for their name and a personal reference number. If the application is successful then this number becomes the loan agreement reference number. If the applicant has a credit rating of 14 or more points the application is rejected. If the credit rating is less than 14 points then the client is prompted for the loan period (must be equal to 12, 24 or 36 months) and the amount required.
The cost of the loan is 6% if required over 12 months and 8% if over 24 months and 12% over 36 months. This is added to the loan. The applicant's net monthly income and total monthly outgoings are taken into account. If the monthly repayment is more than 25% of disposable income (net income outgoings), then the application is rejected. If the repayment is less than or equal to 25% then the loan is granted and a form is produced and printed with the relevant information, including the loan and repayment amounts.
View 2 Replies
Jul 27, 2010
I don't have the same problem when using an untyped dataset.
OK. I have a typed dataset MyDS with a typed datatable MyTable (TableID, Title, Message).
The table gets filled with results from two tables, using a UNION ALL
Select
TableAID,
TableATitle,
[Code]....
Is this how typed datasets handles UNION? I didn't want to create two typed datatables and create separate SQL for each and create a relation.
View 1 Replies
Mar 3, 2011
I have been given a project to do were i have to create a credit union application in Visual studio2008 am just wondering if you could tell me what i need to create this application like how many labels textboxs etc obviously i need to create it but all am asking is if you could tell me were to start as i am lost and never really created anything in vs 2008. here is the spec
The applicant's age is considered, thus
CODE:
The client is prompted for their name and a personal reference number. If the application is successful then this number becomes the loan agreement reference number.
If the applicant has a credit rating of 14 or more points the application is rejected.
If the credit rating is less than 14 points then the client is prompted for the loan period (must be equal to 12, 24 or 36 months) and the amount required.
The cost of the loan is 6% if required over 12 months and 8% if over 24 months and 12% over 36 months. This is added to the loan.
The applicant's net monthly income and total monthly outgoings are taken into account.
If the monthly repayment is more than 25% of disposable income (net income outgoings), then the application is rejected.
If the repayment is less than or equal to 25% then the loan is granted and a form is produced and printed with the relevant information, including the loan and repayment amounts.
View 2 Replies