join or subselect: create

Showing posts with label create. Show all posts

Friday, March 30, 2012

Jump to URL popup

I am trying to create a popup window from a report.
Looked thru some previous posts and generated this string in the "Jump to
url:"
="javascript:void(window.open('http://www.yahoo.com',
'popup','location=no,toolbar=no,left=100,top=100,height=600,width=800'))"
But when I try to preview the report I get,
Processing errors:
The url javascript:void(window.open('http://www.yahoo.com',
'popup','location=no,toolbar=no,left=100,top=100,height=600,width=800')) has
an invalid schema.
URLs in reports may only use http://, https://, ftp://, mailto: or news:
I tried with and without "http://" in the string.
TIA,
Surekha.You need to install SP1.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Surekha" <Surekha@.discussions.microsoft.com> wrote in message
news:770516DE-1A42-4FA4-9832-AA107EA59A12@.microsoft.com...
> I am trying to create a popup window from a report.
> Looked thru some previous posts and generated this string in the "Jump to
> url:"
> ="javascript:void(window.open('http://www.yahoo.com',
> 'popup','location=no,toolbar=no,left=100,top=100,height=600,width=800'))"
> But when I try to preview the report I get,
> Processing errors:
> The url javascript:void(window.open('http://www.yahoo.com',
> 'popup','location=no,toolbar=no,left=100,top=100,height=600,width=800'))
has
> an invalid schema.
> URLs in reports may only use http://, https://, ftp://, mailto: or news:
> I tried with and without "http://" in the string.
> TIA,
> Surekha.sql

Monday, March 26, 2012

Joins with XQuery

Hello,
I have a very simple data table:

CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)

with the following content:

ID = 1
OutputPayload:

<ReportDocument>
<ALMSimulationResult>
<selectedModelAssets>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01" description="Global Bond" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0" initialCostUpFront="0.02" regularCostUpFront="0.015" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF00FFD4" stochasticDuration="5">
<models>
<model ID="0e70216f-48ce-4f6c-b2d6-519a5cdfd246" type="corporate grade bond" description="Eurozone Corporate Bond Intermediate (D=5Years)" weight="1" />
</models>
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec" description="America Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0.005" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF3B00FF" stochasticDuration="13.55">
<models>
<model ID="b0817f64-5090-48a3-b58c-aa8f6e5bbdc1" type="equity" description="US Value Style (Eur)" weight="0.9" />
<model ID="677e8aae-7b32-4dc3-88c5-e9302dddad8f" type="conventional bond" description="Euro Cash (TBill)" weight="0.1" />
</models>
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" description="Europa Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0.001" annualPerformanceFee="0.001" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF9D00FF" stochasticDuration="17.5">
<models>
<model ID="86fedd24-2a92-422c-b733-17c60105ff81" type="equity" description="Asia Value Style (Eur)" weight="0.1" />
<model ID="55425529-8adc-47d8-a36d-8cfd9da34880" type="conventional bond" description="Italian Long Term Gov Bond" weight="0.1" />
<model ID="fea29db9-cf0e-4802-bcbe-e2b8d367f0ca" type="cash" description="Euro Cash (Euribor 1m)" weight="0.1" />
<model ID="8e58d785-5fc5-4ede-8ec8-eb1af8e62541" type="equity" description="Eurozone Value Style" weight="0.7" />
</models>
</modelAsset>
</selectedModelAssets>
<savingModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<surplusModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<modelAssetTimeSeries>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.027353" />
<tValues t="2" value="0" annualYield="0.027288" />
<tValues t="3" value="0" annualYield="0.027237" />
<tValues t="4" value="0" annualYield="0.027274" />
<tValues t="5" value="0" annualYield="0.027262" />
<tValues t="6" value="0" annualYield="0.02722" />
<tValues t="7" value="1453" annualYield="0.027258" />
<tValues t="8" value="1457" annualYield="0.027258" />
<tValues t="9" value="1460" annualYield="0.027219" />
<tValues t="10" value="1463" annualYield="0.027259" />
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.065466" />
<tValues t="2" value="0" annualYield="0.063841" />
<tValues t="3" value="0" annualYield="0.063707" />
<tValues t="4" value="0" annualYield="0.063692" />
<tValues t="5" value="0" annualYield="0.062438" />
<tValues t="6" value="0" annualYield="0.064081" />
<tValues t="7" value="0" annualYield="0.063476" />
<tValues t="8" value="0" annualYield="0.064294" />
<tValues t="9" value="0" annualYield="0.062034" />
<tValues t="10" value="0" annualYield="0.065144" />
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978">
<tValues t="0" value="830" annualYield="0" />
<tValues t="1" value="1641" annualYield="0.06504" />
<tValues t="2" value="2456" annualYield="0.063229" />
<tValues t="3" value="3278" annualYield="0.062939" />
<tValues t="4" value="4104" annualYield="0.062825" />
<tValues t="5" value="4935" annualYield="0.061233" />
<tValues t="6" value="5772" annualYield="0.063522" />
<tValues t="7" value="5155" annualYield="0.062448" />
<tValues t="8" value="5994" annualYield="0.063548" />
<tValues t="9" value="6837" annualYield="0.061053" />
<tValues t="10" value="7688" annualYield="0.06525" />
</modelAsset>
</modelAssetTimeSeries>
</ALMSimulationResult>
</ReportDocument>

When I run the following command from SQL server:

