Changing The Query Based On Value

Jan 5, 2008

in the following procedure i want to pass in a value and as a result of the value I want to change the actual body of the query:

example, I pass in the value 'TS2' and I now want my query to see if TS2MIN < 70

i think I am doing it right, but it is throwing an error saying that "Conversion failed when converting the varchar value 'TS2MIN' to data type int."

any idea on what i am doing wrong?

ALTER proc SPU_YearlyTemp 905,'TS2',70,254,2007
@SITEID INT,
@SENSOR VARCHAR(10),
@MIN INT,
@MAX INT,
@YEAR INT


AS
DECLARE @S AS VARCHAR(12)
SET @S=(
SELECT
CASE @SENSOR
WHEN 'TS1' THEN 'TS1MIN'
WHEN 'TS2' THEN 'TS2MIN'
WHEN 'TS3' THEN 'TS3MIN'
WHEN 'TS4' THEN 'TS4MIN'
WHEN 'TS5' THEN 'TS5MIN'
WHEN 'TS6' THEN 'TS6MIN'
WHEN 'TS7' THEN 'TS7MIN'
WHEN 'TS8' THEN 'TS8MIN'
END AS SENSORVALUE)




select top(10)*
into #tempMin
from sitecontroldataarchive
where siteid = @SITEID
and @S < @MIN --this is where the error occurs,
SELECT * FROM #tempMin

View 11 Replies


ADVERTISEMENT

Changing The MDX Query Based On A I/P Parameter. Is This Possible?

Jul 8, 2006

Hi



I am generating report with my datasource to an OLAP Cube. I have scenario that there are 2 dimension tables pointing to a single fact table. According to a user input, i have to use one dimension table and not the other and vice versa. I tried using IIF statement in the MDX query designer., but was facing errors.

First of all i want to know if this is possible and if yes, how?

Also , Is it possible to open a window form on clicking any report data

(Similar to assigning hyperlink, but i want to open a window form instead!!!)





regard



Sai

View 2 Replies View Related

Where Clause Changing Based On NULL Column(s)

Dec 30, 2003

Hello DBAs:

I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation.

1. Col2,3,4 are NULL
WHERE Col1 = condition

2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL

where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition.

How do I construct this logic. Please help

View 6 Replies View Related

Changing Global Variable Based On Day Of The Week

May 11, 2007

I'm new to SSIS so please be gentle...



I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?



Thanks in advance!

View 6 Replies View Related

Changing SQL Server Connection String Based On User.

Mar 1, 2007

I’m working with a team of programmers who are in the process of upgrading an older ASP application to ASP.NET 2.0 (VB).  The existing application connects to the SQL Server Database using different Logins, depending on the User logged into the ASP Application.
 
We are hoping to do the same using ASP.NET 2.0, although we haven’t been able to find a simple solution.  The most feasible solution we have found includes programmatically trapping all the Selecting etc events and changing the SqlConnection at run time.  This allow for ease during Designing of the Web Pages, but implementation requires adding code to each web page (>100 pages).
 
We are hoping there may be a simple solution which can be implemented once for the application where the event can be trapped at the application level, and the SqlConnection set at this point.  Or possibly another type of solution such as creating a new class etc that can be used?
 
Does anyone have a simple solution where the username / password for the Database Connection can be set at run time?

View 2 Replies View Related

Changing Text Color Based On Toggle Item

Jan 8, 2008

Hi All,

I have a report that has a toggle item that toggles between showing and hiding report items specifically the detail rows, I am wondering can I also used this toggle item to change the font color of other report items. Exampl is I have a toggle item, EmployeeName that toggles the detail rows of the employee from visible to hidden. Can I also use this toggle to change the text color of other report items or highlighting different items.

Thanks & Regards
Fadzli

View 6 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

How To Use Value Calcuated In Query In Subsequent Query, All Based On Value In Querystring?

Jun 5, 2008

I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string.  But, I don't know how to get a value returned from one sql statement into a second.  Here's what I'm trying to do...
From the QueryString, we know that the MatterID = 4  ( xxx.aspx?MatterID=4)
Knowing that the Matterid=4, I query the database to get the OfficeId for that MID  (Select OfficeID from tMatter where Mid=4)   ~This returns an OfficeID of 6
So, then I need to do another query to get the employees where OfficeID = 6   (Select EmployeeID from tEmployees where OfficeID = 6)
How do I do these in one query, or how do I use the Calculated Value for the OfficeID in the 2nd statement? 
 

View 3 Replies View Related

Returning Results Based On One Query Into Existing Query

Aug 2, 2012

linking two tables together to get an end result

find below the code i have used

The first part of the query provides me with the info i need

SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp

[Code].....

View 1 Replies View Related

QUERY : Changing Row To Column

