Is There A Way To Display This Query In 1 Result Set?

Feb 15, 2006

I am trying to count the number of Part that is repaired and those that is not repaired, is there a way to combine the following into one result set instead of returning 2? The bold line is the only condition that's different between this 2 query.

I want to display these fields: date_complete, part_categoryid, part_model, repaired, not_repaired

/* parts being repaired */
select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model,
 count(DISTINCT part_id) as repaired
from  tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partid
where (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')
and part_replaced = 0
and (part_flag_nff = 0 and part_flag_ntf = 0 and part_flag_beyondrepair = 0)
group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid,part_model
order by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)

/* parts completed but not being repaired */
select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model,
 count(DISTINCT part_id) as not_repaired
from  tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partid
where (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')
and part_replaced = 0
and (part_flag_nff = 1 or part_flag_ntf = 1 or part_flag_beyondrepair = 1)
group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid, part_model
order by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)

View 2 Replies


ADVERTISEMENT

Display Query Result Vertically

Mar 27, 2008

Hi,

I have a query to run, but the data in the tables are stored horizontally. I want the query to output the result vertically.

e.g. if row 1 contains the following data:
custA,3-april2008,mango's,123,456,78,10

Then i want it to output as follows:
custA,3-april2008,mango's,123
custA,3-april2008,mango's,456
custA,3-april2008,mango's,78
custA,3-april2008,mango's,10

hope I'm clear, and would appreciate if someone could help me.

Thanks

View 3 Replies View Related

Re-display Result Set Without Re-running Query In Query Analyzer?

Apr 9, 2006

I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.

View 6 Replies View Related

Split Fields And Display Query Result

Mar 21, 2007

Hi,
I'm having a problem in spliting the fields
I need to ru the following query to join two tables and getting the output as shown.

Query:
select cusl.user_name,
pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'

Output:
user_name bills_ref_info payee_acid cust_acid txn_amt
SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318
SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327
SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031
SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918


Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query.
Can this be done? Please guide me on this...

View 7 Replies View Related

Connect To MSSQL2005 Using ASP2 And Display Query Result

Nov 6, 2007

Question: I have searched here and on Microsofts site already but it seems that all solutions require already either some ASP2 knowledge or MSSQL knowledge .. I am quite new in both but need to realise this for a project.

I have installed a MSSQL 2005 server running MSSQL2005 Standard in mixed authentication mode. Services running using a domain account created for this purpose.
I have then created a simple database called test with a table called testtable

All I need to achieve now is a simple ASP2 page, which is located on the DC IIS, which connects to the SQL server (in same domain but different server) using WINDOWS authentication and returns the result of

SELECT * FROM TESTTABLE

I try now for a couple of days so I hope someone could help me with this .. I really need a step by step guide what I need to do on the SQL server side (so a specific user can connect to this particular database) and on the IIS side ..

I know it is always painful to help someone with little knowledge but I am getting desperate.

Thanks a lot guys

View 1 Replies View Related

SQL Server 2012 :: SELECT Query - Cursor To Display Result In Single Transaction

May 25, 2015

Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.

CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR

[Code] ....

View 1 Replies View Related

Display Of A Result Set Without Order By

Apr 8, 2008

Hi all

If we don't specify any Order by clause, what is the default order of displaying the data .

Will SQL Server show the data in a consistent order?

Thanks.

View 6 Replies View Related

How To Display Result Of Different Year In Different Column Instead Of Different Row?

Feb 7, 2006

I am trying to display component usage in January for the past year, if I want to display the year in different column, what should I do?
eg.
component_id   component_description   qty_used_on_2005_Jan   qty_used_on_2006_Jan
C58B0BDD       tape drive                             2                                              3
Currently I am using this sql:
select cast(year(date_complete) as varchar(10)) + ' Jan' as Year, component_id, component_description,sum(component_qty) as total_qty_used
from view_jobComponent
where month(date_complete) = 1group by component_id, component_description, cast(year(date_complete) as varchar(10)) + ' Jan'order by component_id, component_description
which I will get something like this:
Year          component_id   component_description   total_qty_used
2005 Jan    C58B0BDD       tape drive                             22006 Jan    C58B0BDD       tape drive                             3