SELECT
N.ma.value('@.ID', 'uniqueidentifier') as ModelAssetID,
N.ma.value('@.description', 'nvarchar(255)') as ModelAssetDescription,
N.ma.value('@.minimumLimit', 'float') as ModelAssetMinLimit,
N.ma.value('@.maximumLimit', 'float') as ModelAssetMaxLimit,
N.ma.value('@.stochasticDuration', 'float') as ModelAssetDuration,
N.ma.value('@.color', 'char(9)') as Color,
N1.ma1.value('tValues[1]/@.value', 'float') as ActualAssetMix
FROM ALMPayloads A1 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)
LEFT JOIN ALMPayloads A2 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)
ON N.ma.value('@.ID', 'uniqueidentifier') = N1.ma1.value('@.ID', 'uniqueidentifier')
WHERE A1.ID = 1000
ORDER BY ModelAssetDuration

I get the following result:

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 96803
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Instead of

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Why do I have so much duplicates and some random results (I mean the value where I get 96803) ?

Thanks,
Pierre

Pierre, I just tried running the following query. At first, I got no results. When I changed the AI.ID predicate to AI.ID = 1 then I got three rows back (your expected results).

Are you sure there is not other data in the table? I notice that you are doing a self join on the the table [ALMPayloads] but I don't see any predicate on the A2 table alias. I suspect that this could be the reason why you are seeing the additional rows in your results if in fact there are other rows in that table.|||Hi John,
try to add another record (the same xml content with 2 different IDs) and the result will be duplicated.

How can I make the join (on xml data) with the expected result ?

Thanks,
Pierre|||You need to add "AND A1.ID = A2.ID" to your join condition. If you don't need the LEFT JOIN semantics, you can do away with the self join and add another CROSS APPLY.

ALMPayloads A1
CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)

sql

Joins with XQuery

Hello,
I have a very simple data table:

CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)

Why do I have so much duplicates and some random results (I mean the value where I get 96803) ?

Thanks,
Pierre

Pierre, I just tried running the following query. At first, I got no results. When I changed the AI.ID predicate to AI.ID = 1 then I got three rows back (your expected results).

How can I make the join (on xml data) with the expected result ?

Thanks,
Pierre|||You need to add "AND A1.ID = A2.ID" to your join condition. If you don't need the LEFT JOIN semantics, you can do away with the self join and add another CROSS APPLY.

Friday, March 23, 2012

Joins Across Servers/Databases

I am trying to determine how best to create a query that will allow me to perform 'joins across servers'.

I want to create a single join that will join my production database (Oracle) with my Land Database (MSSQL) on a common key.

From what I have gathered on other sites, it appears that SQLServer can perform this function, but I am not that technical and I am not a SQL programmer (eventhough I have access to one), and I am hoping there are some point and click apps that will let me perform this function.

Any thoughts ?Hello,

the best way to join two databases for example from Oracle to MSSQL is to use ADBC. ADBC is a software package that expands the Oracle database. Once installed, you can select the MSSQL tables that should be visible in Oracle.

If you like, we can assist you installing ADBC and developing the necessary functions for MSSQL to Oracle access.

Please send me a mail to m.peter@.alligatorsql.com if you need further help in this way.

Hope that helps ?

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Joining two tables to modify a tree structure with more information

I'm trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry for the tabs that won't work)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
1506 3823136510667
1507 3824136510667
1509 3825136410667
1509 3826136410667
I have tried many ways, but I'm not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable

Thanks :)Worked it out.Used inner join to get the first data, and run an update afterwards to update level 3 and 4sql

Joining two tables to modify a tree structure

I’m trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but I’m not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

Quote:

Originally Posted by KingKong07

Im trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but Im not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

I think you have to Innerjoin the tables

Just go through this|||Thanks for answer, I had made it a bit to complex.

Worked it out.

Used inner join to get the first data, and run an update afterwards to update level 3 and 4|||

Quote:

Originally Posted by KingKong07

Im trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but Im not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

1- i think you should have a MainCatalogId in your second table.because if you have many Maincatalog you can not save relation for other Maincatalog if you have repeated code of items.
2-you must create a dataset returning storedprocedure with the columns for your output.
3-in your storedprocedure you must have three cursor with three loop for three level(suppose you have three level),each loop contains parent loop.then fetch what you want to output for your output dataset.
4-use your storedprocedure As a table in your application .

Wednesday, March 21, 2012

Joining two reporting services datasets

