Query Help

Dear All,
I have a single table name "Remark". It contains the item code, date,
status and remark. If I want to build a query that select items out which
the latest status is still in "pending" for example. How do I make it?
Thanks
Best Rdgs
EllisEllis
SELECT <columns list>
FROM Remarks WHERE[date]=(SELECT TOP 1 [date]
FROM Remarks R WHERE
R.Itemcode=Remarks.Itemcode
ORDER BY [date] DESC)
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:uSSJ$UXbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I have a single table name "Remark". It contains the item code,
date,
> status and remark. If I want to build a query that select items out which
> the latest status is still in "pending" for example. How do I make it?
> Thanks
> Best Rdgs
> Ellis
>sql

Query from Access 2000 won't work in MSDE 2000

I have the following SQL query which works great in MS Access 2000
SELECT DISTINCTROW [tbMenus].[MenuIndex], [tbKeymap].[Key],
[tbItems].[Item], [tbItems].[price], [tbKeymap].[ItemIndex],
[tbItems].[tax1], [tbItems].[tax2] FROM [tbItems] INNER JOIN
([tbMenus] INNER JOIN [tbKeymap] ON [tbMenus].[MenuIndex] =
[tbKeymap].[MenuIndex]) ON [tbItems].[ItemIndex] =
[tbKeymap].[ItemIndex]
This query does not work in MSDE 2000.
I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near '.'.
I have no idea why this is the case since the MDAC is 2.7 (I'm
assuming since this is MSDE 2000).
Any ideas or solutions would be appreciated.
Ali
SQL Server (MSDE) and Access use different dialect of SQL language.
keyword DISTINCTROW does not exist in SQL Server's T-SQL: it is DISTINCT in
SQL Server, while in Access, you have DISTINCTROW and DISTINCT, they do
things slightly different
"Ali Syed" <alijsyed@.hotmail.com> wrote in message
news:26c82868.0408181145.34f0f6e9@.posting.google.c om...
> I have the following SQL query which works great in MS Access 2000
> SELECT DISTINCTROW [tbMenus].[MenuIndex], [tbKeymap].[Key],
> [tbItems].[Item], [tbItems].[price], [tbKeymap].[ItemIndex],
> [tbItems].[tax1], [tbItems].[tax2] FROM [tbItems] INNER JOIN
> ([tbMenus] INNER JOIN [tbKeymap] ON [tbMenus].[MenuIndex] =
> [tbKeymap].[MenuIndex]) ON [tbItems].[ItemIndex] =
> [tbKeymap].[ItemIndex]
>
> This query does not work in MSDE 2000.
> I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
> syntax near '.'.
>
> I have no idea why this is the case since the MDAC is 2.7 (I'm
> assuming since this is MSDE 2000).
>
> Any ideas or solutions would be appreciated.
>
> Ali
|||Thanks Norman that fixed it.
It seems that I will have to compensate for this issue.
Ali

Query dataset locally from code

Hi all,
I need to get 20 rows of data from the SQL Server DB to put in 20 Textboxes
always one item of one row.
It's about Text in various languages, so each language needs 20 textstrings.
I don't want to call the Stored Procedure 20 times.
So I thought in calling it one time, get the data out once of the DB and
refer to it in queries local to the report 20 times.
I tried it with an Assembly. This worked, but I needed to configure the
assembly's trust etc.
This is speaking against a copy/paste deployment to the client's Reporting
Server.
Is it possible to create a standard data set in the Report and refer in some
form to it locally?
E.g.: Dataset dsData takes one time the 20 rows.
In a textbox I call a function like =code.getValue("item17")
and in the code section I query the dsData with something like
function getValue (item as string) as string
return (select col2 from dsData where col1 = item)
end function
Thanks in advance, HenryHello Henry,
I have suggested the following article to you.
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
You do not need to configure the trust level if it not required.
I hope this will be some help.
The Assembly is more functional than the Embeded Code in Reporting Services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
thank you for your answer.
But, as written above, I've already programmed an Assembly that works, but
the client DENIES the use of an Assembly.
I know also, that the <Code> part of the RDL-File is compiled implicitely
into an Assembly by ReportServer on load, so I cant access the outside world
(like the filesystem or the SQL-Server) from within the custom Code without
elevating its trustlevel.
I know also, that it is possible to handover e.g. Parameters to the code to
manipulate it programatically.
Like:
Function GetValue(reportParameters as Parameters) As Object
do sth with the Parameters
return sthelse
end function
The question was:
Since via a Data Source and a DataSet I've already the data available.
CAN I do anything like above with the Parameters, but here with the dataset?
E.g. giving a whole Dataset to a Function (best byRef) to do a select from?
Function GetValue(dsData as Dataset) As Object
return (select sth from dsData where ...)
end function
Thanks in advance, Henry|||Hello Henry,
Unfortunately, you could not refer the dataset in the code.
I would like to know the concern from your client of using the assembly.
You could only grant some proper trust for runing the report.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Wei Lu,
it might be possible to access the file system, as in your
XML-Assembly-Example that you sent me, but getting data out from an
SQL-Server DB is a big trouble.
The info under your hint:
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
is far from sufficient.
Only after lots of hours and the following resources, I got it to work.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
http://forums.microsoft.com/msdn/showpost.aspx?postid=139787&siteid=1
Microsoft Official Course 2840A, Implementing Security for Applications
So, in the very end I needed to give:
- fulltrust via UrlMembershipCondition
Error message: "Cannot generate Hash"
- Give the assembly a strong name
Error message:
Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0,
... failed. (rsRuntimeErrorI-nExpression)
- Adjust CAS
- Assert
System.Data.SqlClient.SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
It didn't work until now!!!
So, worse, I had to install the Assembly in the GAC.
and with this give: AllowPartiallyTrustedCallers()
With our client was agreed a copy/paste-deployment.
All this procedure is FAR from that!
Any ideas?
Thank you, Henry|||Hello Henry,
Yes, the render order is from the upper-left to bottom-right.
I would like to suggest you use a hidden textbox in the report to
initialize.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If I understood you correctly you are passing a predefined number of
parameters to this function. This can also be done dynamically using a
little hack described in this page:
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
I took the code at this page and used to build a dynamic localization
function that reads the text id numbers, language codes and corresponging
texts from a dataset and stores them into a Hashtable object using text id +
language code as the key and text as the value.
Basic idea is that if an aggregate function like Sum is called for a certain
scope, it will go through all of the data in that scope and it can take a
function call that returns a number as a parameter.
Like this:
Sum(Code.LoadDictionary(Fields!TEXT_ID.Value , Fields!LANGUAGE_CODE.Value,
Fields!TEXT_TXT.Value), "DICTIONARY")
Where DICTIONARY is the dataset for dictionary. The LoadDictionary function
is called once for each row in the scope.
You can the have this 'loader' in a hidden field as Mr. Lu suggested.
Then for the localized field you just have for example
=Code.GetLocalizedText("TEXT_0001", Parameters!LanguagePar.Value)
I know that your solution works great. I just think tahat this way the
function calls are simpler. I hope this helps.
Juho Salo

