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

Group By Clause In Sql Query?

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

Can't Use A Where Clause On Both Tables In A LINQ Group Join?

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

DB/Reporting :: INSERT Statement With WHERE Clause

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

Select Statement Using The Where Clause On Oldb

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

Select Statement Where Clause In VB 2008?

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

Select Statement Where Clause In VB2008?

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

Sql - Where Clause In Select Statement - Datetime?

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

VS 2008 IfThen Statement In Linq To Sql Where Clause?

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

.net - Switch/Select Statement Jump To A Different Case Clause?

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

Executing SELECT Statement With Date In WHERE Clause Fails

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

In Datagrid My Sql Statement Uses - Clause To Get Set Information - Countless Errors

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

VS 2005 - Where Clause In The Update Statement While Selecting The Listbox Item

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

Sql - Failing To Read Data From An Excel Sheet With Where Clause In Select Statement?

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

C++ Union To .NET?

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

LINQ-TO-XML Using UNION?

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

Use Union In Linq?

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

See If Current User's Group Name Matches A Specified Group Name Using Active Directory Roles And SID's?

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

Enumerate If An Active Directory Group's Member Is User Or Another Group

Jan 31, 2011

Enumerate if an Active Directory group's member is user or another group

View 1 Replies

Distinct When Using Union On LINQ?

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

.net - Conversion With Union And Option Strict On?

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

Create Sql Union Like Filter In Dataview?

Aug 3, 2010

Is it possible to create sql union like filter in dataview

View 3 Replies

Sql - Linq Union For Extra Row Not From Table

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

Union Ranges In Excel Interop

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

VS 2010 Convert C Macro & Union To VB

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

.net - Attempted Union Structure Cause System.ExecutionEngineException?

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

.net - LINQ Union Objects With Same Base Class?

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

Create A Credit Union Application In VB2008 ?

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

Populate Typed Dataset Wit UNION ALL SQL Query?

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

Project - Create A Credit Union Application

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







Copyrights 2005-15 www.BigResource.com, All rights reserved