Hi All,
It is possibile to create a dataset that is a join of two other datasets
(the two datasets are of diverse origin: the first is OLAP the second is
relational)?
Example:
I have dataset 1 and 2 I want to create the third that is a join of the 1
and the 2.
Can anyone help me?
Thank you.if it's just a SQL command you could create a 3rd dataset?
"gdaquila" wrote:
> Hi All,
> It is possibile to create a dataset that is a join of two other datasets
> (the two datasets are of diverse origin: the first is OLAP the second is
> relational)?
> Example:
> I have dataset 1 and 2 I want to create the third that is a join of the 1
> and the 2.
> Can anyone help me?
> Thank you.
>
>|||You can not do this within RS. You either need to have a stored procedure
doing this (can use linked databases) or you need to use subreports.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gdaquila" <gdaquila@.nospam.it> wrote in message
news:2vhku5F2lqt1fU1@.uni-berlin.de...
> Hi All,
> It is possibile to create a dataset that is a join of two other datasets
> (the two datasets are of diverse origin: the first is OLAP the second is
> relational)?
> Example:
> I have dataset 1 and 2 I want to create the third that is a join of the 1
> and the 2.
> Can anyone help me?
> Thank you.
>|||Hi,
Ok,
I will try with a linked server.
You think that I can follow the sample published by microsoft at
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
In this samples I have one regarding the use of linked server.
The last my question is: in this approach, the multidimensional data must be
retrieved via SQL Query not via MDX query it is correct?
Thank you!
G
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> ha scritto nel messaggio
news:eiHEqHDyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> You can not do this within RS. You either need to have a stored procedure
> doing this (can use linked databases) or you need to use subreports.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "gdaquila" <gdaquila@.nospam.it> wrote in message
> news:2vhku5F2lqt1fU1@.uni-berlin.de...
>> Hi All,
>> It is possibile to create a dataset that is a join of two other datasets
>> (the two datasets are of diverse origin: the first is OLAP the second is
>> relational)?
>> Example:
>> I have dataset 1 and 2 I want to create the third that is a join of the 1
>> and the 2.
>> Can anyone help me?
>> Thank you.
>>
>|||Sorry, I don't have any experience with MDX queries.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gdaquila" <gdaquila@.nospam.it> wrote in message
news:2vjgbgF2lorcjU1@.uni-berlin.de...
> Hi,
> Ok,
> I will try with a linked server.
> You think that I can follow the sample published by microsoft at
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
> In this samples I have one regarding the use of linked server.
> The last my question is: in this approach, the multidimensional data must
be
> retrieved via SQL Query not via MDX query it is correct?
> Thank you!
> G
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> ha scritto nel messaggio
> news:eiHEqHDyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> > You can not do this within RS. You either need to have a stored
procedure
> > doing this (can use linked databases) or you need to use subreports.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "gdaquila" <gdaquila@.nospam.it> wrote in message
> > news:2vhku5F2lqt1fU1@.uni-berlin.de...
> >> Hi All,
> >> It is possibile to create a dataset that is a join of two other
datasets
> >> (the two datasets are of diverse origin: the first is OLAP the second
is
> >> relational)?
> >> Example:
> >> I have dataset 1 and 2 I want to create the third that is a join of the
1
> >> and the 2.
> >> Can anyone help me?
> >> Thank you.
> >>
> >>
> >
> >
>

Joining two dropdown lists to create third dropdownlist

How do i add two values of Dropdownlist 1 and 2 to create No.3

Is there an easy way through Visual Studio to do this than Realms of code that i have been reading online,

Surely, there is some command that is like Select Distinct Name from Clients WHEREcity=@.city +country=@.country

Or is it not this easy?

For multiple conditions you would need to use AND.

Select Distinct Name from Clients WHEREcity=@.city ANDcountry=@.country.

|||

Thanks,

The statement "Select Distinct Name from Clients WHEREcity=@.city ANDcountry=@.country." works fine in the query builder but how do i link Dropdownlist 2 to it.

I have tried all different ways in the Define Paramers Option in Dropdownlist 3 but have had no luck.

Any help would be appreciated.

I am working through VStudio2005 and am new to programming so am building dropdownlists from the toolbox etc.

Thanks,

Sully

Monday, March 19, 2012

Joining Dummy Columns

Is it possible to create a join on two tables when one of the columns has been derived.

e.g.

Table 1

ID Name Price Qty

1 Sand 12,00 2

Table 2

Week Top_Sales

1 24

e.g.

Select a.ID, a.price, a.qty, a.qty *a.price as "Total Sales"

from Table 1 a

inner join on table 2 b

b.top_sales = Total Sales

Is this possible to perform this join without creating a temporary table or a view?

Sure, but you can't use the named column from the SELECT clause because that name hasn't been materialized yet. You will need to do:

Select a.ID, a.price, a.qty, a.qty *a.price as "Total Sales"
from Table 1 a
inner join on table 2 b
on b.top_sales = a.qty *a.price

This seems a very dangerous query, if you ask me though. In your table2, why not include the primary key of the top sales rows? Then you wouldn't have to do this wonky join and you could join on keys. I guess this because you have a fixed amount for the top seller, so you had to have calculated it from the quantity and price somewhere.

Monday, March 12, 2012

Joining datasets returned from stored procedures.

Hello,
I am restricted to using stored procedures for data access. I would like to
be able to create a "Text" Dataset named C which is a join of two "Stored
Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.Tyr this:
SELECT * FROM OPENQUERY(servername, 'sp_1')
union
SELECT * FROM OPENQUERY(servername, 'sp_2')
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:%23%23nvqx4YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am restricted to using stored procedures for data access. I would like
to
> be able to create a "Text" Dataset named C which is a join of two "Stored
> Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.
>

Joings between two table variables

Is it possible to create a join between two table variables?
I tried doing something like this .. and it is throwing an error.
Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
Insert @.tstTableOne Values(1, 'Dummy1')
Insert @.tstTableOne Values(2, 'Dummy2')
Insert @.tstTableTwo Values(1, 'Test1')
Insert @.tstTableTwo Values(2, 'Test2')
Update @.tstTableOne Set @.tstTable.FirstName = t.FirstName from @.tstTableTwo
t where t.Sno = @.tstTableOne.Sno
Regards
PradeepTry:
UPDATE t1
SET t1.FirstName = t2.LastName
FROM @.tstTableOne t1
INNER JOIN @.tstTableTwo t2 ON t1.Sno = t2.Sno
Let me know how you get on.
Damien
"SqlBeginner" wrote:

