Refering Aliases In The Same View/ Query

Dec 6, 2007

HI,
I have a view where I want to add Rundate and Prev Month Rundate as computed columns to simplify my joins and calculations.
Rundate will be a select from another table that has a list of rundates for each month like this.
SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend <= getdate()) AS smalldatetime) AS CurrRunDate
Now, I want to add Prev Month Rundate on the basis of CurrRunDate but it does not accept CurrRunDate. The query is like this

SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend < CurrRunDate) AS smalldatetime) AS PrevMonthRunDate

Can anyone help me to work around with the alias as I dont like to put a whole bunch of code inplace of CurrRunDate(Alias).

Thanks,
Shariq

View 1 Replies


ADVERTISEMENT

While Loading Refering Other Table Values.

Apr 17, 2007

1, abcd, qrs, 1000
2, efgh, tuv, 1000
3, ijkl, wxy, 1000
4, mnop, mno, 1000



lets assume the excel data seems to be above one.


Iam populating to the T1 table (below one) from the Excel source (above data)

Create Table T1(
ID int,
BaID int, -- Foreign Key, BA(ID)
BaBrID int, -- Foreign Key BABR(ID)
Amt int)



When iam populating values for BaID and BaBrid have to refer the respective tables BA(ID) and BaBR(ID) see the below tables.

Create Table BA(
ID int,
BAName varchar(25))



Create Table BABR(
ID int,
BABRName varchar(25))


Could anyonly please help me like which transformation have to use for this and how to use, just give me some tips.


Regards,
Newly to SSIS

View 12 Replies View Related

Adding New Content To A Table By Refering Other Tables Using SQL

Jan 5, 2007

I keep product name, id in Table1.
I keep Category name, id in Table2.
I keep relation between product and category (product_id, category_id) in Table3.
I have added some products to the table with proper category.
Work fine
But for some products I did not specified any category
(ie their id  is not present  in Table3)
But now I want all such products
(ie all products whose category is nothing)
 To be associated with category_id 10
 
Can I do this  simply with SQL queries?
 
Hope u can help me
 
sujith

View 4 Replies View Related

Refering To An Item In Main Report From Subreport

Apr 1, 2008

Hi All,

I have 2 reports where 1 is a subreport and the other one is a main report.
Can i change or refer to a control(item) in the main report depending on the value of an item in the subreport, when the subreport is being run?

Any comments are appreciated.


Thanks in advance.
payal

View 2 Replies View Related

Different Query Plans For View And View Definition Statement

Mar 9, 2006

I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?

View 10 Replies View Related

Aliases

May 7, 2008

Please help...

I need help analyzing this query. I am not sure why the person that wrote this query used the same table for 3 different aliases.

FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role

I tried to get rid of :

code_table country,
code_table prop_role

and put "state" in any line that referenced:

code_table country,
code_table prop_role

to see if the query worked the same, but I different results. Why?

Please help...




--7 seconds to omplete.
--set explain on;
--UNLOAD TO '/export/home/permit/owneraddress.txt'
SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
state.code_table_cd,
address.zip_postal_code,
country.code_description,
address.line_care_of,
prop_role.code_table_cd

FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role

WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate()
AND state.id = address.province_state_cd
AND country.id = address.country_cd
AND prop_role.id = ppi.prop_role_cd
AND prop_role.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'

View 5 Replies View Related

Table Aliases?

Nov 9, 2004

Does anyone know of a neat way of aliasing tables in MS SQL Server 2000? I have a table [X] and I want to refer to it (in TSQL statements, stored procedures, etc.) by two names, say [X] and [Y]. There should be only one copy of the table's data.

I could handle this in my site logic (ie convert [Y] to [X]), but it would be really clean to handle this in the database.

Thanks for your advice.

Matt

View 7 Replies View Related

Aliases To Get Around Error

Mar 26, 2015

trying to write a query from 2 tables and join them only they share a column name of the same name.I can define both aliases seperatrly but get an error when trying both,

select
case_HDR.case_nbr as "test"
Case_DTL.case_nbr as "test2"
From
Case_HDR, case_dtl

I need to join them also but trying to work in stages.

View 12 Replies View Related

How To Use Aliases In Typed Dataset

Jun 27, 2007

I have a SELECT query with an alias in it.
The intellisense shows all field except the alias one.
What goes wrong?

View 2 Replies View Related

Numeric Expressions And Aliases

Mar 8, 2006

I'm in the process of building a site and converting views/tables/queries from an Access database to SQL. I've done this quite a few times, and never had any significant issues I couldn't figure out on my own.

