Search results matching tag 'query'

Display Query Result

Yoseph — Sat, 05 Dec 2009 05:00:00 GMT

Does anyone know of a way to display sql query result in a texbox? I do not want to use anything like Gridview or anything else from data toolbox.

This is the textbox which I will use:

<asp:TextBox ID="TextBox1" text= MyQueryResult runat="server"></asp:TextBox>

Oracle - alternating values

davidl — Fri, 04 Dec 2009 05:00:00 GMT

This is more of a query question... how to I alternate values in a SELECT query, for example:

select * from fruit f where f.isripe = 'Y' and f.type in ('APPLE','ORANGE');

I want the output to be like:

APPLE
ORANGE
APPLE
ORANGE
APPLE
ORANGE
APPLE
APPLE
APPLE

This seems to do the trick (though I am not sure why, explain plan shows a sort happening), but I want to avoid using UNION if at all possible?

select rownum, f.*from fruit fwhere f.isripe = 'Y' and f.type = 'APPLE'
union
select rownum, f.*from fruit fwhere f.isripe = 'Y' and f.type = 'ORANGE';

Possible without using UNION, and using SQL alone?

Cheers,
DGL

Random display query works as query but not on the page - ASP.Net - Visual Studio

teesel — Thu, 03 Dec 2009 05:00:00 GMT

Hi there I’m new to forum help so I hope can explain my problem to the best of my ability.

Basically I have a data list view which is connected to an object data source. The object data source is a table adapter which has a query init(buitl witht he query builder). The query is stated below and when run from the table adapter view(preview data part), it does what I want, that is, displays the result from the chosen category randomly by the product id (so with in a set category the product id are displayed randomly), but when I place it in the web page it all ways is the same, and upon refresh or revisiting the page it doesn’t change.

This is the query that is run

SELECT     TOP 3 Categories.CategoryId, Categories.CategoryName, Products.ProductId, Products.ProductName, Products.CategoryId AS Expr1, Products.Price,
                      Products.Description, Products.Make, Products.CC, Products.Stroke, Products.[Road Style], Products.Link, Products.Random
FROM         (Categories INNER JOIN
                      Products ON Categories.CategoryId = Products.CategoryId)
WHERE     (Products.CategoryId = ?)
ORDER BY Rnd(Products.ProductId)

Could some one please help me resolve this issue as its extremely annoying as I know the query works, but in the page it doesn’t.

I’m using ASP.NET and c# as the background programming language if this helps.

SQL query: Inserting multiple results into a new table

lorengphd1 — Fri, 13 Nov 2009 05:00:00 GMT

I have a MS ACCESS 2000 sql query that returns several results, sometimes up to 30 or so. I want to insert these results into a new table all at once, each with their own unique ID [once in the new table]. How is this accomplished?

Thanks!

Loren

Very slw query

atefyousif — Tue, 03 Nov 2009 05:00:00 GMT

These two SP are the same in all , the only difference is the underlined line, in the fisr case it works 20 times faster than when is used is nol null ??? I dun nu

ALTER Procedure [dbo].[GetNumberOfWOForMachine] ( @TopN INT=10 )

BEGIN

ALTER Procedure [dbo].[GetNumberOfWOForMachine] ( @TopN INT=10 )

BEGIN

WITH TemporaryTable( StartTime ,FinishTime ,WorkOrderCode ,MachineName) AS ( SELECT Min(StartTime) as StartTime ,Min(FinishTime) as FinishTime ,WorkOrderCode , MachineName FROM CP_WorkOrderStepsView GROUP BY WorkOrderCode, MachineName ), Temp2(NumberOFOrders,MachineName) AS( SELECT COUNT(WorkOrderCode) As NumberOFOrders,MachineName FROM TemporaryTable GROUP BY MachineName HAVING ISNULL(MachineName,' ')<> ' ' )

SELECT TOP (@TopN)* From Temp2 ORDER BY NumberOFOrders DESC

END

-------------------------------------------------------------- --------------------------------------------------------------

ALTER Procedure [dbo].[GetNumberOfWOForMachine] ( @TopN INT=10 )

BEGIN

SELECT TOP (@TopN)* From Temp2 ORDER BY NumberOFOrders DESC END Go