> Is it possible to create a join between two table variables?
> I tried doing something like this .. and it is throwing an error.
> Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
> Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
> Insert @.tstTableOne Values(1, 'Dummy1')
> Insert @.tstTableOne Values(2, 'Dummy2')
> Insert @.tstTableTwo Values(1, 'Test1')
> Insert @.tstTableTwo Values(2, 'Test2')
> Update @.tstTableOne Set @.tstTable.FirstName = t.FirstName from @.tstTableTw
o
> t where t.Sno = @.tstTableOne.Sno
> Regards
> Pradeep|||See if this helps
Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
Insert @.tstTableOne Values(1, 'Dummy1')
Insert @.tstTableOne Values(2, 'Dummy2')
Insert @.tstTableTwo Values(1, 'Test1')
Insert @.tstTableTwo Values(2, 'Test2')
Update @.tstTableOne Set FirstName =LastName FROM
@.tstTableTwo t JOIN @.tstTableOne T1 ON t1.Sno =T.Sno
"SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
news:3ACD2367-C9D7-46F9-9938-88C278D6C9C0@.microsoft.com...
> Is it possible to create a join between two table variables?
> I tried doing something like this .. and it is throwing an error.
> Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
> Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
> Insert @.tstTableOne Values(1, 'Dummy1')
> Insert @.tstTableOne Values(2, 'Dummy2')
> Insert @.tstTableTwo Values(1, 'Test1')
> Insert @.tstTableTwo Values(2, 'Test2')
> Update @.tstTableOne Set @.tstTable.FirstName = t.FirstName from
> @.tstTableTwo
> t where t.Sno = @.tstTableOne.Sno
> Regards
> Pradeep|||Thanks it works. But any idea what is the bug in my Update statement?
Regards
Pradeep
"Damien" wrote:
> Try:
> UPDATE t1
> SET t1.FirstName = t2.LastName
> FROM @.tstTableOne t1
> INNER JOIN @.tstTableTwo t2 ON t1.Sno = t2.Sno
> Let me know how you get on.
> Damien
> "SqlBeginner" wrote:
>|||From BOL (UPDATE described):
"A table alias specified in a FROM clause cannot be used as a qualifier in
SET column_name"
You're mixing ANSI syntax with the T-SQL extension for UPDATEs and DELETEs
which allows you to specify multiple tables in a FROM clause. When using th
e
T-SQL syntax I _always_ specify the alias in the UPDATE clause and do not mi
x
aliased tables and non-aliased tables.
So,
UPDATE t
NOT UPDATE your_table
unless you are only updating one table, in which case "UPDATE your_table" is
correct.
Hope that helps!
Damien
"SqlBeginner" wrote:
> Thanks it works. But any idea what is the bug in my Update statement?
> Regards
> Pradeep
> "Damien" wrote:
>|||IMHO while using Joins in Table variables by definition or by rule we need t
o
use Alias names only. Since pradeep wasn't using that it throwed an error.
If at all he had tried this with Temp tables his logic would have worked as
it is without any hassles. Bcoz Temp tables doesn't have that rule.
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"Damien" wrote:
> From BOL (UPDATE described):
> "A table alias specified in a FROM clause cannot be used as a qualifier in
> SET column_name"
> You're mixing ANSI syntax with the T-SQL extension for UPDATEs and DELETEs
> which allows you to specify multiple tables in a FROM clause. When using
the
> T-SQL syntax I _always_ specify the alias in the UPDATE clause and do not
mix
> aliased tables and non-aliased tables.
> So,
> UPDATE t
> NOT UPDATE your_table
> unless you are only updating one table, in which case "UPDATE your_table"
is
> correct.
> Hope that helps!
>
> Damien
> "SqlBeginner" wrote:
>|||This is what I meant,
Create table #tstTableOne table ( Sno int, FirstName varchar(100) )
Create table #tstTableTwo table ( Sno int, FirstName varchar(100) )
Insert #tstTableOne Values(1, 'Dummy1')
Insert #tstTableOne Values(2, 'Dummy2')
Insert #tstTableTwo Values(1, 'Test1')
Insert #tstTableTwo Values(2, 'Test2')
Update #tstTableOne Set #tstTableOne.FirstName = t.FirstName from
#tstTableTwo t where t.Sno = #tstTableOne.Sno
Select * from #tstTableOne
Select * from #tstTableTwo
Drop table #tstTableOne
Drop table #tstTableTwo
Best Regards
Vadivel
http://vadivel.blogspot.com
"Damien" wrote:
> From BOL (UPDATE described):
> "A table alias specified in a FROM clause cannot be used as a qualifier in
> SET column_name"
> You're mixing ANSI syntax with the T-SQL extension for UPDATEs and DELETEs
> which allows you to specify multiple tables in a FROM clause. When using
the
> T-SQL syntax I _always_ specify the alias in the UPDATE clause and do not
mix
> aliased tables and non-aliased tables.
> So,
> UPDATE t
> NOT UPDATE your_table
> unless you are only updating one table, in which case "UPDATE your_table"
is
> correct.
> Hope that helps!
>
> Damien
> "SqlBeginner" wrote:
>

Friday, March 9, 2012

Join with a Having clause -- having problems too

Could someone please help me with a query that I am trying to create or suggest a better way?

What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).

When I put this critera (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) in the Where clause I get an error "An aggregate may not appear in a Where unless it is in a subquery contained in a Having...etc."

SELECT DISTINCT tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, Sum(tbl_ProductionInfo.Production) AS SumOfProduction, tbl_Mertering.PMType

FROM tbl_ProductionInfo LEFT JOIN tbl_Mertering ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType

WHERE tbl_Mertering.DateOfChange>=tbl_ProductionInfo.EntryDate AND tbl_Mertering.UD2=0 AND tbl_ProductionInfo.LineNum= tbl_Mertering.LineNum AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType

GROUP BY tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, tbl_Mertering.PMType

HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)Can you post the DDL for the tables?|||Sorry for the late reply as I have been in meetings all afternoon and please excuse my ingorance, but I am not sure what the DDL is.|||DDL: Data Definition Language.