In Enterprise Manager, I've created a view and in the query, I need to create an alias that is similar to below:

SELECT ((monthmult) + ((b2avg*15)-(av2*10)) + (lp1+lp2) + ((b1avg*30)-(av1*20))) as PIndexValue

which is how the formula reads in the Access view.

However, when I got to run the query, SQL strips out all of the parentheses and calculates the value in left to right order:

(monthmult + b2avg*15-av2*10 + lp1+lp2 + b1avg*30-av1*20) as PIndexValue

Which gives me an incorrect value.

Does anyone know why this is happening, or am I just unaware of the right way of doing it?

Thank you,

Derrick

View 3 Replies View Related

Using Data As Column Aliases

Jan 15, 2008

Hi,

I’m working with a really old design migrated to SQL 2005, in which I basically have two tables…

Table 1 contains all the “proper” data, and has columns called: col_1, col_2, col_3

Table 1’s data is something like:

col_1, col_2, col_3
Jack,jack@yahoo.ca,Toronto
Jill,jill@hotmail.com,Montreal

Table 2 contains meta-data for Table 1, specifically, it has two columns: column, meta-data

Table 2’s data is something like:

column,metadata
col_1,name
col_2,email
col_3,city

(Hopefully, my description of the design makes sense….basically; Table 2’s data describes what’s in each column of Table 1).

So, the question, if I want to write a ‘SELECT’ on Table 1, how can I use the data in Table 2 as aliases (or column) headers.

I’m currently going down the path of building dynamic SQL statements in T-SQL….but before I get too far, wanted to vet this idea here (it’s always been a fantastic resource for me)

Thanks in advance!

View 12 Replies View Related

Multi Aliases With The Same Column?

Jun 17, 2008

TABLE1
======================

PriceList
---------
1
2
3
1
2
3
1
2
3


Price
-----
777
888
999
777
888
999
777
888
999
(pretend these columns are side by side)
======================


I need to make a query to:
SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3


the output that i want is:

PRICE1
------
777
777
777

PRICE2
------
888
888
888

PRICE3
-----
999
999
999
(pretend these columns are also side by side)

View 6 Replies View Related

Numeric Aliases For Columns

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

64 Bit Server Aliases SQL 2005

Feb 15, 2007

Does anyone know how to create SQL Server aliases for Itanium servers?

Alternatively does anyone know how to use SMO to create a SQLAliasCollection for a specific server? I can't seem to find how to get that collection.

I've added aliases that work for 32 bit applications but they don't seem to be used for 64 bit applications.

View 3 Replies View Related

Aliases &&amp; Columns Name W/ Spaces

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

Reuse Of Field Aliases

Jan 3, 2007

I have been working with SQL for quiet a while but think this perhaps is a very basic question that has always escaped me:

At my work I was exposed to both, MS SQL Server 2000 and Sybase Adaptive Server Anywhere/Sybase SQL Anywhere.

Under Sybase I was able to use aliases in other calculations and filters but i have never been able to do the same with SQL.

Example:
In Sybase I can write this:

Select Price * Units as Cost Cost * SalesTax as TotalTaxFrom Invoice Where TotalTax > 3.5
However if i want to do this in MS SQL 2000 i have to go trough


Select
Price * Units as Cost
Price * Units * SalesTax as TotalTax
From Invoice
Where (Price * Units * SalesTax) > 3.5

In the long run this is costing me a lot of code redundancy, not to mention a debugging nightmare. Is there a way to replicate this alias usage in MS SQL Server?

View 3 Replies View Related

SQL 2012 :: Multiple Aliases Of Same Table?

Mar 4, 2015

TableX
-------
X1
X2
X3

TableY
-------
Y1
Y2
Y3

I need to write a query with the following joins:

TableX.X1 = TableY.Y1
TableX.X2 = TableY.Y2

What is the proper way to do this in SQL? Would it be:

select x.*
from TableX x
join TableY y1 on y1.Y1 = x.X1
join TableY y2 on y2.Y2 = x.X2

Is there a more proper way to do this without creating multiple aliases of the same table?

View 2 Replies View Related

Aliases And Remote Connection Failure

Nov 14, 2007

Just stumbled over the interesting bug -
if you have an SQLExpress server and the "allow remote connection" in SQL Instance is turned ON, it doesn't means that it works if you create some aliases for this instance