Apr 21, 2008

Hi all,

I have a problem writing a query to change row to column with some conditions and computations. It's appreciated if you guys can give some ideas.

Below is the sample data:
CREATE TABLE Item (Line int, Code nvarchar(8),Price decimal, Check nvarchar(1))
INSERT INTO Item VALUES (1,'001',200 ,'Y')
INSERT INTO Item VALUES (2,'002',300 ,'Y')
INSERT INTO Item VALUES (3,'003',500 ,'Y')
INSERT INTO Item VALUES (4,'004',1000,'N')
INSERT INTO Item VALUES (5,'005',2000,'N')

The expected result :
Line with check = 'N' must be converted to column(s).

Line Code Price Item004 Item005
1 001 200 1000 * (200/200+300+500) 2000 * (200/200+300+500)
2 002 300 1000 * (300/200+300+500) 2000 * (300/200+300+500)
3 003 500 1000 * (500/200+300+500) 2000 * (500/200+300+500)

Note : I would like to avoid using any cursors if possible.

Thanks in advance.

cheers,
erwine

... sql is fun...

View 3 Replies View Related

Changing Database Has Slowed Down Query?

Jul 15, 2015

I had a query that executed in about 20 seconds pulling from database 1. The only changes I made were to make it pull from database 2. It is currently running and has been for over an hour and a half.

View 2 Replies View Related

Changing The Format For A SQL Server Query

Apr 3, 2006

Hi,

I have to generate a daily report of survey answers by users? My question is there a way to reformat the query so it generates a table or report with it showing the rows as columns instead.

Here is my initial query.

SELECT

dbo.Reporting_SurveyAnswers.DateCreated AS DateCreated

,dbo.Reporting_SurveyAnswers.questionid AS QuestionID

,dbo.Reporting_SurveyAnswers.surveyid AS SurveyID

,dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber

,dbo.Reporting_SurveyAnswers.userid AS UserID

,dbo.Reporting_User.LastName1 AS LastName

,dbo.Reporting_User.FirstName AS FirstName

,dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText

,dbo.Reporting_SurveyAnswers.QuestionAnswer AS QuestionAnswer


FROM

dbo.Reporting_Surveys

INNER JOIN dbo.Reporting_SurveyQuestions

ON dbo.Reporting_Surveys.surveyid = dbo.Reporting_SurveyQuestions.surveyid

INNER JOIN dbo.Reporting_SurveyAnswers

ON dbo.Reporting_SurveyQuestions.QuestionID = dbo.Reporting_SurveyAnswers.QuestionID

INNER JOIN dbo.uvwReporting_User

ON dbo.Reporting_SurveyAnswers.userid = dbo.uvReporting_User.userid

WHERE

dbo.uvReporting_SurveyAnswers.surveyid = 1125

Order by dbo.Reporting_SurveyAnswers.DateCreated

,dbo.Reporting_SurveyQuestions.ordernumber

Select

dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber

, dbo dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText

To complicate matters, some of the users did not answer some of the questions and some of the questions are duplicated in the rows because the database assigned them one answer each.

Example. Question 18 says "Name all the industries you have worked in. Check all that apply.

What happens is lets say the user checks 4 different boxes. In the query results, it will show 4 rows with question 18 with each answer they checked off.

Any help would be appreciated.

Thanks

The Accidental Tourist

View 3 Replies View Related

Changing The Number Format In SQL Query

Jan 30, 2008

Hi


I have query

SELECT col1, a.Inv_Amount as Amount
FROM SPS_Oustandings a

I can get the result values for example as 12345.67 as result, but i need it in german format with , used as decimal point and . as thousand seperator

i need output as 12.345,67
My DataGrid (ASP.NET) is directly bound to SP which have this query, i want to show amount in above format in grid.

Please Help,
Thanks

View 6 Replies View Related

Changing Field Lenght Via Query Analyzer

Jul 17, 2004

Hi
My DB is on the web so but I want change Field Lenght of one of my Fileds on my DB

its 70 rights now I want change it to 120 its a nvarchar


how can I do it , I am using a ASP page


Thanks

View 1 Replies View Related

Changing Query To Point To DB Table But Has Errors

Aug 5, 2014

CREATE TABLE SampleData (
CoID nvarchar(255),
Company varchar(255),
FirstName varchar(255),
LastName varchar(255),
JobLevel varchar(255))

[Code] ....

I am trying to make changes to the above query. This query will select 2 records per Company with top down priority on JobLevel. For example: This query will select 2 records from "123 Inc", where they have 2 Managers and 3 Staff contacts. The return results selects (2) managers and no staff records and FirstName and LastName are not duplicate. Then for "A Small Co.", they have 1 VP, 1 Director, 1 Manager and 3 Staff contacts. the return results chooses 1 VP and 1 Director and no managers or staff level people. And so on and so forth for the remainder of the companies.