The SQL Statements that can be used to create the tables and objects involved in your problem, or at least the relevant parts.

Brett is asking for more information on your table design.

blindman|||Here is a little more history.
The production table already existed. I am attempting to create a "meterting" scheduler for our home grown CMMS. We want to create workorders (table that already exists) based on the amount of production that has occurred. I created the metering table to holds the date that work is done. I want to keep each record for historical data.

The flow of the program is
User completes the existing work order; this action creates a record in the metering table; DateOfChange is populated UD2 defaults to 0.

Each day the job runs that looks for Metering records with UD2 = 0 and sums the production for that line, equiptype, dept, ItemDesc. If the sum is greater >= LifeCycle then I write a new work order and change the UD2 =1.

I run this job as an active X (because I am weak in SQL). The writing of the work order and the changing of UD2 works fine. I just can't get the sum right because it is not picking up the >= Max(DateOfChange).

Here is the table structure.

tbl_ProductionInfo Columns
Name Type Size
ProdID int (autonumber) 4
EntryDate Date/Time 8
LineNum Text 10
Shift Text 10
SubEmp Text 35
Dept Text 10
EquipType Text 35
ProductType Text 10
ContNum int 4
Production int 4
ScheduledTime int 4
OnHold int 4
Speed int 4
Potential int 4
PM int 35
EditedBy Text 35
DateCode Text 15
Employee Text 35
UtilTime int 4
Util float 8

tbl_metering Name Type Size
Id int (autonumber) 4
Dept Text 35
EquipType Text 35
LineNum Text 2
Station Text 35
ItemDesc Text 35
LifeCycle int 4
DateOfChange smalldate 8
Comments Text 250
PMType Text 40
UD2 int 4
CreateWo int 4|||To start with, rewrite your query like this:

SELECT tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
Sum(tbl_ProductionInfo.Production) AS SumOfProduction,
tbl_Mertering.PMType
FROM tbl_ProductionInfo
inner join tbl_Mertering
ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
and tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange
AND tbl_ProductionInfo.LineNum = tbl_Mertering.LineNum
AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
WHERE tbl_Mertering.UD2=0
GROUP BY tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)

DISTINCT is not need in GROUP BY queries, and your LEFT JOIN is superfluous when you are matching records in the WHERE clause.

Now to your problem...
Does tbl_Mertering hold a history of values, differentiated by DateOfChange, or is DateOfChange just updated every time a record is modified?

blindman|||A new record is written and DateOfChange is added for each record so that I maintain a record of the date the PM was done. The reason for this is that there may be delay in when work is actually done. With the historic data, we can tell what the average production and/or actual production between PM's is.|||What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).

[I can't speak to whether the above Max() code, etc, is appropriate or correct. I simply include it as part of a quote. In fact I don't think it is...]

I suggest that you first construct a query which returns "the production information that is greater than the last date..." This query will return all rows.

Then build a second query which is based on the first (i.e. it takes input from the first), and does the sum.

That combination is clear, easy to understand, and also easy to prove/audit by desk checking. Furthermore, when you go to run the combined query, the DBMS will automatically consider both queries in combination when building the overall execution plan.|||Yes! I do need to approach it differently as I continue to get an incorrect sum of production.

I had thought that I could return a recordset with all the records in the metering table with UD2 = 0 and then with active x loop through the recordset with a second query that would sum the production based on the critera (line, date, equiptype, etc) and sum >= LifeCycle.

I had just hoped that I could learn a cleaner way.
Thanks,
Lee|||My BAD!

I missed the signing
tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange to
tbl_ProductionInfo.EntryDate >= tbl_Mertering.DateOfChange
In the INNER JOIN critera.

Also I found that that collects the production data was not putting the right equipment type in the column. I corrected that as well and now IT SEEMS to be working fine.

Thanks to all for your help!
You are the best!

Join using like?

I would like to create a view that shows each employee's id and there vp's information, if the vp's id is a part of the dbo.HIERARCHY.Hierarchy field (which is a string containing the employee's entire hierarchy).
In enterprise manager it accepts this as a parsable query, but it isn't getting me the data I need. I suspect it has something to do with me not knowing how to add the wildcard characters when searching the dbo.HIERARCHY.Hierarchy field. Any idea how I can fix this?

SELECT dbo.CCINFORMATION.*, dbo.HIERARCHY.AWID AS Employee
FROM dbo.CCINFORMATION INNER JOIN
dbo.HIERARCHY ON dbo.CCINFORMATION.AWID LIKE dbo.HIERARCHY.Hierarchy
WHERE (dbo.CCINFORMATION.Title LIKE '%VP,%') OR
(dbo.CCINFORMATION.Title LIKE '%CEO%') OR
(dbo.CCINFORMATION.Title LIKE '%VICE PR%')The problem is in your join clause:

ON dbo.CCINFORMATION.AWID LIKE dbo.HIERARCHY.Hierarchy

Since you are not using wildcards, your criteria is equivalent to

ON dbo.CCINFORMATION.AWID = dbo.HIERARCHY.Hierarchy

...except for ignoring trailing spaces in AWID.

Try:

ON dbo.CCINFORMATION.AWID LIKE '%' + dbo.HIERARCHY.Hierarchy + '%'

...but I suspect there are more problems in your table design that need to be addressed.

blindman|||Originally posted by blindman
...but I suspect there are more problems in your table design that need to be addressed.

blindman

Blindman, what makes you thinking that?

dyingjoy: if hierarchy contains your employee ID, you have to reverse your join condition:

ON dbo.HIERARCHY.Hierarchy LIKE '%' + dbo.CCINFORMATION.AWID + '%'|||Blindman, what makes you thinking that?