View 9 Replies View Related

T-SQL (SS2K8) :: Display Result In Required Order

Jun 17, 2014

I have an issue while display the result in the required order. How to get the required output.

Code :

USE tempdb
GO

IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL
DROP TABLE #VersionFormat_tbl
CREATE TABLE #VersionFormat_tbl
(
[FormatID] [smallint] NOT NULL,
[Description] [varchar](50) NULL,

[Code] ....

Present output :
fileExtension FormatID Description fileExtension versionFormatTypeId
txt 1 Text txt 1
html 2 HTML html 1
xml 3 XML xml 1
pdf 4 PDF pdf 1
xls 5 Excel xls 1
doc 6 Word doc 1

Required output:
fileExtensionFormatIDDescriptionfileExtensionversionFormatTypeId
html2HTMLhtml1
xls5Excelxls1
doc6Worddoc1
pdf4PDFpdf1
txt1Texttxt1
xml3XMLxml1

View 5 Replies View Related

How To Use A Function To Format And Display Result From Data Reader

Feb 5, 2008

Hi guys n gals !
I am having a few problems manipulating the results of my data reader,To gather the data I need my code is:
        // database connection        SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]);
        // sql statement to select latest news item and get the posters name        SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon);                // open connection        dbcon.Open();
        // execute        SqlDataReader dr = rs.ExecuteReader();
        // send the data to the repeater        repeater_LatestNews.DataSource = dr;        repeater_LatestNews.DataBind();
Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is:
    // prevent html    public string StripHtml(string data)    {        // grab the data        string theData = data;
        // replace < with &alt;        theData = Regex.Replace(theData, "<", "&lt;");                // return result        return theData;        }
But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???

View 2 Replies View Related

Change Display Of Result Set From Showing As Rows To Column As One Or More

May 13, 2014

I am interested in changing the way that data is displayed in my result set.Essentially I want to display a selection of rows (1 to n) as columns, the following diagram explains my intentions.Perhaps one of the greatest challenges here is the fact that I do not have a concrete number of rows (or BIN numbers). Each stock item could be stored in one or more BINS, which I will not know until running my query.

View 2 Replies View Related

Display A Group In A Report That Has No Data Rows In The Result Set

Dec 18, 2007

Hello

I have a report that retrieves its data from Analysis Services. The data includes a count and dollar value of projects against their current status: It looks something similar to



(group1) status1 10 $200,000

(detail) p1 1 $5,000

p2 1 $10,000

.

.

p10 1 $20,000



(group1) status3 5 $90,000

(detail) .

.



(group1) status4 15 $150,000

(detail) .

.



In the report I hide the detail rows. I have a fixed/known number of statuses (in this case 4) and need to show all 4 in the report. eg



(group1) status1 10 $200,000

(detail) p1 1 $5,000

p2 1 $10,000

.

.

p10 1 $20,000



(group1)status2 0 $0



(group1) status3 5 $90,000

(detail) .

.



(group1) status4 15 $150,000

(detail) .

.

ie in this case I need to show status 2 (that doesn't exist in the data set) with zero totals.



Does anyone know if this is possible to get SSRS to display each of the status groups (in a known fixed list) and then match them to the records in the dataset.

As an alternative, if I were using SQL Server I could add rows to the dataset using a union statement. Is there similar functionality using mdx? My mdx skills are very basic.



Thanks


Stewart

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

Do Not Display The Result Of My Long Text String, Approx About 400 Characters

Mar 26, 2004

I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??

SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE

WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
.......

View 1 Replies View Related

How To Display The Search Result Of A BookStore With Pictures Of Books, In A Proper Format.

Jan 22, 2007

Hi all,
I am using ASP.NET 2003 with SQL Server as database. 
I have a database of a book store with BookPicture, Author, Title, and Description of the Book.
Now when the user searchers for a  book with a keyword, how can I display the results which should show:
1. The picture of the book, 2. The at it right, Title of Book, 3. The author,4. The descritionThen the image of "Add to cart"
Each search result must be separated by a box like the cell of a table. 
Is it possible to be done?
Thanking you in advance
Tomy
 

View 2 Replies View Related

How To Access Data From Different Database And Display Result Set In Managed Stored Procedure

Jan 31, 2008

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Text
Imports System.Collections

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _
ByVal strCompany As String, _
ByVal decSerial As Decimal, _
ByVal strSerialCode As String, _
ByVal strSerialScan As String, _
ByVal decMasterSerialNumber As Decimal, _
ByVal strCustomerPart As String, _
ByVal strTakataPart As String, _
ByVal strCustomerRanNo As String, _
ByVal strCustomerAbv As String, _
ByVal strDestinationAbv As String, _
ByVal decQty As Decimal, _
ByVal strCreatDate As String, _
ByVal decVoidSerialNo As Decimal, _
ByVal strProductionLineNo As String, _
ByVal strProcType As String)

Dim sp As SqlPipe = SqlContext.Pipe
Dim strResult As Integer = 0
Dim strErrorText As String = String.Empty
Dim dsData As New DataSet
Dim parameter(15) As OleDbParameter
If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then
' Populate parameter collection

parameter(0) = (CreateParameter("PARM1", OleDbType.Char, 20, ParameterDirection.InputOutput, strAS400ServerName))
parameter(1) = (CreateParameter("PARM2", OleDbType.Char, 2, ParameterDirection.InputOutput, strCompany))
parameter(2) = (CreateParameter("PARM3", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decSerial))
parameter(3) = (CreateParameter("PARM4", OleDbType.Char, 2, ParameterDirection.InputOutput, strSerialCode))
parameter(4) = (CreateParameter("PARM5", OleDbType.Char, 25, ParameterDirection.InputOutput, strSerialScan))
parameter(5) = (CreateParameter("PARM6", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decMasterSerialNumber))
parameter(6) = (CreateParameter("PARM7", OleDbType.Char, 30, ParameterDirection.InputOutput, strCustomerPart))
parameter(7) = (CreateParameter("PARM8", OleDbType.Char, 15, ParameterDirection.InputOutput, strTakataPart))
parameter(8) = (CreateParameter("PARM9", OleDbType.Char, 15, ParameterDirection.InputOutput, strCustomerRanNo))
parameter(9) = (CreateParameter("PARM10", OleDbType.Char, 6, ParameterDirection.InputOutput, strCustomerAbv))
parameter(10) = (CreateParameter("PARM11", OleDbType.Char, 6, ParameterDirection.InputOutput, strDestinationAbv))
parameter(11) = (CreateParameter("PARM12", OleDbType.Decimal, 9, ParameterDirection.InputOutput, decQty))
parameter(12) = (CreateParameter("PARM13", OleDbType.Char, 10, ParameterDirection.InputOutput, strCreatDate))
parameter(13) = (CreateParameter("PARM14", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decVoidSerialNo))
parameter(14) = (CreateParameter("PARM15", OleDbType.Char, 3, ParameterDirection.InputOutput, strProductionLineNo))
parameter(15) = (CreateParameter("PARM16", OleDbType.Char, 2, ParameterDirection.InputOutput, strProcType))

RunDB2Sp("FABLE.MAP", parameter, dsData)

If dsData.Tables.Count > 0 Then
dsData.Tables(0).TableName = "Supreeth"
Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString()
Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString()

' I am not sure here
SqlContext.Pipe.Send(bitresult)
SqlContext.Pipe.Send("No errors")


End If

Else
Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid")
End If

End Sub

Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet)
'*********************************************
' Declare Variables
'*********************************************
Dim daAdaptor As OleDbDataAdapter
Dim cmdAS400 As OleDbCommand
'Dim dstestMe As New DataSet
Try
cmdAS400 = CreateCommand(strProcedure, parms)
daAdaptor = New OleDbDataAdapter(cmdAS400)

