Queries With Different Statements - Show Results In Datagridview
Oct 19, 2015
I have made a couple of queries I want to use into a Visual studio project, Now is my problem:
All my queries have different statements. I believe the best is to show the results in a Datagridview
How to view them there? I know how to do it... but I have got about 30 queries
Here is my code so far:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = MyDB_DevDataSet.DataSetName.
Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True"
Dim sql As String = "SELECT * FROM CompanyMain"
[Code] ....
View 2 Replies
ADVERTISEMENT
Jan 11, 2007
Hi guys,
Im having some issues understanding how to manipulate the data that is in my DataGridView
The View is created by calling a stored procedure from VB code and works on two tables:
people
pswds
The columns required from Table people are (personid, userid and sort).
The columns required from Table pswds are (comment and activeuser).
Table people is parent table while pswds is the child table.
The Relationship works between both tables using primary keys from both tables: which is called personid in both.
The link below shows the Relationship Diagram for the Two tables:
http://cale.csd.anglia.ac.uk/~csd03ib/images/RelationTables.bmp
The code for the Stored procedure can be found at the following Link:
http://cale.csd.anglia.ac.uk/~csd03ib/images/StoredProcedure.bmp
Now the DataGridView returned by the Stored procedure produces the following output found at this link:
http://cale.csd.anglia.ac.uk/~csd03ib/images/DataGridView.bmp
Of course there are many more results returned for this DataGridView but i have only shown a few of them. What i really need to know is:
Can this view be made to be editable?
I need to be able to update the both the Active user Check box and the Comment Columns from this view
I hope someone can help
Thx Flakky
View 8 Replies
View Related
Jun 18, 2008
I have two SQL queries that I would like to combine. Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query. I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or if it would solve my question. Do you have any ideas or examples of how to solve this scenario?
View 9 Replies
View Related
Nov 5, 2015
Currently I have this query which I am inserting into budterminals table
--Insert
INSERT INTO budterminals([TERMINALNUMBER], [ACCOUNTINGUNIT], [TERMINALNAME], [MBFTERMINALNAME], [SOURCESYSTEM], [COMPANYCODE], [STATUSID], [CREATEDUSERID], [CREATEDDATETIME], [LASTMODIFIEDUSERID] , [LASTMODIFIEDDATETIME])
SELECT CAST(left(TerminalName, patindex('%[^0-9]%', TerminalName+'.') - 1) as int)
,''
,SUBSTRING(TerminalName , CHARINDEX('-' , TerminalName) + 1, LEN(TerminalName))
[Code].....
Next I am inserting into budcustomers table
--Customer
INSERT INTO [dbo].[BudCustomers]
([LegalName]
,[EffectiveDate]
,[LawsonCustomerNumber]
,[ChangeReason]
,[ImportedRecord]
[Code] .....
Next I am inserting into budcontracts table but here in this table to insert i want the inserted terminalid and customerid column which are unqiue identity based.
So how to do that here in this query
--Contracts
INSERT INTO [dbo].[BUDCONTRACTS]
([CONTRACTNAME]
,[CONTRACTNUMBER]
,[VERSIONSTARTDATE]
,[VERSIONTERMINATIONDATE]
[Code] .....
View 3 Replies
View Related
Jan 29, 2004
Hello-
i have a fairly big SQL query that is used to display data into a datagrid. Each query grabs data from two seperate databases. Is there anyway to combine these queries into one so all the data appears in 1 datagrid and not 2.
here is the 1st query:
SQL = "SELECT sum(case when month(pb_report_shippers.shipper_date_time) = 1 then pb_report_shippers_lots.quantity else 0 end) as Jan ,sum(case when month(pb_report_shippers.shipper_date_time) = 2 then pb_report_shippers_lots.quantity else 0 end) as Feb ,sum(case when month(pb_report_shippers.shipper_date_time) = 3 then pb_report_shippers_lots.quantity else 0 end) as Mar ,sum(case when month(pb_report_shippers.shipper_date_time) = 4 then pb_report_shippers_lots.quantity else 0 end) as Apr ,sum(case when month(pb_report_shippers.shipper_date_time) = 5 then pb_report_shippers_lots.quantity else 0 end) as May ,sum(case when month(pb_report_shippers.shipper_date_time) = 6 then pb_report_shippers_lots.quantity else 0 end) as Jun ,sum(case when month(pb_report_shippers.shipper_date_time) = 7 then pb_report_shippers_lots.quantity else 0 end) as Jul ,sum(case when month(pb_report_shippers.shipper_date_time) = 8 then pb_report_shippers_lots.quantity else 0 end) as Aug ,sum(case when month(pb_report_shippers.shipper_date_time) = 9 then pb_report_shippers_lots.quantity else 0 end) as Sept ,sum(case when month(pb_report_shippers.shipper_date_time) = 10 then pb_report_shippers_lots.quantity else 0 end) as Oct ,sum(case when month(pb_report_shippers.shipper_date_time) = 11 then pb_report_shippers_lots.quantity else 0 end) as Nov ,sum(case when month(pb_report_shippers.shipper_date_time) = 12 then pb_report_shippers_lots.quantity else 0 end) as Dec FROM pb_customers INNER JOIN pb_jobs ON pb_customers.customer_id = pb_jobs.customer_id INNER JOIN pb_recipes_sub_recipes ON pb_jobs.recipe_id = pb_recipes_sub_recipes.recipe_id INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id INNER JOIN pb_report_shippers_lots ON pb_jobs_lots.intrack_lot_id = pb_report_shippers_lots.intrack_lot_id INNER JOIN pb_report_shippers ON pb_report_shippers_lots.job_id = pb_report_shippers.job_id AND pb_report_shippers_lots.shipper_id = pb_report_shippers.shipper_id WHERE pb_customers.customer_deleted <> 1 AND pb_jobs.job_deleted <> 1 AND pb_jobs_lots.lot_deleted <> 1 AND pb_report_shippers.shipper_date_time between cast('01/01/2003 00:01AM' as datetime) and cast('12/31/2003 23:59PM' as datetime)"
Here is the 2nd query:
SQL = "SELECT ISNULL(sum(case when month(nonconformance.nc_date) = 1 then nonconformance.nc_wafer_qty else 0 end),0) as Jan , ISNULL(sum(case when month(nonconformance.nc_date) = 2 then nonconformance.nc_wafer_qty else 0 end),0) as Feb ,ISNULL(sum(case when month(nonconformance.nc_date) = 3 then nonconformance.nc_wafer_qty else 0 end),0) as Mar ,ISNULL(sum(case when month(nonconformance.nc_date) = 4 then nonconformance.nc_wafer_qty else 0 end),0) as Apr , ISNULL(sum(case when month(nonconformance.nc_date) = 5 then nonconformance.nc_wafer_qty else 0 end),0) as May ,ISNULL(sum(case when month(nonconformance.nc_date) = 6 then nonconformance.nc_wafer_qty else 0 end),0) as Jun ,ISNULL(sum(case when month(nonconformance.nc_date) = 7 then nonconformance.nc_wafer_qty else 0 end),0) as Jul ,ISNULL(sum(case when month(nonconformance.nc_date) = 8 then nonconformance.nc_wafer_qty else 0 end),0) as Aug ,ISNULL(sum(case when month(nonconformance.nc_date) = 9 then nonconformance.nc_wafer_qty else 0 end),0) as Sept ,ISNULL(sum(case when month(nonconformance.nc_date) = 10 then nonconformance.nc_wafer_qty else 0 end),0) as Oct ,ISNULL(sum(case when month(nonconformance.nc_date) = 11 then nonconformance.nc_wafer_qty else 0 end),0) as Nov ,ISNULL(sum(case when month(nonconformance.nc_date) = 12 then nonconformance.nc_wafer_qty else 0 end),0) as Dec FROM nonconformance INNER JOIN nc_department on nonconformance.department_id = nc_department.department_id INNER JOIN nc_major_category ON nonconformance.major_category_id = nc_major_category.major_category_id AND nonconformance.status_id <> '5' WHERE nc_department.scrap_category = '1' AND nonconformance.nc_date between cast('01/01/2004 00:01AM' as datetime) and cast('12/31/2004 23:59PM' as datetime)"
I know there has to be someway to combine these into 1. The issue I have is they are in different databases.
ANY HELP would be appreciated.
View 2 Replies
View Related
Apr 24, 2006
Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.
Here's the query:
update GCDE_SEQ
set LAST_NO = (select max(FLD_NO)
from PONL_FLD)
,UPDT_USER = ?
,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'
Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?
View 8 Replies
View Related
Oct 29, 2007
I am trying to build a query for a report and I need both results for a column to show on the same row. The results are multiple names tied to the same account.
This is what I am trying.
SELECT
number AS 'Customer'
(case when seq = '0' then name end) as 'name1',
(case when seq = '1' then name end) as 'name2'
FROM customer
WHERE number between '600080' and '600230'
View 5 Replies
View Related
Apr 10, 2002
I have three tables, Employer, Customer and CustomerEmployer. The employer table contains all employers and the customeremployer table contains customer records for an employer. For example
Employer Table
EmployerID Name Address
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street
4 ZZZ Company 2 Rust Street
Customer Employer Table
ID EmployerID CustomerID
1 3 1
2 3 2
Customer Table
ID Name
1 Joe Smith
2 Jane Thomas
3 Tim James
I would like to run a select statement which lists all the employers and whether the employer has already been assigned to a customer. For example, for Joe Smith I would like to see all the employers listed AND some marker indicating that Joe is already assigned to EmployerID 3, JJJ Company.
Results
EmployerID Name Address CustomerID
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street 3
4 ZZZ Company 2 Rust Street
I have tried unions and many types of joins but none seem to work correctly. I was using the following select statement however only employers that are contained in the customeremployer table appear.
SELECT Employer.EmployerID, Employer.Name, Address, Employer.City,
CustomerEmployer.CustomerID
FROM CustomerEmployer RIGHT OUTER JOIN
Employer ON CustomerEmployer.EmployerID = Employer.EmployerID
WHERE (CustomerEmployer.CustomerID = @customerid) OR (CustomerEmployer.CustomerID IS NULL)
ORDER BY Name
Any ideas?
Thanks.
View 1 Replies
View Related
Sep 10, 2007
I have 2 Table
Authors
ID Name
1 Clint
2 Voke
Books
BookID ID BookName Price
1 1 Book1 10
2 1 Boo21 12
3 2 Book3 6
4 1 Book4 13
5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.
So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).
I want to Create NEw Views Which Shows my required Results.
thanks,
View 15 Replies
View Related
May 7, 2008
In my SPs, I commonly have a situation, where a SELECT statement gets a single scalar value (e.g. SELECT Name FROM Employee WHERE id=@id) from a table.
Because the result is still a relation, I cannot process it directly or assign the result to a variable
(like set @name = SELECT Name FROM Employee WHERE id=@id)
So, how can I process the results of the statement in this case.
In some other cases, the result is actually a relation. And I want to iterate over all rows, processing each row's columns.
(I know this smells of ADO.NET, but how can I help it if I am coming from that background)...
The point is I want to do all this in T-Sql on server side!!!
View 13 Replies
View Related
May 9, 2005
I have a sproc in my database that when editing in VS 2003 shows different results.
The sproc code is:
ALTER PROCEDURE dbo.VMUsage_GetRaw
@VMBox nvarchar,@StartDate datetime,@EndDate datetime
AS
SET NOCOUNT ON
SELECT *FROM VMRawUsageWHERE (ACCOUNT = @VMBox) AND (CONVERT(datetime, DATE + ' ' + TIME) > @StartDate) AND (CONVERT(datetime, DATE + ' ' + TIME) < @EndDate)
When I open the SQL statement in the designer and run (and enter my parameters) I get a recordset returned, but when I just "Run Stored Procedure" and enter the same parameters I get no results. The same occurs when I run the sproc from my website (no results).
Any ideas what is happening between the two?
View 1 Replies
View Related
Feb 5, 2015
My query produces accurate results just produces one instance for each sales region. I am needing it to only give me one instance of each sales region and give me total counts. What should I re-write?
Code:
Select
salesmanname,
case when [state] IN ('GA', 'FL', 'AL', 'SC', 'NC', 'TN') Then 'South'
when [state] IN ('CA', 'NV', 'WA', 'OR', 'TX') Then 'West'
when [state IN ('NY', 'NJ', 'PA', 'DL', 'CT') Then 'NE'
end As [Sales Region]
[Code] ....
View 1 Replies
View Related
Aug 22, 2014
We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.
SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,
[code]....
View 3 Replies
View Related
Nov 19, 2007
Hi there
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
Can someone help please.
Please ask if I haven't explained myself.
thanks
Dianne
View 9 Replies
View Related
Jul 17, 2006
Try this script to see what queries are taking over a second.To get some real output, you need a long-running query. Here's one(estimated to take over an hour):PRINT GETDATE()select count_big(*)from sys.objects s1, sys.objects s2, sys.objects s3,sys.objects s4, sys.objects s5PRINT GETDATE()Output is:session_id elapsed task_alloc task_dealloc runningSqlText FullSqlTextquery_plan51 32847 0 0 select count_big(*) from sys.objects s1, sys.objects s2,sys.objects s3, sys.objects s4, sys.objects s5 SQL PlanClicking on SQL opens the full SQL batch as a .txt file, including the PRINTstatementsClicking on Plan allows you to see the .sqlplan file in MSSMS========Title: Using a VB Script to show long-running queries, complete with queryplans.Today (July 14th), I found a query running for hours on a development box.Rather than kill it, I decided to use this opportunity to develop a scriptto show long-running queries, so I could see what was going on. (ReferenceRoy Carlson's article for the idea.)This script generates a web page which shows long-running queries with thecurrently-executing SQL command, full SQL text, and .sqlplan files. The fullSQL query text and the sqlplan file are output to files in your tempdirectory. If you have SQL Management Studio installed on the localcomputer, you should be able to open the .sqlplan to see the query plan ofthe whole batch for any statement.'LongestRunningQueries.vbs'By Aaron W. West, 7/14/2006'Idea from:'http://www.sqlservercentral.com/columnists/rcarlson/scriptedserversnapshot.asp'Reference: Troubleshooting Performance Problems in SQL Server 2005'http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspxSub Main()Const MinimumMilliseconds = 1000Dim srvnameIf WScript.Arguments.count 0 Thensrvname = WScript.Arguments(0)Elsesrvname = InputBox ( "Enter the server Name", "Server", ".", VbOk)If srvname = "" ThenMsgBox("Cancelled")Exit SubEnd IfEnd IfConst adOpenStatic = 3Const adLockOptimistic = 3Dim i' making the connection to your sql server' change yourservername to match your serverSet conn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")' this is using the trusted connection if you use sql logins' add username and password, but I would then encrypt this' using Windows Script Encoderconn.Open "Provider=SQLOLEDB;Data Source=" & _srvname & ";Trusted_Connection=Yes;Initial Catalog=Master;"' The query goes heresql = "select " & vbCrLf & _" t1.session_id, " & vbCrLf & _" t2.total_elapsed_time AS elapsed, " & vbCrLf & _" -- t1.request_id, " & vbCrLf & _" t1.task_alloc, " & vbCrLf & _" t1.task_dealloc, " & vbCrLf & _" -- t2.sql_handle, " & vbCrLf & _" -- t2.statement_start_offset, " & vbCrLf & _" -- t2.statement_end_offset, " & vbCrLf & _" -- t2.plan_handle," & vbCrLf & _" substring(sql.text, statement_start_offset/2, " & vbCrLf & _" CASE WHEN statement_end_offset<1 THEN 8000 " & vbCrLf & _" ELSE (statement_end_offset-statement_start_offset)/2 " & vbCrLf & _" END) AS runningSqlText," & vbCrLf & _" sql.text as FullSqlText," & vbCrLf & _" p.query_plan " & vbCrLf & _"from (Select session_id, " & vbCrLf & _" request_id, " & vbCrLf & _" sum(internal_objects_alloc_page_count) as task_alloc, " &vbCrLf & _" sum (internal_objects_dealloc_page_count) as task_dealloc " &vbCrLf & _" from sys.dm_db_task_space_usage " & vbCrLf & _" group by session_id, request_id) as t1, " & vbCrLf & _" sys.dm_exec_requests as t2 " & vbCrLf & _"cross apply sys.dm_exec_sql_text(t2.sql_handle) AS sql " & vbCrLf & _"cross apply sys.dm_exec_query_plan(t2.plan_handle) AS p " & vbCrLf & _"where t1.session_id = t2.session_id and " & vbCrLf & _" (t1.request_id = t2.request_id) " & vbCrLf & _" AND total_elapsed_time " & MinimumMilliseconds & vbCrLf & _"order by t1.task_alloc DESC"rs.Open sql, conn, adOpenStatic, adLockOptimistic'rs.MoveFirstpg = "<html><head><title>Top consuming queries</title></head>" & vbCrLfpg = pg & "<table border=1>" & vbCrLfIf Not rs.EOF Thenpg = pg & "<tr>"For Each col In rs.Fieldspg = pg & "<th>" & col.Name & "</th>"c = c + 1Nextpg = pg & "</tr>"Elsepg = pg & "Query returned no results"End Ifcols = cdim filenamedim WshShellset WshShell = WScript.CreateObject("WScript.Shell")Set WshSysEnv = WshShell.Environment("PROCESS")temp = WshShell.ExpandEnvironmentStrings(WshSysEnv("TEMP")) & ""filename = temp & filenameDim fso, fSet fso = CreateObject("Scripting.FileSystemObject")i = 0Dim cDo Until rs.EOFi = i + 1pg = pg & "<tr>"For c = 0 to cols-3pg = pg & "<td>" & RTrim(rs(c)) & "</td>"Next'Output FullSQL and Plan Text to files, provide links to themfilename = "topplan-sql" & i & ".txt"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-2)f.Closepg = pg & "<td><a href=""" & filename & """>SQL</a>"filename = "topplan" & i & ".sqlplan"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-1)f.Closepg = pg & "<td><a href=""" & filename & """>Plan</a>"'We could open them immediately, eg:'WshShell.run temp & filenamers.MoveNextpg = pg & "</tr>"Looppg = pg & "</table>"filename = temp & "topplans.htm"Set f = fso.CreateTextFile(filename, True, True)f.Write pgf.CloseDim oIESET oIE = CreateObject("InternetExplorer.Application")oIE.Visible = TrueoIE.Navigate(filename)'Alternate method:'WshShell.run filename' cleaning uprs.Closeconn.CloseSet WshShell = NothingSet oIE = NothingSet f = NothingEnd SubMain
View 1 Replies
View Related
Sep 10, 2007
I have 2 TableAuthorsID Name1 Clint2 Voke
BooksBookID ID BookName Price1 1 Book1 10 2 1 Boo21 12 3 2 Book3 6 4 1 Book4 13 5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results.
thanks,
View 2 Replies
View Related
Feb 4, 2015
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
FROM VENDFIL A, VENDFIL B, VENDFIL C
GROUP BY A.FEDTID, B.VENDOR
View 5 Replies
View Related
Oct 31, 2007
Hello Frds,
This is my query
Select Assessment_Id,Question_Id,Question_short_description,
(isnull(Answer_description,' ') + ' ' + Answer_text) as Answer
from dbo.viewX where assessment_id ='xxxx'
Pleae Note: This query is assigned to a string and passed to the database layer. So, i need the modifications with in this query.
Answer_description and Answer_text are the two different fields,
i need to embed a ":" (colon) between the two data if Answer_text is having any data
else display only answer_description.
Thanks in advance..
Phani.
View 17 Replies
View Related
Sep 1, 2004
Hi,
I would like to use the result table of the first query in a number of other queries. How do I do this ?
Thanks.
View 2 Replies
View Related
May 4, 2005
I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table. However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results. What would be the best method to combine searches?At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries. Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)e.g.1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Combined using my function creates:Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))
When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level') I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?
View 1 Replies
View Related
Nov 9, 2005
I'm trying to create a list of orders in my db that has been created correctly (some orders are not dealt with correctly...) An order should go from "open -> assigned" to "assigned -> responded" status.
I got the following query:
select org.name, count(order) AS correct, NULL AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id
where
o1.status = 'Open -> Assigned'
and o2.status = 'Assigned -> Responded'
and o1.time_stamp < o2.time_stamp
This gives me a list of all organisations with the correct number of orders in the system...
But now I need to add the total number of tickets they got in the system. So I was thinking about a union with a query without the were constraints
UNION 'with the above query
select org.name, NULL AS correct, count(order) AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id
..but that gives me a list like this:
name correct total
org1 324 NULL
org1 NULL 423
How can I combine them, or maybe doing it a better way?
View 3 Replies
View Related
Jun 21, 2007
Hi,
I have to queries that return tables with the same names. How do i add these 2 so it returns one table?
Thanks for your help.
Mike
View 7 Replies
View Related
Jul 20, 2005
Hi all,Here is my problem, I have 3 tables :People-------------IDPeopleFirstnameLastnameCars------------IDPeopleCarnameBoats------------IDPeopleBoatname1 person can have 0 or n car/boatI want to a result set displaying : Firstname, Lastname, NumberOfCars,NumberOfBoatsI have two queries, but i want to merge the results in one. how can i dothis ?This one gives me FIRSTNAME, LASTNAME and CARCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Cars.CarName) AS CARCOUNTFROM dbo.People LEFT OUTER JOINdbo.Cars ON dbo.People.IDPeople = dbo.Cars.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThis one gives me FIRSTNAME, LASTNAME and BOATCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Boats.BoatName) AS BOATCOUNTFROM dbo.People LEFT OUTER JOINdbo.Boats ON dbo.People.IDPeople = dbo.Boats.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThanks in advancePhil
View 2 Replies
View Related
Jul 20, 2005
Does anyone know how I can 'join' the results ofone SQL query to the bottom of another?Eg. I have two queries:1. SELECT Name, Surname FROM People WHERE Surname = SmithNAME SURNAMEAdam SmithJohn SmithMichael SmithSteve Smith2. SELECT Name, Surname FROM People WHERE Surname = JonesNAME SURNAMEBob JonesLarry JonesTom JonesWhat I want to produce is:NAME SURNAMEAdam SmithJohn SmithMichael SmithSteve SmithBob JonesLarry JonesTom JonesHowever, if I use UNION like this:SELECT Name, Surname FROM People WHERE Surname = SmithUNIONSELECT Name, Surname FROM People WHERE Surname = Jonesit mixes up all the results:NAME SURNAMEAdam SmithBob JonesJohn SmithLarry JonesMichael SmithSteve SmithTom Jones(I guess it's sorting by the first field, NAME).Is there a way to stop it sorting the results, so that itjust tacks the second query results to the bottom of thefirst query results?(I realise I could use "ORDER BY Surname" to get the same resultin this simple example, but for the more complicated queriesI want to use it won't work).Thanks for any help,Matt.
View 3 Replies
View Related
May 29, 2008
Hi,
I am new to MDX and I have created a query listed below, this returns the correct information from the cube. However when I split the query into a CREATE SET and Query the data returned is wrong. I need to include the set creation in the cube but this returns the wrong information. I thought that information returned by these two queries would be indentical can anyone explain please.
Thanks David
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0) on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
----------------------------------------------------------------------------------------------------------------------------------------
create SET [Finance].[LFL Stores List] AS
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0)
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
[LFL Stores List] on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
View 8 Replies
View Related
Nov 12, 2007
hi,
my first query is:
"SELECT TBL_STOK.stok_adi, TBL_STOK.fiyat1 FROM TBL_STOK INNER JOIN" _
TBL_BARKOD ON TBL_STOK.stok_id = TBL_BARKOD.stok_id " _
where TBL_BARKOD.barkod=@barkod"
second query :
"SELECT TBL_STOKDEPO.fiyat1 FROM TBL_BARKOD left outer JOIN TBL_STOKDEPO ON TBL_BARKOD.stok_id = TBL_STOKDEPO.stok_id" _
where TBL_BARKOD.barkod=@barkod and TBL_STOKDEPO.depo_kod=@depokod "
i want to merge these queries' results.first query returns 2 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1)
second query returns 1 column (TBL_STOKDEPO.fiyat1) .but i want a query that returns 3 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1,TBL_STOKDEPO.fiyat1)
View 8 Replies
View Related
Jan 25, 2008
Hello all,
I have been using T-SQL for a while now although the majority of my work required relativley simple queries.
I just need to know is there a way to JOIN the results of several SELECT queries, maybe through the use of functions??
A reference to any online article would be most helpful.
Cheers,
Sean
View 6 Replies
View Related
Jan 28, 2014
These separate COUNT queries are very fast:
SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
But I want to add the COUNT statements, so this is what I did:
select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount
FROM location_views vws,extlinks lnks
WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357)
OR
(lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)
Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?
View 7 Replies
View Related
Jul 23, 2005
I'm trying to devise a query for use on SQL Server 2000 that will dowhat was previously done with one query in MS Access. The MS Accessquery was like this:SELECT Count(*) as [Opened],Abs(Sum([Status] Like 'Cancel*')) As [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened)Order By Year(DateOpened), Month(DateOpened)Here were I'm at with SQL Server, TSQLSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Opened]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yieldsMonthOpened======================Aug 2004503Sep 2004752Oct 2004828Nov 2004658Dec 2004533Jan 2005736Feb 2005707Mar 2005797Apr 2005412AndSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus like 'Cancelled%'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yields;MonthCancelled=========================Aug 200478Sep 2004105Oct 2004121Nov 2004106Dec 200475Jan 200582Feb 200571Mar 200594Apr 200533What is desired isMonthOpenedCancelled============================Aug 200450378Sep 2004752105Oct 2004828121Nov 2004658106Dec 200453375Jan 200573682Feb 200570771Mar 200579794Apr 200541233Any assistance would be appreciated.Cheers;Bill
View 3 Replies
View Related
Mar 5, 2012
Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
View 6 Replies
View Related
Oct 12, 2012
I am running a SQL stored procedure which runs 3 queries on 3 different SQL tables. What is my best option to export the results of these 3 queries to excel?
If it matters they are all SELECT queries, and at most will return < 500 rows.
View 6 Replies
View Related
Dec 21, 2005
Hello,I am trying to create a view that shows the followingField1: Sum of Amounts from Table AField2: Count of Amounts from Table AField3: Sum of of Amounts from Table BField4: Count of Amounts from Table B......Field3: Sum of of Amounts from Table HField4: Count of Amounts from Table H......Things are a bit more complex but this is the gist.I am using SQL 2000.I know how to do this pretty easily using a stored procedure. But howcan I do it in a view? A SQL server won't meet my needs in thissituation.I tried OpenQuery ('myserver', 'exec myprocedure') but get the messagethat my server is not configured for data access. I tried the systemstored procedure to set data access to true but nothing seemed tohappen.I also tried Select * from (Select Statement1, select statement2)but got syntax error at the comma between statement1 and statement2.Trying to use select Statement1 as ABC to does not seem to work either.Is there a way to do what I want without making 15 views and then afinal view that shows them all together? I know I could probably dosomething by creating a ton of functions, but it really seems thisshould not be that hard...I am definitely open to any easy suggestions!Thanks,Ryan
View 3 Replies
View Related
Aug 30, 2007
Hi,
We have a client who runs SQL Server 2000 queries on one database server and performance is approx. 4 seconds. If the database is backed up, no tables in the query or indexes on these tables are modified (we may run a small script that affects stored procedures, views, etc.) the query can run virtually forever.
The customer is runing a cluster and we are running a stand-alone. Although, the two environments that they run in and have these wildly different results in are the same.
The queries are not worth listing (join a couple of tables and views, select a few columns, put on a few conditions--nothing crazy).
Is this normal behavior for MS SQL Server?
I've personally seen where a database is backed up and query plans and performance are different from one server to another, but we are looking at extreme cases here. In fact, on the second server, the majority of the queries are faster and only a couple run very slowly.
Also, the query optimizer seems to be making poor decisions at this custoemer. For example, two tables will be cross joined (forming over 200 million records) and then table scans ensue. The process in some cases will take a 4 second query to 45 minutes.
To me none of this makes any sense. I've been working with SQL Server since 1997 and have not experienced any type of performance problems or variances of this magnitude. Although this is a 6GB database, SQL Server 7 ran on a terabyte without even blinking, so I wouldn't understand why this would have anything to do with it.
Also hampering our efforts is that we do not have easy access to this SQL Server database to get our hands on it and debug these issues.
Does anyone know of a way to examine these issues in a "system wide" manner to determine what the problems could be since the problems are not specific to the database (i.e. .bak file) but seem to be specific to the server?
They have also had database corruption (an index that wouldn't update) and had to roll back the database. Would that indicate that the MDF/LDF's are unstable? Is there a way to figure out if there is some type of MDF/LDF file structure corruption?
Thanks,
Henry.
View 6 Replies
View Related