I'm no expert, but I think that for a one time search this query would work fine. As part of a process however, it would be much more efficient to definitavely identify employess as a 'CEO' or 'VP' in a seperate column.

From the query it appears the Title values are something like 'VP of Marketing', 'VP of Finance', etc. For frequent searches on specific portions of this column, it would meake sense to me to standardize the portion you are searching on in its own column.

ie.

CCINFORMATION.EmpType CHAR(3)
CCINFORMATION.Title VARCHAR(20)|||Yup.

Hard-coding strings like 'VP', 'CEO', 'VICE PR' just invites problems when someone enters a title like 'V.P.', 'C.E.O.', or 'Big Kahuna', or when some mid-level manager or department head requests to be included.

blindman

Join to a single date rate table?

Given an exchange rate table like:
Create Table XRates
(
EffectiveDate smalldatetime,
FromCury char(3),
ToCury char(3),
Rate float
)
and Transaction table like
Create Table Trans
(
TranDate smalldatetime,
TranCury char(3),
TranAmt float
)
What would be the best SQL query to join from the Transaction table to the
correct exchange rate based on the transaction date? Any sugestions?
Thanks in advanceHere is one way (SQL Server 2005):
WITH Transactions
AS
(SELECT T.TranDate, T.TranCury, T.TranAmt,
R.ToCury, R.Rate,
ROW_NUMBER() OVER(
PARTITION BY R.FromCury
ORDER BY R.EffectiveDate DESC) AS seq
FROM Trans AS T
JOIN XRates AS R
ON T.TranCury = R.FromCury
AND T.TranDate >= R.EffectiveDate)
SELECT TranDate, TranCury, TranAmt, ToCury, Rate
FROM Transactions
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Wednesday, March 7, 2012

JOIN Statement Help

I am very new to SQL and need to create a statement that will JOIN data from 3 tables into my datagrid. The following are the tables:
Table A: Compliance
- FinancialsID
-NetWorth
-DebtRatio
-WorkCapital

Table B: Financials
- FinancialsID
- cAssets
- TransDate
- CustomerID

Table C: CompanyInfo
- CustomerID
- Company
- Agent

I need to be able to display Company.CompanyInfo, NetWorth.Compliance, DebtRatio.Compliance, WorkCapital.Compliance in a datagrid and make sure that it ONLY displays the most current entry for the Company.

The Compliance table has a relationship to the Financials table through the FinancialsID field and the Financials table is related to the CompanyInfo table through the CustomerID field. The TransDate is a date field in the Financials table.

This seems extremely confusing to me, but I am sure its easier than what I am trying to make it.

Any help would be GREATLY appreciated.

Thanks
Garrettto start with :


SELECT Company.CompanyInfo, NetWorth.Compliance, DebtRatio.Compliance, WorkCapital.Compliance
FROM CompanyInfo ,Financials ,Compliance
WHERE companyInfo.customerid = Financials.customerid AND Financials.FinancialsID=Compliance.FinancialsID

now what do you mean by most current ? does it have anything to do with the date ?|||Thanks for the help on this.

There can be multiple financial records for each company. There is a date field in the Financials table named TransDate so that I can differentiate the records within a company.

The main purpose of my web app is to create a sort of 'PipeLine'. When a user logs into the page he/she is supposed to see the most current financials for each company in their respective pipeline.

For this reason, I need to ensure that only the most current record is returned for each company.

Any Ideas?-

Thanks|||yes but I dont understand what you mean by "current" do you mean today ? what is the condition for the date ? give technical details.|||you need to include the date in ur sql statement and order by date|||I have been able to JOIN the tables and display the correct fields in the datagrid, however, I have been unable to reconstruct the SQL statement so that only the LATEST financial records row is displayed for each Company.

Here is what I have:


SELECT CompanyInfo.Company, CompanyInfo.customerID, CompanyInfo.uname,
Compliance.FinancialsID, Compliance.NetWorth, Compliance.uname,
Compliance.DebtRatio, Compliance.WorkCapital FROM CompanyInfo INNER JOIN
Financials ON CompanyInfo.customerID = Financials.customerID INNER JOIN Compliance
ON Financials.FinancialsID = Compliance.FinancialsID ORDER BY TransDate DESC"

How do I simple return one record for each company and make sure that record is the most current by date (TransDate)?

Thanks|||Think I figured it out.

"SELECT CompanyInfo.Company, CompanyInfo.CustomerID, CompanyInfo.uname, " & _
"Compliance.FinancialsID, Compliance.NetWorth, Compliance.uname, " & _
"Compliance.DebtRatio, Financials.TransDate, Compliance.WorkCapital " & _
"FROM CompanyInfo " & _
"INNER JOIN Financials ON CompanyInfo.CustomerID = Financials.CustomerID " & _
"INNER JOIN Compliance ON Financials.FinancialsID = Compliance.FinancialsID " & _
"WHERE Financials.TransDate = (SELECT MAX(TRansDate) FROM Financials F1 " & _
"WHERE F1.CustomerID = Financials.CustomerID)"

Thanks for everyone's help.

Friday, February 24, 2012

join several fields to create new field

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

122

joe

I would have:

122 joe st

Thanks!

You need to separate the storage of data from it's presentation. Generally you would store data at the lower level, then combine it when you retrieve it. You can do that either in the application that retrieves it, or in SQL. For instance in your example:

Select a + ' ' + b + ' ' + c as d

would give the result you are after

|||Could this be accomplished in a sql view?|||It could, but in my view you're still using the database to process data rather than storing it. You could also use a stored procedure|||I should clarify myself. I want the attribute data in fields

