GROUP BY On DECODE
May 14, 2007
Hi,I have two questions, firstly..
I am hoping there is a way to GROUP BY on a concatenated output like the following decode etc..
SELECT
DECODE(PRIM_REASN,'','Reasons not entered',PRIM_REASN)
|| ' '
||SECDY_REASN
|| ' '
|| TERT_REASN) Downtime_Reasons,
EVENT_DUR/60 Minutes
FROM
DOWNTIME_WEB
WHERE
DOWNTIME_WEB.TIMESTAMP>= (SELECT PRODN_SHIFT.START_DATIME
FROM PRODN_SHIFT
WHERE
PRODN_SHIFT.START_DATIME <=sysdate
AND PRODN_SHIFT.END_DATIME > sysdate)
AND
TRIM(DOWNTIME_WEB.STATUS_TEXT) = 'STOPPED'
AND
TRIM(DOWNTIME_WEB.WORK_CTR_NAME) = 'PACK2'
ORDER BY DOWNTIME_WEB.EVENT_DUR
I have tried to put the DECODE etc into the GROUP BY statement but I get an error - " not a valid GROUP BY clause"
Secondly, is there a way of modifying value in field A based on the value in field B
Erroneous example follows - in an effort to explain what I want to do (from above):
DECODE(PRIM_REASN,'',(DECODE(EVENT_DUR,<200,"Low",>200,"High"),PRIM_REASN)
ie:If PRIM_REASN is null I want EVENT_DUR to be tested to determine the new value for PRIM_REASN.
Except that tthe above won't work and I don't know what the right function/argument should be..
This is my first foray into SQL server, I am trying to migrate some access queries to work with web services and hence Xcelsius.
Cheers.
View 4 Replies
ADVERTISEMENT
Jan 12, 2004
In Oracle we have expression
SELECT
.....
DECODE (T.LANGUAGE, 'E', T.DESC_ENG, T.DESC)
FROM
TABLE T
if acts like that :
if T.LANGUAGE = 'E' it returns T.DESC_ENG. If it is different - T.DESC.
Is there some way to make the same in MS SQL Server ?
View 10 Replies
View Related
Jul 20, 2005
I have to run a query to give a column a value based on a time range. Can Iuse DECODE?select decode(trans_date, trans_date>='01-Jul-2002' andtrans_date<='30-Jun-2003','Fiscal2002', .....) as fiscal,from. . .where. . .
View 1 Replies
View Related
Aug 28, 2001
HI,
Is there any way to replicate the decode function in SQL Server 7.0? I have a problem where I have to write a query such that if the value in a column is '0' , I need to output it as 'No' and if it is '1', I need to output it as 'Yes'. An example of my problem is as follows:
select col_1+","+(value 'No' if col_2='0' and 'Yes' if col_2='1')+
","+col_3+...........
I appreciate your help in advance.
thanks,
Sravan.
View 3 Replies
View Related
Jun 4, 2004
Hi
I have oracle statement and I want to translate it in SQL Server:
select DECODE(count(bid_Vendor), 1, NULL, COUNT(BID_VENDOR))
from bid_total
I tried it this way:
Select case(count(bid_vendor) when 1 then null else count(bid_vendor)end) as cs from bid_total
It gave me error i.e.
Incorrect syntax near the keyword 'when'
View 1 Replies
View Related
Jun 8, 2008
Hello,
I need to do the following:
Lets say we have the following table.
A B C
1 XYZ XYZ
100
Now if the value of a is "100" then i need to do a join and get a value from another table, else I put the value of b a "test"
I am trying to do this using case
like this
select case
(when a = 100 then (select col from ....where newtable.id = a.id
else "test"
end) as xyz
from a,b,other tables.
But the problem is that the subquery returns more than one row..(it should return one row)
How do I do this?
View 1 Replies
View Related
Sep 30, 2006
hi i have a view in oracle. in that i am using decode function.
same query i want to write it in sqlserver. what it is the alternate
to decode.
this is a cross tab query
SELECT code, SUM(DECODE(field1, 4, Present_Value, 0)) AS c1, SUM(DECODE(field1, 5, Present_Value, 0)) AS c2,
SUM(DECODE(field1, 6, Present_Value, 0)) AS c3,SUM(DECODE(field1, 9, Present_Value, 0)) AS c4
FROM (SELECT field1,Code, Present_Value FROM table1) DERIVEDTBL GROUP BY code
thanks
suji
View 8 Replies
View Related
Jul 20, 2005
What is the function in SQL that works like DECODE in Oracle?"Thanks,N
View 10 Replies
View Related
Jan 23, 2006
Hi!
I have a question about the "Decode" funktion. is it only avaiable in Oracle? The reason for my question is that I need to SELECT a colum in a table based on a int value. So if the uservalue is lower then that take that colum, lower then that take colum...etc.
Can I use decode? or/and is there better funktion (I'm using MS SQL 2005)
Thanks in advance
Mark
View 6 Replies
View Related
Dec 11, 2007
how can I write decode function in sql
such that i have a feild in my table called "file type" based on its content i want to pass a value to sql datasource
such that if file type = doc i want to pass "~/Images/ico_filetype_10.gif" an so on
View 1 Replies
View Related
Mar 27, 2001
I need to know what is the DECODE statement called in SQLSERVER?
Can somebody provide me simple decode statement for sqlserver.
Thanks,
Ranjan
View 1 Replies
View Related
Jun 21, 2004
Hi, Here is my oracle statement. How can I change it to be compatible with SQL Server?
update propertytable set visible =decode(propertyid, 1,0, 2,0, 3,1, 5,1, 6,1, 7,0, 9,1, 10,1, 11,0, 14,1, 30,1, 38,1, 60,0, 232,0, 233,0, 415,1, 605,0) where parentid between 2000006001 and 2000006020
Thanks...
View 10 Replies
View Related
Mar 6, 2008
I am a novice to SQL Server, so this is probably a really easy problemto fix. I'm translating an Oracle query and need to change the'decode' to something compatible. Everything I've read points me tousing 'case' but no matter how I write it I can't get it to work andget a syntax error. Suggestions?select SYST CTR, isnull(substring(CD_A, 1, 3), ' ') RESCODE, DES DESCRIPTION, decode (substring(CD_A, 1, 3), CODE,PRICE,0) UNIT_PRICE, count (distinct OR_NO) QTYW
View 11 Replies
View Related
Jun 7, 2006
Hi,
I have a xml string which is consist of some images encoded in base64; I have to extract these images in a stored procedure and save them in some tables.
The problem is that I can't decode this base64s to images. Is there a way to do it? (I use SQL Server 2005 Enterprise)
Thanx
View 5 Replies
View Related
Aug 2, 2006
Hi,
How to translate oracle Decode without changed code I mean:
I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I
Wrote this function
select dbo.fnDecode( 1 , 2 , 3 )
first parameter always int, and the others parameters could be char, int or float.
I would like to test first value and return second or third value
-> sql_variant for all parameters, ok
but I can use + or -
I can't do this
select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 )
If I put cast ok, but I is there another way, overload this call?
With clr doesn't work, because Sql Server doesn't accept function overload calls from C#
Any ideia????????????????????
cheers,
View 4 Replies
View Related
Nov 20, 2014
how to execute this stored procedure to decode a varchar hexadecimal string? My SQL syntax stills have faded with the sands of time... I want to do a select * from the users table and decode the Password field (but not update) in the process.
CREATE PROCEDURE spPasswordDecode (@hex varchar (100), @passwordtext varchar (100) output)
AS
declare @n int,
@len int,
@str nvarchar(500),
@output varchar(100)
[code]....
View 4 Replies
View Related
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
Oct 19, 2004
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali
View 1 Replies
View Related
Nov 27, 2007
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
View 1 Replies
View Related
Nov 6, 2015
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.
View 4 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Sep 25, 2003
I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager
Now when I try to add Mikel, Im getting error 15401.
Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?
Can I go into the Master database and just change Mike login to Mikel ?
Thank you
View 3 Replies
View Related
Feb 28, 2014
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
View 0 Replies
View Related
May 1, 2014
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
View 9 Replies
View Related
Nov 21, 2007
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 Replies
View Related
Jan 5, 2008
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
View 1 Replies
View Related
Feb 6, 2008
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
View 1 Replies
View Related
Jan 29, 2007
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
View 9 Replies
View Related
May 28, 2007
Hi folks,
I have a Projects , each project have many tasks now i want to display tasks replated to each project:
for example:
Project1-------------------->task1
task2
task3
task4
Project2----------------------->task4
task5
task6
.............................................projectN.....................
how to write query for this
i have 2 tables:
Project .......>columns are projectid
Task------------->columns are projectid, taskid
|
View 4 Replies
View Related
Jun 6, 2007
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
Example:
Now:
Code Snippet
Column Column
Section1 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Section2 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Should be:
Code Snippet
Column Column
Section1 -
Section1 Mnemonic
Section1 Mnemonic
Section1 Mnemonic
Section2 -
Section2 Mnemonic
Section2 Mnemonic
Section2 Mnemonic
View 2 Replies
View Related
Feb 12, 2015
I have a code like this:
SELECT Node_ID,Day,Operation, AA,BB
FROM
(SELECT
CASE
WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA,
CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
View 1 Replies
View Related
Jul 23, 2005
Hi,I was hoping someone may be able to help me with a tricky T-SQLproblem.I need to come up with a SELECT statement that does basically thefollowing:Select RCRD_REFNO, MAX(MODIF_DTTM) as MODIF_DTTM from[StageDb].[dbo].tblPersonInfo group by RCRD_REFNOHowever, I need to select ONLY the TOP 1 of each group (i.e. only 1record for each unique RCRD_REFNO). The problem is of course that if Iadd 'top 1' after select, it only brings back 1 record full stop,rather than 1 for each group!Now, I have previously come up with a similar query that DOES do thissuccessfully, but it relies on a criteria (such as a unique identifier)-unfortunately, the nature of the table I'm using for this currentjob means that it actually doesn't have a primary key, as it'ssimply a staging area for raw data, and can even have completelyidentical records in it. I think the only way I'm going to be able todo it is to literally use the 'TOP' command somehow, but am notsure how to adapt the above to implement it...I'd be very gratefulfor any advice.Many thanks
View 6 Replies
View Related
Sep 6, 2007
Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000
For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000
2 a 100 2000 0
Team Total 100 0
Total 3400 3000
BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)
I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount
And the finial total it turns out 2400 (3400 - 1000)
I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0
2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)
this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ??
thank you
View 9 Replies
View Related