If Im trying to connect to SQLExpress via new created alias ('localhost' for example) I got the "SQL Server does not allow remote connections" error. Albeit, connecting to ".SQLExpress" is successful.
I've checked the properties of locahost DB instance in SQL Management Studio - remote connection is allowed.
I've restarted the SQLExpress instance.
There is no SQLServer or other instances on my PC

But it doesn't work

How to fix this and allows to connect to sql instance via aliases?

View 2 Replies View Related

Column Aliases In Case Statement In Order By

Jan 18, 2007

Hi All,

I have this query :

select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then col99
end

when i execute the above query it gives me the following error message.

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'col99'.

Thanks in advance.

Thanvi.

View 5 Replies View Related

Refering To Distinct Values Post(My Other Post)

Oct 22, 2007

---------------------------------------------------------------
My Original Post
I have to query n table(NLRImports) using the Distinct keyword, to retrieve a set of ID numbers. ( "Select DISTINCT id_nbr from NLRImport" ).

Now i want to use those values i retrieved, to process the records in the table(NLRImports) 1 by 1. How do i use those ID no's i retrieved as Variables or parameters for my next query?? If this makes sense?
----------------------------------------------------------------

First, thanks for the response.... now here is what im trying to do.
I created a simple application in delphi to import information to a table in MSSql2005. This is some of the resulting columns...

date | id_nbr | account_nbr | sub_account_nbr | ... etc
-------------------------------------------------------------

Now there will be several entries with the same id no but on different dates, so i take it dates would rather be my pkey.

Then i need to take one person's entries(i work on id_nbr) and go thru all the entries taking the earliest date and comparing all the other entries for that person to the first date and select all the dates more than 19 days after the first date and less than 91 days from first date and place it in a new table.
I used cursor s and while loops to kind of get it going but i know that cursors are not really recommended use but the performance implications dont bother with this particular job.

What other ways should i be using to accomplish this?

thanks, i hope this is clear...

View 1 Replies View Related

Clustering, Virtual Sql Servers And Client Network Aliases

Oct 31, 2006

I have a question regarding the nature of virtual sql servers, specificially what protocol is used to communicate to the server when a request is made by a client.

For example, if a scheduled job is run on the virtual sql server, what determines the protocol used (e.g. TCP/IP, named pipes etc.) by SQL Server agent? Is it the client network alias set up on the virtual server?

I am asking because currently the client aliases on some of our virtual sql servers are using named pipes and I think this is causing a problem with our backups.

Thanks,
Adrian.

View 8 Replies View Related

Server Aliases/linked Servers Cause Remote Queries

Apr 4, 2008

Can someone please shed some light on what seems to me to be a common requirement.

If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).

Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.

It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?

If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!

I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.

View 2 Replies View Related

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

Query View Vs Query Tables Directly

May 19, 2008

Greetings,

I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.

I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?

Thanks in advance for your replies.

View 3 Replies View Related

Query For A View

Jun 28, 2005

Hello,I have a View called View1 with the field ID, F1, F2, F3. Now I need to check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records (so if condition matches, I need to bring rows, not only totals) how can I write my view query to handle this?Thanks,

View 1 Replies View Related

Help With A Query/view

Apr 25, 2005

Hi all!

I have a table that looks like this:

m_id | s_id1 | s_id2 | dt
m1 | 1 | 2 | 2001-01-01
m2 | 3 | 2 | 2001-01-02
m3 | 4 | 1 | 2001-01-03
m4 | 2 | 3 | 2001-01-04
m5 | 1 | 2 | 2001-01-05
m6 | 5 | 3 | 2001-01-06
...

I need to create a view that displays ONE m_id for every s_id, and that m_id should be the latest one...

ie:
s_id | m_id
1 | m5
2 | m5
3 | m6
4 | m3
5 | m6

I've been trying hard for a while, but I simply can't get it do display only one row per s_id...

any helponthis one would be greatly apreciated...

cheers.

View 3 Replies View Related

Query View

Oct 5, 2006

Hi all, I ran a query, pasting in my sql text. I received this error.

Msg 8163, Level 16, State 3, Procedure All_Participants, Line 7

The text, ntext, or image data type cannot be selected as DISTINCT.

Msg 208, Level 16, State 1, Procedure Distinct_Proposals, Line 7

Invalid object name 'Proposal_Details'.

But, I cannot see the msg numbers in my script. How do I find Msg 8163? Excuse the ignorance....only my second day using this :P

Thanks,

Steve

View 3 Replies View Related

How To Query A View From The Master DB?

Apr 7, 2004