a,b,c to popluate a new field d. I've created field d in the

table with fields a,b,c. Now I just need to populate it.

Thanks.|||Possibley a better way of doing this is in the view because I

don't need to store the actual data. I just need a way to present

it in an application. So if there's a sql statement that I could

add to my existing view, that would work as well.

One caveaet in the current view is that I still want the a,b,c fields to present themselves in the view.

So ...

select a, b, c, d (as the virtual field which concatenates a,b,c)

from f

the presentation would be:

a b c d

123 joe st 123 joe st|||

The field 'd' is totally superfluous. There is no need to duplicate the data in storage.

Select a, b, c, a + ' ' + b + ' ' + c as d

Will produce exactly the output you want. You can put this select either in a stored proc, a view or in raw sql, it doesn't matter.

|||This works ... sort of.

If there are any null fields, it returns nothing. So is there a way around that.

Something can be built into the view that handles: if null then '' otherwise d + ' ' ?|||I've worked with the view and it is working well enough to return valid

results. I can't use it for the application however. I need

to actually store the data in a seperate field.

If there are thoughts on how to make this happen, I would appreciate it.

The problems I see are:

the new field must have spaces - example 122 joe st

there may be a direction but not in all cases - example 122 N joe st

Thanks.|||Depending on how complex you want your logic to be, you could either use Computed Columns (for simple logic. See http://msdn2.microsoft.com/en-us/library/ms191250.aspx) or DML triggers (for more complex logic. See http://msdn2.microsoft.com/en-us/library/ms191524.aspx).

join several fields to create new field

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

122

joe

I would have:

122 joe st

Thanks!

Select a + ' ' + b + ' ' + c as d

would give the result you are after

a,b,c to popluate a new field d. I've created field d in the

table with fields a,b,c. Now I just need to populate it.

Thanks.|||Possibley a better way of doing this is in the view because I

don't need to store the actual data. I just need a way to present

it in an application. So if there's a sql statement that I could

add to my existing view, that would work as well.

One caveaet in the current view is that I still want the a,b,c fields to present themselves in the view.

So ...

select a, b, c, d (as the virtual field which concatenates a,b,c)

from f

the presentation would be:

a b c d

123 joe st 123 joe st|||

The field 'd' is totally superfluous. There is no need to duplicate the data in storage.

Select a, b, c, a + ' ' + b + ' ' + c as d

Will produce exactly the output you want. You can put this select either in a stored proc, a view or in raw sql, it doesn't matter.

|||This works ... sort of.

If there are any null fields, it returns nothing. So is there a way around that.

Something can be built into the view that handles: if null then '' otherwise d + ' ' ?|||I've worked with the view and it is working well enough to return valid

results. I can't use it for the application however. I need

to actually store the data in a seperate field.

If there are thoughts on how to make this happen, I would appreciate it.

The problems I see are:

the new field must have spaces - example 122 joe st

there may be a direction but not in all cases - example 122 N joe st

jOIN Query help(urgent)

i want to create a join query (for view) that will show one data per day for each agent.
just a select query joing these two tables..Seems like the date field is given me problem
i want result like below

TOTALCALL , TOTALESCA , AGENTID , DATE
50 , 5 , IDME1 , 10/28/2004 12:28:00 PM

TOTALESCA shows NuMbers of escalated calls out of totalcalls

table 1

TOTALCALL
AGENTID
DATE

TABLE 2

TOTALESCA
AGENTID
DATE

SAMPLE DATA ON TABLE 1

TOTALCALL, AGENTID, DATE
50 , IDME1 , 10/28/2004 12:28:00 PM

SAMPLE DATA ON TABLE 2

TOTALESCA, AGENTID , DATE
5 , IDME1 , 10/28/2004 12:28:00 PMTry datepart function to ignore the time part in your query and try it. If you still have issue, publish the query you have and I could help|||gives me inaccurate results and date column on select shows wrong data's.

possible unique data is agent id and date (wihout time messed), if i join with agent id then it will filter out table 2
Note.. There's some days agent wont escalate anycall and so therefore no record on table2, but has record on table1.
i want a query that will still show 0 on TOTALESCA column even if agent didnt escalate any call on that day.
Begining to think this is not possible with query
any idea?
eg below

TOTALCALL , TOTALESCA , AGENTID , DATE
30, 0 , IDME1 , 10/28/2004

SELECT table1.TOTALCALL, table2.TOTALESCA,
table2.[agent Id],
datepart(day,table1.Date)
FROM dbo.totalcall table1,
dbo.totalEsc table2
WHERE datepart(day,table1.Date)=datepart(day,table2.Date )|||I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||pat your query work like charm

but i get this message too
Null value is eliminated by an aggregate or other SET operation.

what does that means?|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.

-PatP|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.

-PatP|||pat check your pm|||From the PM, I got:hey pat,
can you help me join this query with another table? the query i got from you yesterday.
there's one more table that has agent name, agent id, supervisor and manager. agent id is unique. i want the same result on this query but now to show agent name, agent id, supervisor and manager. let's say this is table3 and has this columns

agent_ID NVARCHAR(20),
SUP_LAST NVARCHAR(25),
SUP_FRST NVARCHAR(25),
MGR_LAST NVARCHAR(255),
MGR_FRST NVARCHAR(255)

i will love to concenate like
EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS ManagerThere was also some informaiton that pointed back into this thread too. Moving on, I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||To eliminate the error do either:

set ansi_warnings off

or

, Sum(isnull(TOTALCALL, 0)) AS DAY_CALLS
, Sum(isnull(TOTALESCA, 0)) AS DAY_ESCA|||thanks
i had this error
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

but i fixed it when i added all column to groupby
rdjabarov
set ansi_warnings off works but i want to use the query to create view.
where should i put it
im getting error when i try

ALTER VIEW myview
as
set ansi_warnings off
SELECT...

or

ALTER VIEW myview
set ansi_warnings off
as

SELECT ...|||I would recommend that you avoid changing settings to suppress messages. That has always been a receipe for disaster for me. You could also suppress them using:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(Coalesce(TOTALCALL, 0)) AS DAY_CALLS
, Sum(Coalesce(TOTALESCA, 0)) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, EMP_FRST, EMP_LAST, SUP_FRST, SUP_LAST, MGR_FRST, MGR_LAST-PatP|||thanks pat
that works without error|||The setting needs to be set on the connection that is used to create an object, so it needs to preceed the CREATE statement.

Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!|||Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!True, but what I meant was that changing settings (of any kind, any where) to make warning messages go away has always proved to be a disaster for me. I didn't mean that you ought to rely on default settings, I meant that changing settings to suppress messages was a receipe for disaster.

I always try find the underlying source of the problem, and correct it or code to ignore the meassage instead of finding ways to suppress the message.

-PatP|||That's why I gave 2 options, SET and ISNULL (which you changed to Coalesce).

Monday, February 20, 2012

Join problem

CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Users] VALUES('Unal')
INSERT INTO [dbo].[Users] VALUES('Volkan')
INSERT INTO [dbo].[Users] VALUES('Duygu')
INSERT INTO [dbo].[Users] VALUES('Elif')
INSERT INTO [dbo].[Users] VALUES('Mehmet')
INSERT INTO [dbo].[Users] VALUES('Demir')