What I was trying to accomplish is having this same query point to the database tables instead creating a table each and every time. I have this data residing in our database. I tried to make the change so it would point to the database tables but it kept giving me errors. This is way more complex than I thought.

My failed attempt:

Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck
WITH Priorities(Priority, JobLevel) AS(
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL

[Code] ....

View 2 Replies View Related

Best Way For A Set-based Query

Jul 23, 2005

I have a database where each row has a set of keywords separated bycommas eg .ROW 1 ID,<other columns>,"green,hat,man,boat"ROW 2 ID,<other columns>,"green,boat,seaside"ROW 3 ID,<other columns>,"red,hat"I want users to be able to find similar records to the one they arelooking it (this is art BTW). ie if they are looking at row 1 andclick "Find Similar Works of Art" I want to do a query where I findrecords that have a commonality of keywords and obviously the onesthat match the most keywords should be shown first. The keywords arecurrently just stored comma-separated in a single column but I am notattached to this. Nor are they guaranteed to come in any order (iethey are not alpha sorted) but they could be.Number of records is around 15000 on a hosted server.Any tips for best query/algorithm/data storage to achieve this? orreferences to useful sources?Thanks,RB

View 3 Replies View Related

Insert Based On Query

Nov 2, 2004

Hi everyone -

I have a simple query that will return a result set that
has a unique value in it (primary key) -

I would like to cycle through each of these values
returned from the query, and create a new row
in another table.

I do not want to use a cursor -


The first query is pretty simple....

select p_id from table1 where active=0

the query i need to execute for each of the p_id that
is returned in the first example is....

insert into table2 (p_id_field, text_field) values (@p_id, 'text')

the @p_id is filled in from the primary query...

please advise


thanks
tony

View 1 Replies View Related

Query Based On The Top MTD Sales

May 29, 2006

hello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group

Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales

Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...

Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...

Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: 1,2,3,4,5,6,7,8,...

Thank you

View 10 Replies View Related

Max Query( Based On Two Table)

Mar 19, 2007

i've two table Rate and Topic

Sample Table

Table name==>Topic
---------------------
Topic|TID|
-----|---|
hddgg|1
trgsd|2
vnvnv|3
sdfsd|4
dfsdf|5
dsdff|6
........
........
-----------------
above table TID is primary key

next table

Table Name==>Rate
-------------------
RID|TID|Rate
---|---|--------
1--|2--|4
2--|5--|3
3--|2--|3
4--|2--|2
5--|1--|1
6--|2--|1
7--|2--|2
8--|5--|3
9--|1--|4
.........
........

above table RID is primary key and TID is foreign key..

i need Top 3 Topic but based on Maximum Rate of value

any query.........

View 2 Replies View Related

How Do I Set A Variable Value Based On A SQL Query

Apr 25, 2007

I have a SQL query that finds a value which I need to pass into an SSIS variable so I can use it later to set the filename for my data flow task. So far, I have been unsuccessful in determining how this is done.

google searches, etc seem to be fruitless for me, there are so many hits but none really seem to address what I want to do because there are so many things you can do with variables. Likewise the books online and the boks I have on SSIS also only give a very high level of info. I need some examples, if anyone could help I would appreciate it.

Here is the basic gist of my query:

exec SP_GET_FILE_NAME @file_name OUTPUT

I need to take the resulting @file_name and store it in an ssis variable. How?

View 1 Replies View Related

Query Based Textbox

Jun 4, 2007



Hello Everyone,



How do you display a dataset query result in a textbox?



Thanks for the easy answer...

View 10 Replies View Related

How Do U Create A Table Based On A Query

Jan 23, 2002

Hi guys,
Can anyone please tell me how to create a table based on a query.
Thanks,
Joe

View 2 Replies View Related

Update Query Based On Another Dbase

Jun 5, 2000

Can i update a field in a table in 1 dbase based upon data in another table in another dbase.
could somebody let me know if this is possible.
Thanks

View 1 Replies View Related

Insert Based On Query Values

Sep 20, 2002

Using SQL Server 7 w/SP4.

I need to insert into another table the results of a query. Thought about using a temp table for the first query results, then querying those for the insert, but can't figure out how to pass the search results as a value.

Example:

SELECT ID, NAME FROM tbl2 WHERE ID BETWEEN 1 AND 50

IF @@ROWCOUNT > 0 BEGIN
INSERT INTO tblAudit
(ID, NAME)
VALUES
(@ID, @NAME)
END

Took a look at using SELECT INTO and INSERT INTO, but counldn't get those to work, either.

Thanks for any help.

View 3 Replies View Related

Select Query Based On Datetime

Feb 12, 2002

I need to select certain rows based on a "datetime" column. I need to select rows from 8am yesterday until 8am today.
In Oracle I would use:
select * from foo where TIMESTAMP >= trunc(sysdate - 1) + 8/24 AND TIMESTAMP < trunc(sysdate) + 8/24.
This would start at 8am yesterday and end at 7:59am today.

How would I do this with T-SQL?

thank you,

Mark
Fiesta_donald@email.com

View 1 Replies View Related

Query Based (partly) On CSV Column

Feb 5, 2007

I'll try to simplify the problem as much as I can.

There's survey. It has different types of question answer pairs, but the problem is in Multiple Choices, Multiple Answers questions, where user can choose, for example 3 checkboxes from 5 at the web form, for the question.

I am emphasizing three tables:

tbl_questions
IDquestion (primary)
question (textual question)

tbl_answers
IDanswer (primary)
IDquestion (foreign,to table tbl_questions)
answer (textual answer)

tbl_results
IDresult (primary)
IDquestion (foreign, to tbl_question)
result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7")

Inside those tables are data (I'll put here just two questions):

Example:
tbl_questions
IDquestion_question
1_________Which OS do you use?
2_________Which databases do you use?
tbl_answers
IDanswer_IDquestion_answer
1________1_________Windows
2________1_________Linux
3________1_________Mac OS
4________2_________SQL Server
5________2_________MS Access
6________2_________MySQL
7________2_________Oracle
8________2_________Other
9________2_________No, I do not use databases
tbl_results
IDresult_IDquestion_results
........
23_______1________1,2
24_______2________4,6,7
........
Is there a possibility to make query, using columns, but also the data inside the CSV format in results (varchar or char) column?

Result of the query would be:
IDquestion_IDanswer_answer
1_________1________Windows
1_________1________Linux
2_________4________SQL Server
2_________6________MySQL
2_________7________Oracle

Further queries based on this query should be able to make statistics (GROUP BY).

View 4 Replies View Related

Looking For Browser-based Query Tool

Feb 2, 2005

Does anyone know of a vendor who provides a packaged application that allows a user to pass T-SQL commands from a browser, to our IIS web server, to our SQL Server database?

Thanks!!!

View 1 Replies View Related

Display Results Based On Query

Mar 3, 2005

Hi,

I don't know if anyone has encountered this before but here goes:

I've a select clause below:
result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"

queryLocalName is an input field that allows the user to search for non-English characters in the database.

What I'm wondering is what kind of effect is the N in the where clause is having?

I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.

View 2 Replies View Related

Query To Parse One Column Based On Another

Nov 24, 2014

I have a table that is something like this:

Animal | Count
Dog |10
Dog |5
Dog |16
Dog |2
Cat |1
Cat |12
Cat |14
Cat |7

I need to write a query that will return something like this:

Dog_Count| Cat_ Count
10 |10
5 |5
16 |16
2 |2

View 4 Replies View Related

Query Based On Group By Clause

Jun 21, 2008

Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**

if no employee is absent in the department it has to display Zero

View 2 Replies View Related

Query Parameter Based On Columnna,e...

Jun 24, 2008

good day dudes! here's my 2nd questione for ye'all...

my databases:

workshift
- shiftid
- shiftname
- timestart
- timeend
- flexwindow
- status

employeeplottedsched
- employeeid
- month
- year
- day1shiftid
- day2shiftid
- day3shiftid
...
...
...
- day29shiftid
- day30shiftid
- day31shiftid

how can I query the plotted shift sched of employees for a particular day? like I would like to know what shift the employees were scheduled
from day1 to 15 of May 2008? the days that I would like to query would be dynamic so it can be day1 to 15 or day3 to day5 or just day20...

any of you guys nice enough to enlighten me?


U + U + D + D + L + R + L + R + Sel + Start...

View 9 Replies View Related

Adding A Column Based On A Query

Mar 27, 2015

I have 2 independent queries which run just fine, the 2nd one returns a dollar value. I need this dollar value as a column in my first query so that I can return it in a reporting program. The 2 queries are pasted below.

SELECT dbo.vw_tphillip_Customer_Inv.InvoiceNumber AS "InvoiceNumber",
dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType",
dbo.vw_tphillip_Customer_Inv.Date AS "Date",
dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate",
dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",

[Code] ....

View 6 Replies View Related

Add A Column Based On A Select Query

Mar 5, 2006

Supose I have the following select:

Select Name, Age, (select TelNums from Telephone)
From Person

The problem is that (select TelNums from Telephone) can return more than 1 record:

tel1
tel2
.
.
.

I was wondering how I can make a select to return the tel numbers like: 'tel1,tel2,tel2'

»»» Ken.A

View 3 Replies View Related







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