Is there a way to access your views in x database from the master database?

I'm getting this error

Invalid object name 'v_StatisticsScalars'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.

View 4 Replies View Related

Query Problem --with A View

Feb 17, 2006

I have a View which does what I need it too-select distinct d.deptid, d.[description], d.supervisor, d.approvedby, case when c1.cnt = c2.cnt then 'reviews are complete' else 'reviews still in progress' end as deptstatusFrom department dLeft join (Select Count(*) cnt, deptid from employee group by deptid) c1 on (c1.deptid = d.deptid)Left join (Select Count(*) cnt, deptid from employeeeval join employee on (employeeeval.employeeid = employee.employeeid) group by deptid) c2 on (c2.deptid = d.deptid)Brings back data as follows:Cast -  Cast - 00999 - 09246 - reviews still in progressCMP- Copper Metal Products - 03315 - 09246 - reviews are completeCNS- Copper Narrow Strip - 07530 - 09246 -  reviews still in progressCRW- Copper Rod and Wire - 01950 - 09246 - reviews still in progressSo I did the following: select e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName, CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status, d.deptstatusfrom vw_DeptStatus d Left OUTER JOIN Employee e ON e.deptid = d.deptid LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') -- and (ev.PersonalScore is null)ORDER BY e.DeptID, e.LastNameNow this isn't bringing back what I want--If run the query w/out joining it up w/the view it brings back the following:
Cast - Atkins, Carl - 09935 - Chris Burke - Not StartedCast - Bridges, James - 09929 - Chris Burke - In Progress CNS - Cunningham, Kenton - 02100 - Kahle Rummer - Not StartedCNS - Mitchell, Bill - 06200 - Kahle Rummer - In ProgressNow what I really need it to do is (below are the results from my View joined in with my query) -- return all the dept's--regardless if they are finished.  If they are complete I only need ONE row, not all the rows for that dept. (highlighted in RED) I added these rows in-- Doesn't have to be NULL but maybe a blank string.Cast - Charlton, Maurice -  01313 - Chris Burke - In Progress - reviews still in progressCast  - Dorsey, Steve - 02455 - Chris Burke - In Progress  - reviews still in progressCMP - NULL - NULL - Fred Grove - NULL - reviews are completeCNS  - Bennett, Mark -  09911 - Kahle Rummer -  In Progress  - reviews still in progressCNS - Buckingham, Mark - 00964 - Kahle Rummer  - In Progress - reviews still in progressCRW - Eubanks, Kellie - 07599 - Rick Cramer - In Progress - reviews still in progressCRW - Luikart, Tyler- 09908 - Rick Cramer - In Progress - reviews still in progressMicroMll - NULL - NULL - Tim Cross - NULL - reviews are completeI hope this makes sense to someone -- if you have any questions just ask me.  Another note I need to bring this back in a DS --for Crystal Reports.  So the outline looks like this:
Dept ID- Supervisor - Reviews still in Progress   Any unfinished Reviews for that dept
Dept ID- Supervisor- Reviews Complete   No data shown since COMPELTEDept ID- Supervisor - Reviews still in Progress   Any unfinished Reviews for that dept

View 3 Replies View Related

Query Results To A View

Nov 15, 2004

I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields : id and child. So, my table currently looks like this:

id child1 child2 child3

1 sam bob chris

and i would like it like this......

id child

1 sam

1 bob

1 chris

Can anybody help me? Thanks in advance,

Bob

View 3 Replies View Related

View Data In Query

Apr 9, 2008

Hi,
I have an accounting table with 5 year of data for an account, the table headers look as following

Acctno___Year___db_1,db_2,db,db_3,db4,….db_12,Cr_1,cr_2,cr_3…,cr-12

I want to with select statement to convert the data to query the header must be the following:-

Years1___Year2___year3___year4____year5
Db_1
Db_2
Db_3
.
.
.

Thanks for any help

View 3 Replies View Related

Converting Query To A View?

Jan 23, 2015

I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.

Create view vwMAW_KnowledgeDB AS
Select * from don0001
left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null

quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2

Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.

View 1 Replies View Related

Urgent - View Query

May 29, 2007

Dear Friends
I have 3 form with these names :

Clients <----- Projects <------- Files

I am using this query in my [ web ] page :

select clients.id ,
(select count(*) from projects where projects.clients_id = clients.id) as TotalProjects

from clients



I want to compute SUM for all relevant files for each client's projects.
how i can add it to select select statement ??


thank you.
m.o

View 5 Replies View Related







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