' Fill the Data Set
daAdaptor.Fill(dsData)
Catch expError As OleDbException
daAdaptor = Nothing
Finally
daAdaptor = Nothing
cmdAS400.Dispose()
'Me.Close()

End Try

End Sub
Public Shared Function CreateParameter(ByVal name As String, _
ByVal type As OleDbType, _
ByVal size As Integer, _
ByVal direction As ParameterDirection, _
ByVal paramValue As Object) As OleDbParameter
Dim param As OleDbParameter = New OleDbParameter
param.ParameterName = name
param.OleDbType = type
param.Size = size
param.Direction = direction
param.Value = paramValue
Return param
End Function

Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand
Dim CmdSAS400 As OleDbCommand
Dim parameter As OleDbParameter
Dim connAS400 As OleDbConnection
connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;")
connAS400.Open()

CmdSAS400 = connAS400.CreateCommand()
CmdSAS400.CommandText = strProcedure
CmdSAS400.CommandType = CommandType.StoredProcedure
CmdSAS400.Parameters.Clear()
'CmdAS400.CommandTimeout = intTimeOut
If (prams Is Nothing) Then
Else
For Each parameter In prams
CmdSAS400.Parameters.Add(parameter)
Next

End If

Return CmdSAS400

End Function

I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset.
My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs

Thanks

View 3 Replies View Related

How Can I Send Row Data To A SQL Database Table Via VB 2008, And Permanently Save And Display The Result?

Mar 11, 2008

This is related to:
How can I make some graphics drawings stick while others disappear?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1


Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called €œHexMap€? that contains some columns that I am ready to insert some row data into. Here is what my program should do:

As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor. If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database. Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:

http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg
-sorry, I didn't capture the mouse cursor inside the red hexagon

I think I am missing something since I appear to be able to connect successfully to the database table. Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer). I am hoping someone will review my code snippet (below) and tell me what I am missing. What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.




Code Snippet
'======================================================================================
Dim CN As New SqlClient.SqlConnection()
Dim da As New SqlClient.SqlDataAdapter

'Consider using Me._adapter that is used already

CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
CN.Open()

'Use the following code to verify that a connection to the database has achieved
If CN.State = ConnectionState.Open Then


MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")
End If

Try

Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)
End Try

'use the Connection object to execute statements
'against the database and then close the connection
da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)

If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================

Dim rows As Integer

rows = 0

Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)

CN.Open()

rows = CMD.ExecuteNonQuery

If rows = 1 Then
MsgBox("Table HexMap updated successfully")
Else
MsgBox("Failed to update the HexMap table")
End If


If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================



Thanks for reviewing my code.

Technozoide

View 1 Replies View Related

SQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?

Feb 26, 2015

Is there a way to display a column alias as part of the result set column labels?

View 9 Replies View Related

One Table, Two Condition, Display Result As One Table

Nov 29, 2004

I got one table with 3 columns = Column1, Column2, Column3

Sample Table

Column1 | Column2 | Column3
------------------------------------
A | 12 | 0
A | 13 | 2
B | 12 | 5
C | 5 | 0

Select Column1, Column2, Column3 as New1
Where Column1 = A AND Column2 = 12 AND Column3 = 0

Select Column1, Column2, Column3 as New2
Where Column1 = A AND Column2 = 12 AND Column3 >0

The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table.
So how do i display the result in one table where the new Output will be in this manner

Column1 | Column2 | New1 | New2|

Thanks

View 3 Replies View Related

Easy SQL Question. How To Display Query Results In Query Analyzer

Feb 12, 2008

When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare     @SniierId as   uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare    @SniierAlias  as nvarchar(50)declare    @AlwaysShowEditButton  as bitdeclare     @SniierName  as  nvarchar (128)/* Check access for Sniier */SELECT TOP 1       @SniierName      = Sniiers.SniierName,        @SniierAlias    = Sniiers.SniierAlias,        @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId

View 3 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View 1 Replies View Related

Same Query Gives Result With Different Column Sequence When Used In Query Analyzer

Feb 25, 2012

When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.

result in Excel 2003 SQL OLE DB query

col-A col-B col-C
values...

Result with Query Analyzer and VBA Macro