Question on automating a MySQL query LIMIT statement

Jay-B — Sat, 10 Oct 2009 04:00:00 GMT

I asked this somewhere else, but since I'm using ASP.net with MySQL, I'm going to ask this in here, too:

I have a large database that has multiple items associated with one group. What I was doing was searching the table for items with the group ID, and then doing a LIMIT X ASC statement manually for the number of items associated with that group. This returned only the latest data for this group. Example table might look like this:

Tempature of device 88 degrees 10/10/2009
Status of device running 10/10/2009

This device might have 6 things associated with it, but I only need to know the above 2. So I set each one with a boolean to display or not to display bit (0 or 1) and added the boolean to my query. (And doing this does work just the way I want).

Here is the problem: Using the above example, I can do a report and set a query LIMIT statement to LIMIT 2...but that involves me looking at the display bits and saying that group only needs to display two records and then adding LIMIT 2 ASC to the query.

I would like to automate this somehow, but I don't know how. I need a query to search a table for the group, see how many display bits are set for that group, and then set a LIMIT X ASC based on that number. Or maybe there is a much better way to accomplish this???

A point in the right direction to do this would be greatly appreciated.

Thanks,

Jay

MySQL Query with a variable on ASP.net

Jay-B — Wed, 07 Oct 2009 04:00:00 GMT

Using, VWD 2008 express, I've been working through a MS tutorial called "Creating Master/Detail Web Pages in Visual Studio." The tutorial is simple. A dropdown list, set to autopostback from a table, that when selected, pulls up the data based on the variable category id.

What I have works in mssql, but I have convereted the data to tables in MySQL and I need to do the exact same thing. I can't get it to work when I use a variable in MySQL. For what I'm working with, I HAVE to use MySQL.

Can someone please show me the syntax to use a variable in a MySQL query statement?

Here is a working mssql statement:

SelectCommand="SELECT [ProductID], [ProductName], [CategoryID] FROM [Products] WHERE ([CategoryID] = @CategoryID)">

I know I have to remove the brackets, but the variable format is what I really, really need to know. Whatever I have tried, hasn't worked at all.

Thanks,

Jay-b

Join on table with multiple instances on one side

Metalore — Fri, 25 Sep 2009 04:00:00 GMT

I have two tables I want to join on customer id

table a has one instance of a customer

table b has three instances of the same customer

When running the query, I am getting 3 results being returned which is not correct. I need just one to be returned. I have tried joining on another select, using distinct, max, top 1 etc etc but can't get the correct result. Can anyone give me an idea what route I should take to get around this issue?

Thanks.

What is the best way to make a PDF from SQL Query data?

KenNichols — Thu, 24 Sep 2009 04:00:00 GMT

I have a SQL Query that is 8 columns wide and well over 1000 rows long.

Right now I have it displayed on the page in a GridView with paging.

I need to send the data to a PDF, what is the best way to do this?

I don't need the exact GridView Data I will use a separate Query for the PDF.

Help with SQL query to count based on values TWO separate fields

EricCagle — Thu, 24 Sep 2009 04:00:00 GMT

I need to come up with an SQL query that counts the number of occurrances that a number in TWO fields occurs in a table. I have been fighting with it all day and have yet to come up with a working query. I am fairly certain that I need to use a subquery to achieve this, but cannot seem to get it to give the correct results.

Here is a rough sample of the data layout:

id	mydate		type	size1	size2
1	1/16/2009	E	15	
2	2/4/2009	E	15.5	15
3	2/18/2009	N	14	
4	3/6/2009	E	16.5	
5	4/19/2009	E	16	14.5
6	5/15/2009	N	14	
7	5/28/2009	E	15	15.5

What I need to do is count the number of occurrances in the table that:

Type = "E"
(size1 >= 15 AND < 16) OR (size2 >= 15 AND < 16)

In the sample data above, id 1 would count as 1, id 2 would need to count as 2, id 5 would count as 1, and id 7 would count as 2. Thus for this query, the resulting count would be 6. I have been able to get a query to get "close" but I believe the problem lies with the records that have two fields with a valid value only add one to the count.

Any help in the right direction would be greatly appreciated.