T-SQL (SS2K8) :: Aliases In OPENQUERY - Get Selected Columns By Their Ordering Number
Apr 9, 2014
I have the following query
SELECT [KPI].*
FROM
OPENQUERY(LINKED_OLAP,'SELECT
HEAD(TAIL(DESCENDANTS
(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,
([Game].[Game Code].&[1] ,
[Code] ...
The last three columns are dynamically generated because they change during time. Next month they will be different.
I like to introduce aliases for them and to have them in the select as 'TWO_MONTHS_AGO','ONE_MONTH_AGO', 'CURRENT_MONTH'
I wonder if exists something like [KPI].(0), [KPI].(1), and etc.. of the OPENQUERY to get the selected columns by their ordering number...
View 1 Replies
ADVERTISEMENT
Apr 28, 2015
I am getting error [[Msg 16924, Level 16, State 1, Line 13
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.]] when i execute below script.
Declare @mSql1 Nvarchar(MAX)
declare @dropuser int
declare @dbname Nvarchar(max)
declare @username Nvarchar(max)
DECLARE Dropuser_Cursor CURSOR FOR
[Code] ....
View 9 Replies
View Related
Sep 15, 2015
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cdpermit_nbrreference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1991100003
ABC1992100004
ABC1993100005
ABC2880100006
ABC2881100007
ABC2881100007
ABC2882100008
ABC2882100008
View 2 Replies
View Related
May 6, 2008
Hi,
is it possible to assign a numeric value as a column alias:
select ... as 1234 from ...
does not work.
Cheers
View 3 Replies
View Related
Oct 5, 2006
Formatting question. The query below is failing on the columns with spaces in the name. I've tried brackets and single quotes with no luck. How should this be formatted?
----------------------------
SELECT x.trkuniq, s.mstuniq, t.meetuniq,
c.coursec AS Course_Code,
c.descript AS Course_Name,
[q.cactus #] AS Cactus_#
s.sectionn AS Section,
RTRIM(f.lastname) + ', ' + RTRIM(f.firstname) AS Teacher, f.funiq,
t.termc AS Term_Code, zd.cycldayc AS Day,
zp.periodc AS Period, zp.periodn,
p.schoolc AS School
FROM mstmeet t INNER JOIN
mstsched s ON t.mstuniq = s.mstuniq INNER JOIN
trkcrs x ON s.trkcrsuniq = x.trkcrsuniq INNER JOIN
course c ON x.crsuniq = c.crsuniq INNER JOIN
track p ON p.trkuniq = x.trkuniq INNER JOIN
facdemo f ON s.funiq = f.funiq INNER JOIN
courses q ON c.coursec = [q.course number] INNER JOIN
trkper zp ON t.periodn = zp.periodn AND
x.trkuniq = zp.trkuniq INNER JOIN
trkcycle zd ON t.cycldayn = zd.cycldayn AND
x.trkuniq = zd.trkuniq
View 4 Replies
View Related
Jul 20, 2004
I have a query that pulls records from a table based on whether is matches one or more of several criteria. I'd like to be able to order the results of this query by the number of conditions a particular row met. So a row that met 3 conditions would rank about a row that only met 2. So far I haven't been able to dent this with any attempt so I'm open to any suggestions.
View 5 Replies
View Related
Jan 2, 2007
i add new column using alter command but i always found it in the end of table but
i want to add it in particular position between the columns...........
how to do so .............:)
View 6 Replies
View Related
Nov 26, 2001
Bottom line question:
Does it make a difference which table is referenced first following the ON keyword in a FROM clause when creating a join?
--Example 1:
SELECT * FROM TABLE01 t1, JOIN TABLE02 t2
ON t1.ID = t2.ID
--Example 2:
SELECT * FROM TABLE01 t1, JOIN TABLE02 t2
ON t2.ID = t1.ID
Does the sequencing of the tables after the ON keyword make a difference?
Thanks in advance for your help.
View 1 Replies
View Related
Aug 18, 2007
Hello,
I am trying to format a matrix report so that columns appear in a specified order.
An example of what I mean is, I have 3 columns; New, Additional and Old.
When these columns are dynamically generated by RS they are put in alphebitic order. I want them to appear in the order in which I have them above.
The dataset returns a sequence (int) for each of the columns, so 'New' = 1, 'Additional' = 2 and 'Old' = 3. I am ordering on that sequence, but still can't get it to work. These are actually column groupings.
What am I doing wrong? I don't want the column sorted (i.e. data sorted within a row), but the columns to appear in a specific order.
Many thanks
Shawn
View 2 Replies
View Related
Dec 1, 2014
I am emailing data using this:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT ITEM_ID AS 'td','',"td/@align" = 'right', replace(convert(varchar,convert(Money, AVAILABLE),1),'.00','') AS 'td','',
[Code] ....
The result displays:
ITEM_ID Available DESC EXPCT DELV
AC 16,015Apples 01/18/2015
ORG12 2,908 Oranges 12/30/2014
AHA 3,355Apricots 01/01/1753
Our systems stores 01/01/1753 when the date field is blank. In the above TSQL, how can I replace 01/01/1753 with null or blank in teh results?
View 5 Replies
View Related
Jan 6, 2007
Hello,
I am new to SSIS.
I am trying to write a simple package to export data from some SQL 2005 tables and into a flat file.
In my data flow, I am using the OLE-DB data source and then the flat file destination.
This all works fine except that I cant get the package to write the columns out in the order I want. Even when I drive the OLE-DB source by a query, they columns are getting written to the flat file in a different order than I want.
How is SSIS determining what order to write the columns in and, more importantly, how can I change it to do it in the order I want?
Please help if you can. As mentioned I am new to SSIS so please give clear+simple answers.
Thanks
Mgale1
View 5 Replies
View Related
May 27, 2005
This is my SQL :
Select p.patientid,p.patientname,p.patientIc,pvi.DateOfAdmission,pvi.visitid,pvi.ward,pvi.bedno,pf.status,pvi.SurgeonName,(f.Title + ' ( Ver ' + (CAST(f.Version as Char(10))) + ')') as Title FROM patient p, patientvarianceinfo pvi,patientForm pf,Form f where (p.PatientName LIKE '%" & Name & "%' or p.PatientIc LIKE '%" & ic & "%' or pvi.Ward LIKE '%" & ward & "%' or pvi.Bedno LIKE '%" & bed & "%') and (p.patientid = pvi.patientid) and (p.patientid = pf.patientid) and (pvi.patientid = pf.patientid) and (pf.FormID = f.FormID)and p.patientid in (select patientid from patientform pf)how do i get the number of rows?
View 1 Replies
View Related
Feb 20, 2013
Recently I had an application developer approach me and asked if I could provided him with a list of sprocs by returned row count. We had an issue where the application passed in a number of parameters which attempted to return 200k plus rows of data and the application was timing out. He changed the required parameters in the application and a reasonable number of rows were returned as expected. Short term solution to this one problem.
However there are always timeout issues with this particular application and we got to thinking that maybe other sprocs that were called using parameters would also fail at some point in time because too much data was being returned.
View 5 Replies
View Related
Jul 20, 2005
Help,I am running a pass through query to oracle from SQL server 2000 asfollows;select * from openquery(nbsp, 'select * from FND_FLEX_VALUES')I have run this query through both DTS and the query analyzer and getthe foloowing error;Server: Msg 7357, Level 16, State 2, Line 3Could not process object 'select * from FND_FLEX_VALUES'. The OLE DBprovider 'MSDASQL' indicates that the object has no columns.OLE DB error trace [Non-interface error: OLE DB provider unable toprocess object, since the object has no columnsProviderName='MSDASQL',Query=select * from FND_FLEX_VALUES'].The really strange thing is, I'll get this error the first time Iexecute the query but if I execute it immeadiatley after it will runfine.Any help would be most appreciated!Cheers
View 1 Replies
View Related
Jul 4, 2012
Code:
SELECT ((ACOS(SIN(34.37769 * PI() / 180) * SIN(latitude * PI() / 180)
+ COS(34.37769 * PI() / 180) * COS(latitude * PI() / 180) * COS((132.404738 - longitude)
* PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS ranges FROM completeyuubinwithlatlon having ranges<=3
First, I have that code whenever i disregard the "having ranges<=3" it selects record but when I include it it returns error. The problem is I want to just select records with ranges less than 3 miles..how do I do that.. and I want to count the number of selected rows and store it on a column on the same table.
View 3 Replies
View Related
Feb 13, 2007
Hi All
I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.
Many Thanks
Delli
View 4 Replies
View Related
Sep 22, 2005
Hi,
I have a table with 3 columns - userID, score, dateCompleted
From this table I wish to select the highest score for each userID and the date for that score. If there is more than one row with the same userID and score (i.e. the user got the same score twice), I want to select the earliest date.
To clarify, there are multiple rows which have the same userID, and may have the same score too, but I want to pull out just 1 score for each userID.
I could do this quite easily if the DISTINCT operator worked on a per column basis, but it works on the entire row instead. Is there a way to SELECT DISTINCT column, but still output other columns in those rows?
I hope this is clear, but please let me know if I've confused you.
View 3 Replies
View Related
Jul 31, 2007
Hi all,
i have a table which has date and count as column
Can you please...help me write a query for it.
date | type | count
10/07 | A | 70
11/07 | A | 50
12/07 | A | 50
11/07 | B | 60
12/07 | B | 50
13/07 | B | 40
11/07 | C | 50
12/07 | C | 60
13/07 | C |60
14/07 | A | 50
i need to write a sp such that..if user sends date and @ndays(no. of days) as parameter say 11/07 and 3
then
out put
type 11/07 12/07 13/07
A 50 50
B 60 50 40
C 50 60 60
Thanks..
Regards,
sradez
View 3 Replies
View Related
Nov 19, 2007
Hello,
I start with this table,
Tagname
Timestamp
Value
A
12/12/2007 12:00:01
1
A
12/12/2007 12:00:02
1,1
A
12/12/2007 12:00:03
30
B
12/12/2007 12:00:01
12
B
12/12/2007 12:00:02
12
B
12/12/2007 12:00:03
12
C
12/12/2007 12:00:01
13,4
C
12/12/2007 12:00:02
14
C
12/12/2007 12:00:03
14,5
I would like the table to be as following
Timestamp
A
B
C
12/12/2007 12:00:01
1
12
13,4
12/12/2007 12:00:02
1,1
12
14
12/12/2007 12:00:03
30
12
14,5
Does anyone knows how to do this within a query?
Thx,
Nic
View 9 Replies
View Related
Aug 9, 2012
I am trying to just get a count of the number of distinct rows are in a table --- not looking at the entire row of fields, but only selecting a few.
i don't want to see the distinct rows, i just want a count of how many are in the table.
View 4 Replies
View Related
Apr 10, 2008
Hello everyone,
I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?
SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex
FROM Person LEFT OUTER JOIN
Person2Role ON Person.personID = Person2Role.personID
WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))
AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))
AND (Person.sex = ISNULL(@sex, Person.sex))
This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters
I am working with SQL Server 2005 Express Version
Thank you,
Alex
View 10 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
Mar 18, 2015
I have created row level security on two views and adding these two views to particular role.Today I have got an requirement that, middle level managers shouldn't see the all the columns. So I have created another role for Middle level managers and assign securables as those two views with selected columns by grant, and map all the middle level managers to this role. I thought my job is done. But these managers uses this view on SSAS(tabular model) and Excel, In those applications they are not able to load the data.
Later I come to know we can't use -- select * from ViewA ( in viewA I have restristced few columns in the role level) Work around is creating another view and assigning to the role. But how can we achieve column level security to implement this in either SSAS/SSRS/EXCEL?
View 6 Replies
View Related
Sep 24, 2007
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
View 1 Replies
View Related
Aug 28, 2014
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies
View Related
Jun 11, 2015
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
[Code] ....
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
View 3 Replies
View Related
Jun 5, 2007
Hi,
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
Thanks in advance for any answer.
View 1 Replies
View Related
Apr 4, 2007
Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
View 4 Replies
View Related
Sep 10, 2007
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
View 1 Replies
View Related
May 6, 2015
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
View 3 Replies
View Related
Feb 21, 2014
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
View 0 Replies
View Related
Apr 24, 2008
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
--insurancetype is the new column.
View 9 Replies
View Related
Sep 12, 2015
@BoxesNeeded will change every time it runs. This method works.
declare @i as int,
@BoxesNeeded as int
select @i = 0,
@BoxesNeeded = 15
--drop table #temp
select @i as DDL
into #temp where 1=2
while @BoxesNeeded > @i begin
set @i = @i + 1
insert into #temp
select @i
end
select DDL
from #temp
View 3 Replies
View Related