col-c col-B col-A
values...

View 3 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB        123
colA        XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

Return Result From One Query As A Column In Other Query

Jun 3, 2004

I'm having a bit of a trouble explaining what I'm trying to do here.

I have 3 "source" tables and a "connecting" table that I'm going to use

tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)

They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID

One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:

ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]

I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)

Kirikiri

View 1 Replies View Related

Save Query Result At CSV Or TXT File From The Query

Dec 10, 2007

We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.

Thanks
Amit K Patel

View 7 Replies View Related

How Do I Write A Query To Display Top 9 Plus Others

Feb 12, 2007

I have query that return statuses and their totals, the data looks like:
StatusCode     Total----------    ---------A              100PS             50SI1            9SI2            8etc...
The query returns over 30 totals, but I only want the top 9 totals plus others (the rest of the totals combined) .
How do I go about this query?
StatusCode     Total----------    ---------A              100PS             50SI1            19SI2            18SI3            9SI4            8PS1            6PS2            6PS3            6Others         99 
 Also, have it work for queries that have 9 or less totals?
 
 
 
 
 
 
 
 

View 7 Replies View Related

SQL Query - How To Display Row Two And Onwards?

Sep 26, 2007

Hi,
I have this query:
Dim strSQL As String = "SELECT Ticketreply.Status, TicketReply.ReplyID, TicketReply.AssignedTo, TicketReply.ReplyName, TicketReply.Reply, TicketReply.Attachment, TicketReply.LastUpdated, TicketReply.Priority FROM TicketReply INNER JOIN TicketThread ON TicketThread.TicketID = @TicketID WHERE (TicketReply.TicketID = @TicketID) ORDER BY TicketReply.LastUpdated"
How in the gridview do I display all the results bar the first record?
It does this at the moment:
First
Second
Third
I want it to display like this:
Second
Third
So it misses the first row.
Hopefully easy for you Pro's!
 Any help appreciated.
Cheers, Ricky

View 1 Replies View Related

Query To Display Sum In Different Ways

May 24, 2014

I have I rather complex query and need to display a Sum in different ways. Now I'm wondering if the performance improves if you nest the queries in the described way. To me it looks, as if the sum just has to be calculated once?

select SUM(tiempo) as time_minutes,
CONVERT(varchar(5), dateadd (minute,sum(tiempo),'1900-1-1 0:00'), 114) as time_hours,
SUM(time)* 0.95 as time_discount
from table

Select tiempo as time_minutes,
CONVERT(varchar(5), dateadd (minute,tiempo,'1900-1-1 0:00'), 114) as time_hours,
tiempo * 0.95 as time_discount
From (
Select Sum(tiempo) as tiempo
from table)a

View 2 Replies View Related

Query To Display Max Values For Each ID

Jun 5, 2015

I have table that contains below data

Date                              ID            
Message
2015-05-29 7:00:00      AOOze            abc
2015-05-29 7:05:00      AOOze            start
2015-05-29 7:10:00      AOOze            pqy
2015-05-29 7:15:00      AOOze            stop
2015-05-29 7:20:00      AOOze            lmn     

[code]....

and so on following the series for every set of different ID with 5 entries.I need to Find Maximum interval time for each ID and for condition in given message (between Start and Stop)For example, in above table

-For ID AOOze, in message "start" is logged at 7:05 and stop and 7:15, so interval is 10 mins
-For ID LaOze, in message "start" is logged at 7:30 and stop and 7:45, so interval is 15 mins

I am looking for a sql query that will return in below format

ID     MAX interval
AOOze   15
LaOze   10

View 15 Replies View Related

How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).

Jan 28, 2008

Hi, 
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. 
I have three tables namely – Forums,Topics and Threads  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC 
Whose result set is as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  
On modifying the query to:- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC 
I get the result set as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
4
Swings
p@q.com
2008-01-27 15:12:51.000