query contains concatenated inner results

I have a table item(id, item_name, item_added_date, item_price).
The second table is item_viewedby(id, item_id, userid).
The second table holds the users who are viewing a certain item.
So data in first table item is like:
id item_name item_added_date item_price
1 toothbrush 01/01/2003 100
2 toothpaste 02/02/2003 125
And data in second table item_viewedby is like:
id item_id userid
1 1 jane
2 1 john
3 1 kim
4 2 jim
5 2 tim
I want to execute a query so that for each item I have a single row of
item_id item_price item_viewed_by
Where item_viewed_by is a semi_colon separated list of users who are
viewing a certain item.
So my result table is like
item_id item_price item_viewed_by
1 100 jane;john;kim
2 125 jim;tim
I am wanting to put this query in a stored procedure and call it from
my ASP.NET code.
If this is to be done using cursors then please help me with that, too
Thanks,
FredSee the following example which will give you a fair idea how can you
convert rows to columns.
Ex:
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'jane')
insert into tab values(1 ,'john')
insert into tab values(1 ,'kim')
insert into tab values(2 ,'nick')
insert into tab values(3 ,'jim')
insert into tab values(3 ,'tim')
go
drop table #tmp
create table #tmp(id int, tmpval varchar(50))
go
declare @.id int, @.old_id int
declare @.seq_num int
declare @.cats varchar(50), @.f_cats varchar(50)
select @.id=0, @.old_id=0,@.seq_num=0, @.cats='', @.f_cats=''
declare c1 cursor for
select id, cats from tab order by id
open c1
fetch c1 into @.id,@.cats
while @.@.fetch_status = 0
begin
If @.old_id <> @.id and @.old_id <> 0
begin
insert into #tmp values(@.old_id, @.f_cats)
select @.seq_num=0, @.f_cats=''
end
select @.f_cats = @.f_cats + case @.f_cats when '' then '' else ',' end + @.cats
select @.old_id = @.id
fetch c1 into @.id,@.cats
end
close c1
deallocate c1
insert into #tmp values(@.old_id, @.f_cats)
select * from #tmp
--
-Vishal
"Fred" <fredg1232003@.yahoo.com> wrote in message
news:2022fcea.0307281714.3529f2f@.posting.google.com...
> I have a table item(id, item_name, item_added_date, item_price).
> The second table is item_viewedby(id, item_id, userid).
> The second table holds the users who are viewing a certain item.
> So data in first table item is like:
> id item_name item_added_date item_price
> 1 toothbrush 01/01/2003 100
> 2 toothpaste 02/02/2003 125
> And data in second table item_viewedby is like:
> id item_id userid
> 1 1 jane
> 2 1 john
> 3 1 kim
> 4 2 jim
> 5 2 tim
> I want to execute a query so that for each item I have a single row of
> item_id item_price item_viewed_by
> Where item_viewed_by is a semi_colon separated list of users who are
> viewing a certain item.
> So my result table is like
> item_id item_price item_viewed_by
> 1 100 jane;john;kim
> 2 125 jim;tim
> I am wanting to put this query in a stored procedure and call it from
> my ASP.NET code.
> If this is to be done using cursors then please help me with that, too
> Thanks,
> Fred|||yes you can review it..........
if you get any issues just inform.
regards
Hari Sharma, India
Wangkhar@.yahoo.com (WangKhar) wrote in message news:<bb269444.0308040106.63f76743@.posting.google.com>...
> hkvats - I took the liberty of reviewing your code - hope you dont mind.|||This type of concatenation over rows is
very easy with the RAC utility for S2k.
No coding required.
Check out:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite released.
www.rac4sql.net