Dear friends 
I am a new developer. I need some SQL Language examples to write some quaries.
Ex.
how you collect last record that relevent for the last record updated for a relevent employee in a record table which includes RecordNo and EmployeeNo, salses
Thanks
Any help is greatly apprciated
Amila
Hi,e.g.
SELECT TOP 1
RecordNo, EmployeeNo, salses
From SomeTable
WHERE EmployeeNo = 'SomeValue'
ORDER BY TheColumnWheretheSalesDateisStored DESC
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
How can I find records that contain a STRING from some (more than one) other fields ?
for example, I have:
Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"
Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)
How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?
Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)
Thanks in advance, Yovav.You can use like % for sure. Generally when I have searching functions where a user can enter one or more fields
I apppend each field in the SQL string as I build it. So for example mine may look something like the following:
DECLARE @.SQLWHERE varchar(1000)
SET @.SQLWHERE = ''
IF @.FirstName IS NOT NULL
SELECT @.SQLWHERE = ' WHERE FirstName ' + ' LIKE ''' + '%' + @.FirstName + '%'''
IF @.LastName IS NOT NULL
BEGIN
IF @.SQLWHERE = ''
SELECT @.SQLWHERE = @.SQLWHERE + ' WHERE P.LastName ' + ' LIKE ''' + '%' +
@.LastName + '%'''
ELSE
SELECT @.SQLWHERE = @.SQLWHERE + ' AND P.LastName ' + ' LIKE ''' + '%' + @.LastName + '%'''
END
And you can keep doing that type of logic for as many as you would like,
Another option is not to include any "%" at all and then indicate on your textboxes where users search that they can enter a * or % for like searches.
Just food for thought.|||I doing this search on 6 NTEXT fields...
so I thought maybe it will be faster if I do it on one field that has the content of the all 6...
what do U think ?
+
How should I write it ?
"SELECT * FROM X WHERE Key_Words LIKE %'" & SearchString & "'%" ?|||' SELECT * FROM X WHERE FirstName ' + ' LIKE ''' + '%' + @.FirstName + '%'''
.LastName ' + ' LIKE ''' + '%' + @.LastName + '%'''
ANd keep appending for each additional field until you have all six included.
If this is a stored procedure assign your string to variable and then at the end
call exec yourVarialbename
For example
EXEC (@.SQLStatement)
Dear Advance,
I used one stored procedure to retrive 3 different result set. and in the codebehind i seperate it. means from the dataset i seperate three different datatable and then show my data as my need.
but the main problem is ... after retriving the datafrom the database i have to user foreach loop to bind the coulmns data to my different custom class.
example:
foreach (DataRow oDrow in MyDataTable.Rows) {
oClass=new Class();
oClass.Name1=oDrow["Name1"] .toString();
oClass.Name2=oDrow["Name2"] .toString();
...
}
1. so my first question is there any optimization possible ?
2. my result set is too loong ... so should keep just one hit to database or hit more than one time
Currently i am optimizing my web application. in the previous version 1 have to hit the database 3/4 times for different purposes. but now it hits only one time... but it takes time in the codebehind to perform different operation.
Any Suggestion
Starting point: Asynchronous SQL
SqlCommand.BeginExecuteReader()
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref4/html/O_T_System_Data_SqlClient_SqlCommand_BeginExecuteReader.htm
Dear Friends..
I need help if any one can... My need is that...I am devloping my Cube using SSAS. I want to access my cube in multi languages e.g.: English, Japanese or other. At the movement I have two languages description in SQL Database like English and Japanese.
But In SSAS Translations, I can translate Measures Groups, Dimensions, Perspectives, KPIs, Actions, Named Sets & Calculated Members names. But there will be a need to Map Columns as per selected Lenguage so that I can display in proper language data.
Suppose I select Japanese the description should come from Column where Japanese description is stored.
OR any othere method by doing I can see my reports/data in any language.
Please Help...
Do you whant to display the description for the dimension members also translated?
In dimension editor you can specify data source feild for name translation of any dimension attribute. It this what you are looking for?
|||Thanks for your reply.
What I'm understanding here that in dimension editor I will have to set NameColumn for another Lenguage Atrribute in Source. But doing that I will have to maintain two Dimension/Hierarchies for Two Languages e.g.: English and Japanese. I donot want like this. I want only based on lenguage selection data should come for that lenguage without duplicating hierarchies for the languages.
Or if I'm not understanding what you said please clear.
Looking positive response..
|||Hi
I've got the solution in dimension editor in transalations we can do Attribute Data Transalation by clicking ellipsis.Thanks again.
But I've read out regarding Analysis Services Instance for Multi Languages - If you know about please tell me. Or in other words I would like to say any method so that all data in Cube should display in any lenguage based on lenguage selection, but we are not maitaing any manual mapping columns.
Thanks Again.
|||Translations is the way for you to model your cube such that you can later on switch the language and present your users with translated data.
For that you got to specify where Analysis Server shoudl read translated data from.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
Take a look at adventure works sample SSAS database. There you find an example of what you are looking for.
You don't have to maintain two dimensions\hierarchies. You will still have the same dimension with it's hierarchies but depending of LCID of connection you will see captions of dimensions and dimension member in corresponding language.
What you have to do is to supply you dimension tables with additional fields, containing translated names. It's pretty easy, without headache.
|||Need Help
I tried with AdventureWorks db for Analysis Services - Translation. I made a Geography Dimention Attribute translation for Spanish & Franch having columns in DimGeography. It is working fine when browse SQL Browser. Then I made a SSRS Sample Report..given below a detail...
SELECT
NON EMPTY
{[Measures].[Reseller Sales-Sales Amount]} ON 0,
NON EMPTY
{
([Reseller Geography].[Geographies].[State-Province])
*
([Date].[Month Name].[Month Name])
} ON 1
FROM (SELECT(STRTOSET(@.DateMonthName, CONSTRAINED)) ON COLUMNS
FROM (SELECT(STRTOSET(@.ResellerGeographyGeographies, CONSTRAINED)) ON COLUMNS
FROM [Reseller Sales]))
AND DATASET FOR Geography
WITH
MEMBER [Measures].[ParameterCaption] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS ,
{[Reseller Geography].[Geographies].[Country-Region]} ON ROWS
FROM [Reseller Sales]
AND DATASET FOR Date/Month
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Month Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Month Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Month Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Month Name].ALLMEMBERS ON ROWS FROM [Reseller Sales]
Now Report is successfully deployed. But How to change the language. Here I am not sure but what I tried writting....I have opened Report Server and change the setting in Tools/Internet Settings/Lenguages -> and added Franch and move it to 1st place. After this Drop down list labels and it's data start to come in Franch but not same data comming in report itself. What to do.
Please Help.
|||Moving to RS forum
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Dear Friends..
I need help if any one can... My need is that...I am devloping my Cube using SSAS. I want to access my cube in multi languages e.g.: English, Japanese or other. At the movement I have two languages description in SQL Database like English and Japanese.
But In SSAS Translations, I can translate Measures Groups, Dimensions, Perspectives, KPIs, Actions, Named Sets & Calculated Members names. But there will be a need to Map Columns as per selected Lenguage so that I can display in proper language data.
Suppose I select Japanese the description should come from Column where Japanese description is stored.
OR any othere method by doing I can see my reports/data in any language.
Please Help...
Do you whant to display the description for the dimension members also translated?
In dimension editor you can specify data source feild for name translation of any dimension attribute. It this what you are looking for?
|||Thanks for your reply.
What I'm understanding here that in dimension editor I will have to set NameColumn for another Lenguage Atrribute in Source. But doing that I will have to maintain two Dimension/Hierarchies for Two Languages e.g.: English and Japanese. I donot want like this. I want only based on lenguage selection data should come for that lenguage without duplicating hierarchies for the languages.
Or if I'm not understanding what you said please clear.
Looking positive response..
|||Hi
I've got the solution in dimension editor in transalations we can do Attribute Data Transalation by clicking ellipsis.Thanks again.
But I've read out regarding Analysis Services Instance for Multi Languages - If you know about please tell me. Or in other words I would like to say any method so that all data in Cube should display in any lenguage based on lenguage selection, but we are not maitaing any manual mapping columns.
Thanks Again.
|||Translations is the way for you to model your cube such that you can later on switch the language and present your users with translated data.
For that you got to specify where Analysis Server shoudl read translated data from.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
Take a look at adventure works sample SSAS database. There you find an example of what you are looking for.
You don't have to maintain two dimensions\hierarchies. You will still have the same dimension with it's hierarchies but depending of LCID of connection you will see captions of dimensions and dimension member in corresponding language.
What you have to do is to supply you dimension tables with additional fields, containing translated names. It's pretty easy, without headache.
|||Need Help
I tried with AdventureWorks db for Analysis Services - Translation. I made a Geography Dimention Attribute translation for Spanish & Franch having columns in DimGeography. It is working fine when browse SQL Browser. Then I made a SSRS Sample Report..given below a detail...
SELECT
NON EMPTY
{[Measures].[Reseller Sales-Sales Amount]} ON 0,
NON EMPTY
{
([Reseller Geography].[Geographies].[State-Province])
*
([Date].[Month Name].[Month Name])
} ON 1
FROM (SELECT(STRTOSET(@.DateMonthName, CONSTRAINED)) ON COLUMNS
FROM (SELECT(STRTOSET(@.ResellerGeographyGeographies, CONSTRAINED)) ON COLUMNS
FROM [Reseller Sales]))
AND DATASET FOR Geography
WITH
MEMBER [Measures].[ParameterCaption] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS ,
{[Reseller Geography].[Geographies].[Country-Region]} ON ROWS
FROM [Reseller Sales]
AND DATASET FOR Date/Month
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Month Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Month Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Month Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Month Name].ALLMEMBERS ON ROWS FROM [Reseller Sales]
Now Report is successfully deployed. But How to change the language. Here I am not sure but what I tried writting....I have opened Report Server and change the setting in Tools/Internet Settings/Lenguages -> and added Franch and move it to 1st place. After this Drop down list labels and it's data start to come in Franch but not same data comming in report itself. What to do.
Please Help.
|||Moving to RS forum
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