I want the result set as follows:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. 
The scripts for creating the tables and inserting test data is as follows in an already created database:- 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO 
CREATE TABLE [dbo].[Forums] (
            [forumid] [int] IDENTITY (1, 1) NOT NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Threads] (
            [threadid] [int] IDENTITY (1, 1) NOT NULL ,
            [topicid] [int] NOT NULL ,
            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [replies] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Topics] (
            [topicid] [int] IDENTITY (1, 1) NOT NULL ,
            [forumid] [int] NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Forums] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [forumid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Threads] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [threadid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [topicid]
            )  ON [PRIMARY]
GO  
ALTER TABLE [dbo].[Threads] ADD
             FOREIGN KEY
            (
                        [topicid]
            ) REFERENCES [dbo].[Topics] (
                        [topicid]
            )
GO 
ALTER TABLE [dbo].[Topics] ADD
             FOREIGN KEY
            (
                        [forumid]
            ) REFERENCES [dbo].[Forums] (
                        [forumid]
            )
GO  
------------------------------------------------------ 
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum'); 
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion'); 
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
 

View 7 Replies View Related

Getting Result Of Query Inside Another Query

May 22, 2007

Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.

View 4 Replies View Related

How To Get Sum() Result In Query?

Mar 15, 2008

mail_delivery_master----------------------ml_id       ml_from_mail_id                          ml_to_mail_id                                ml_user_id      ml_subject                     ml_content             ml_file_attached  ml_no_of_file_attached ----------- ---------------------------------------- -------------- ------------ --------------------------------------------------------------------------------------------------------------------------------1           aa                                       bb                                                cc              dd                             ee                                                                                                                                                                                                                                                                            2           joe@hotmail.com                          tt@gmail.com,                                   JOHNYJP         Forum answer                   somebody answer          0                1                                                                                                                                                                                                                                                                      3           joe@hotmail.com                          mailme@hotmail.com,                             JOHNYJP         Forum answer                   somebody answer          -1               0                                                                                                                                                                                                                                                              4           joe@hotmail.com                          mailme@hotmail.com,janani@ajsquare.net,         JOHNYJP         Forum answer                   somebody answer           0                0                                                                                                                                                                                                                                                             5           joe@hotmail.com                          janani@ajsquare.net,                            JOHNYJP         Forum answer                   somebody answer          0                0                                                                                                                                                                                                                                                               6           joe@hotmail.com                          dff@gg.com,                                     JOHNYJP         Forum answer                   somebody answer           0                0                                                                                                                                                                                                                                                           7           joe@hotmail.com                          tt@gmail.com,janani@ajsquare.net,               JOHNYJP         Forum answer                   somebody answer          0                0                                                                                                                                                                                                                                                             8           Durai@gamial.com                         yogesh@gamail.com                               durai           test                           test                      0                0                                                                                                                                                                                                                                                                9           janani@ajsquare.net                      devi@ajsquare.net                               janu            test                           hi                        0                0                                                                                                                                                                                                                                                             10          janani@ajsquare.net                      devi@ajsquare.net                               JANANI          test                           Miss u                   -1               2                mail_attachementsml_id       ml_file_name                 ml_file_format       ml_file_size      ml_file_select_from                                                                                  ----------- ---------------------------- -------------- ------------ ------------------------------------------ 1           chocolate                       .jpg                         114528            F: empchocolate.pjg                                                                                2           Tiger Caspian Blue.jpg          .jpg                     1114407      F:imageTiger Caspian Blue.jpg                                                                      3           Autumn.jpg                      .jpg                         66287        F:imageAutumn.jpg                                                                                  4           Ascent.jpg                      .jpg                           63244        F:imageAscent.jpg                                                                                  5           Azul.jpg                        .jpg                              61365        F:imageAzul.jpg                                                                                    6           daisy.jpg                       .jpg                             8197         F:imagedaisy.jpg                                                                                   7           Stonehenge.jpg                  .jpg                       59600        F:imageStonehenge.jpg                                                                              i want the result :ml_id , ml_from_mail_id, ml_to_mail_id  from mail_delivery_master   ,,,, and sum(ml_file_size) for that mail from mail_attachements give me the code solution regards samuel chandradoss      

View 2 Replies View Related







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