I have a stored proc. in that proc i need to select a value based on which one is greater.
Here is a non working example
select
name,
if (truck1.age > truck2.age, truck1.age, truck2.age)
from person
left join truck truck1
on truck1.make = person.make
left join truck truck2
on truck2.make = person.make
Thanks in advance for any help with this
Here is the first part of a query for MySQL that I am trying to get working on MSSQL:
Code:
SELECT n.*,
round((n.rgt-n.lft-1)/2,0) AS childs,
count(*)+(n.lft>1) AS level,
((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower,
(( (n.lft-max(p.lft)>1) )) AS upper
FROM table n
...
But, I get this error message:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
Is there a way to convert this? Thanks
I'm trying to sniff or view a fairly large query that get's passed to my sql server.
The problem I'm having is that the dbcc inputbuffer only shows the first 255 chars of query and I need to see the whole thing.
Is there another method or a way to increase the size of this so I can see the whole query?
Thanks
- jeff
Hi Guys
I have a table with id, date column in it. How do i list all records which has date greater than 30 day?
Thanks
Is this Correct?
SELECT * FROM TABLE WHERE DATEDIFF(DAY, DATE, GETDATE()) > 30
I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.
How do I query just the changes?
Example dataset:
idrating
15
25
35
45
56
66
[code]....
There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:
idrating
45
56
97
118
189
I use Microsoft SQL 2008
I was comparing diffrent columns and within those columns there are specific values. I want to get the greter values using SQL 2000
i want something like these but i think there was an error in scripting
CASE
WHEN a > b,c,d,e THEN a
WHEN b > a,c,d,e THEN b
WHEN c > a,b,d,e THEN c
WHEN d > a,b,c,e THEN d
WHEN e > a,b,c,d THEN e
END
Please help. Is there any possible way to implement this? Thank you.
Hello,
I would like to create a job that runs ones a day to delete records in a table that are older than one day. Any ideas?
Thanks,
Nina Melhem
The length of my query string is greater than 255 characters. SQL Server is truncating the query string when I assign it to a Local variable.
What should i do....???
I created this unique codes and I need all [FRMDAT] field set to "12/31/2014" in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > "12/31/2014"
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
im supposed to output the companies that have commission rates highter than company "Industrial Appparatus".
is there some whay to modify this code so that it will work?
commissionrate > ALL(Select commissionRate From salescompanydomestic Where companyName = 'Industrial Appparatus')
I have a query pulling all records with a disconnect date and a transaction date. However, I would like to retrieve any records that have a transaction date greater than 30 days from the disconnect date. I have been unable to figure out the correct formula to use. I think I need to use the datediff function in SQL, but I've never really used this function before.
View 2 Replies View RelatedProblem: I want to set compatibility_level only when it is greater than 110.
Solution: Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110
ISSUE Irrespective of IF Exist statement the alter database statement is executed all the time.
Here is the sql statement
IF EXISTS (
SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'
)
BEGIN
ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110
END
What is that I am missing here ?
Hello All,
Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.
The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...
Code Snippet
=Count(Fields!ResponseTime.Value > "500")
However, this appears to just return the count of all rows and ignores the "500" part.
Am I missing something? If someone could post a alternate code snippet, that would be great.
Thanks in advance,
Clint
I'm using and Execute Sql Task to get a count of the record in the table: How can I make the workflow to stop if it doesn;t meet the count requirement and continue if it does to the next flow. I'm looking at expression...but a bit comfused about using it.
thanks
I have a column of varchar(2000) but when I use it in a select statement I only get the first 255 characters displayed. (all the data is there as I can see different parts using substring) How do I get the complete column to display?
View 2 Replies View RelatedI think I'm trying to do a simple query on maximum date.
I've got 100 tools that have been used over the past three years.
Some of the tools are used almost every day. Other tools haven't been used for a month, while other tools haven't been used for a year or more.
Ultimately I'm trying to just find the list of tools whose latest date of use was a year ago.
I have a list of tools and a list of times each tool was used.
I think I'm going to have to do a search that for each tool what was the times it was used. That I can do.
What I'm not sure of is how to then pull only the latest date for each tool.
Once I get that I can then do a query off that result to pull the "oldest latest" date of use.
finding the first value to cross a specific threshold in SQL Server
LoadTemp1 LoadTemp2 LoadTemp3 TimeStamp
300 350 200 12-10-2013 13:30
177 238 333 12-10-2013 13:05
240 190 170 12-10-2013 12:45
How do I design query to find the first LoadTemp* that cross a threshold of 340 degrees per TimeStamp.
When I use the following SQL with the variable @CutOFFTID the second select statement query takes several minutes. If i run it replacing the value in the where clause with the acutal value of the variable it runs instantly.
DECLARE @CutOffTid int
SELECT @CutOffTid = isnull(max(ccas_id),0)
FROMdbo.fbs_trans (NOLOCK)
print 'Cut Off ID is ' + cast(@CutOffTid as char)
DECLARE @MinPeriod int
SELECT @MinPeriod = Min(period)
FROM agr.dbo.atrans
WHERE agrtid >@CutOffTid
Greetings,
Is there anything new on returning max of two values in SQL 05? There seems to be nothing I have searched everywhere. Only option might be to create my own UDF or do a CASE statement, but I could not believe my eyes there is no such thing?
Thanks
I want SQL to look at a date field and sort the data by todays date and greater. Even though there may be some data older than today. I've tried something like this but not working
order by SSD_SED >= GETDATE()
Where the date field is SSD_SED.
I am trying to select all the data which are not associated with values older than a specific value.. For example I have:
paola -- 2
paola -- 3
paola -- 10
jessica -- 3
jessica -- 12
james -- 3
james -- 2
james -- 5
I want to query only those names, that are associated with numbers greater than 3. So, for this example, the result would be only Jessica. the others should be discarded.
I have a 300MG DB and Query Analyzer gives me the "... DB larger than configured..." error when I try to connect to it...
What is the work around?
Thanks in advance
JEK
I created this unique codes and
I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > ‘12/31/2014’
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
I have a created a table and entered data into the table as follows:
CREATE TABLE t ( id INT , txtcol varchar(1000) )
INSERT INTO t ( id , txtcol ) VALUES ( 1 , 'ATXR_SOURCE_ID,CDDL_AG_PRICE,CDDL_ALLOW,CDDL_ALTDP_EXCD_ID,CDDL_CAP_IND,CDDL_CHG_AMT,CDDL_COINS_AMT,CDDL_CONSIDER_CHG,CDDL_COPAY_AMT,CDDL_DED_AC_NO,CDDL_DED_AMT,CDDL_DIS_PA_LIAB,CDDL_DISALL_AMT,CDDL_DISALL_EXCD,CDDL_DISC_AMT,CDDL_DP_PRICE,CDDL_FROM_DT,CDDL_PAID_AMT,CDDL_PF_PRICE,CDDL_PR_PYMT_AMT,CDDL_PRICE_IND,CDDL_REF_IND,CDDL_RISK_WH_AMT,CDDL_SB_PYMT_AMT,CDDL_SURF,CDDL_TOOTH_BEG,CDDL_TOOTH_END,CDDL_TOOTH_NO,CDDL_TOT_PA_LIAB,CDDL_UNITS,CDDL_UNITS_ALLOW,CGCG_ID,CGCG_RULE,DPCG_DP_ID_ALT,DPDP_ID,DPTC_CD,PDVC_LOBD_PTR,PSDC_ID,UTUT_CD' )
Now if i select data using the query below the txtcol field displays only 255 characters :
SELECT * FROM t
Y is this happening?
hi all, I'm after a way to produce a single-lined output of a char/varchar string replicated over 8000 times.
basically I've been given a task to create a stored procedure which can accept any integer to replicate a string X times.
From what I've read the replicate() fn will covert to varchar of max 8000 bytes.
I've thought about creating a UDF to accept the varchar and int and run a loop to keep outputting but 'print' will pass an endline to the buffer which is no good for my loop.
Any help would be great on this. Thanks.
Hello,
I am running a stored procedure thru' isql.
This returns only one field.
The sp is executed fine but if the data has length greater than 255 the data is truncated.
How do u capture the full output ?
Thanks In Advance
Ashutosh
:rolleyes:
I need a query to get the first value greater than a specific threshold value
LoadTemp1 LoadTemp2 LoadTemp3 LoadTemp4 TimeStamp
300 200 100 320 12-10-2013 13:30:29
100 250 113 340 12-10-2013 14:20:12
114 339 209 345 12-10-2013 14:45:01
I need to get the first value >= 340 threshold
I need to make a Benchmark on when the first LoadTemp crosses Threshold of 340 and capture the time.
I want to know how to make StartDate and EndDate should be greater than current date....
View 4 Replies View RelatedWe have Windows Server 2008R2 installed on VM Server.On that we have three SQL instances running. From few past months we are observing physical memory is going high. Earlier we observed it was at 86-88%, now it is 96-97%.We have 16RAM & 8 CPU cores on VM. what is the best and ideal configuration so that we can rectify the high physical memory issue.
View 13 Replies View RelatedWe have a requirement to call a Web Service method from SSIS where the method accepts a single parameter that is derived from a database recordset and can exceed 4k characters. From the little we know about the Web Service task it is limited to only accepting direct input and variables. Does anyone have any suggestions to overcoming this issue? Any detailed information in possibly calling the Web Service from script or using an object variable would be extremely appreciated.
Thank you in advance for your help.
What I want to see is how to show PO's who's due dates are > three
Select * from mytable
where myorderstatus = 'onorder'
This is my duedate format and what I want is any that past that date over 3 days
2014-08-11 00:00:00.000
Hi, all experts here,
Thank you very much for your kind attention.
I am having a question about the node_distribution.PRABABILITY. Some of the attribute values though have a small number of support for the specific node, but why it has a big node_distribution.probability even greater than 1? How can the node_distribution.PROBABILITY be greater than 1? How dose SQL Server 2005 data mining engine calculate the node_distribution.PRPBABILITY for its Clustering algorithm? Really confused and need guidance for that.
Thank you very much for your help.
With best regards,
Yours sincerely,
Hi
I need to pass the where clause of my statement as an input parameter for a stored procedure. As this is built dynamically within the program (Borland C++), it could sometimes exceed the maximum 8000 bytes of a varchar. I thought of sending in two input paramenters and concatenating them into a memo in the sql, but cannot seem to be able to execute the memo variable (i.e. exec(memo) doesn't work). Is there any other options for passing in such a huge parameter.
Also, I do not think there is any other option than passing in the where clause: passing in seperate parameters and building the clause in sql would require over 300 parameters to be passed, and as the possible number of combination is indefinate, I cannot have a different Stored Procedure for each potential case.
Thank you