CREATE TABLE [dbo].[Agenda](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ActivityName] [nvarchar](50) NULL,
[UserId] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Agenda] VALUES('school excursion','1,3,4')
INSERT INTO [dbo].[Agenda] VALUES('party','6,2,3,1')

Table Name : Users

Id UserName

1 Unal

2 Volkan

3 Duygu

4 Elif

5 Mehmet

6 Demir

Table Name : Agenda

Id ActivityName UserId

1 school excursion 1,3,4

2 party 6,2,3,1

I want to join Users table with Agenda table.

The Result has to be like below:

Id ActivityName UserId UserNames

1 school excursion 1,3,4 Unal, Duygu, Elif

2 party 6,2,3,1 Demir, Volkan, Duygu, Unal

How can I do it in SQL 2005. I don't want use cursor.

if I can't do that join, I have to do other join that it's below.

Id ActivityName UserId UserNames

1 school excursion 1 Unal

1 school excursion 3 Duygu

1 school excursion 4 Elif

2 party 6 Demir

2 party 2 Volkan

2 party 3 Duygu

2 party 1 Unal

thanks so much for help

What you are attempting to accomplish is a form of denormalization. While it is not a 'straightforward' task in T-SQL, here are links to a couple of approaches that may work for you.

Lists -Field Concatenation, One Field to Itself for string
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

|||

Thanks everbody who help and answer.

Join Problem

Hi,

I have 2 tables:

CREATE TABLE [dbo].[TBL_CONDITION](
[CONDITIONID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](500) NULL)

CREATE TABLE [dbo].[TBL_CONDITION_CUSTOMER](
[CCAN] [varchar](10) NOT NULL,
[CONDITIONID] [int] NOT NULL,
[FOLLOW-UP_DATE] [nchar](10) NOT NULL)

Sample data is as follows:

INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of statements')
INSERT INTO [tbl_condition] ([NAME])VALUES('Satisfactory review')
INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of latest interim')

INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',1,'03/09/2007')
INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',2,'04/09/2007')

Inrespective of data in child table ([tbl_condition_customer] ) I want all the rows from tbl_condition - I am using outer join for that but am not getting the required output:

SELECT dbo.TBL_CONDITION.CONDITIONID, dbo.TBL_CONDITION.NAME, dbo.TBL_CONDITION_CUSTOMER.CCAN
FROM dbo.TBL_CONDITION
LEFT OUTER JOIN dbo.TBL_CONDITION_CUSTOMER ON dbo.TBL_CONDITION.CONDITIONID = dbo.TBL_CONDITION_CUSTOMER.CONDITIONID
WHERE (dbo.TBL_CONDITION_CUSTOMER.CCAN = '52410')

But I am getting output as that of Inner Join?

What am I missing. I want output as follows:

ConditionId Name CCAN
--
1 Receipt of statements 52410
2 Satisfactory review 52410
3 Receipt of latest interim NULL

Hi JayaC

You could change your select statement to the statement shown below.

Chris

|||Thanks Chris. That worked :)

Friday, March 30, 2012

Jump to URL popup

Monday, March 26, 2012

Joins with XQuery

Joins with XQuery

Friday, March 23, 2012

Joins Across Servers/Databases

Joining two tables to modify a tree structure with more information

Joining two tables to modify a tree structure

Wednesday, March 21, 2012

Joining two reporting services datasets

Joining two dropdown lists to create third dropdownlist

Monday, March 19, 2012

Joining Dummy Columns

Monday, March 12, 2012

Joining datasets returned from stored procedures.

Joings between two table variables

Friday, March 9, 2012

Join with a Having clause -- having problems too

Join using like?

Join to a single date rate table?

Wednesday, March 7, 2012

JOIN Statement Help

Friday, February 24, 2012

join several fields to create new field

join several fields to create new field

jOIN Query help(urgent)

Monday, February 20, 2012

Join problem

Thanks everbody who help and answer.

Join Problem

join or subselect

Blog Archive

About Me