ItemTypeEnum = Resource question

I have a Resource file that is a htm file in a sub-folder on reporting
services. Using the web service I am getting the catalog items using
ListChildren. I want to be able to extract out the contents of the htm file.
It is stored as a text/html MIMEType. I don't know if this is the correct
forum for this question or not but any assistance is appreciated.
JamesI found the answer myself and will post for reference:
byte[] resourceDefinition = null;
string mimeType = null;
string res = null;
resourceDefinition = rs.GetResourceContents(strReportName, out mimeType);
System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
res = enc.GetString(resourceDefinition);
"James Tow" wrote:
> I have a Resource file that is a htm file in a sub-folder on reporting
> services. Using the web service I am getting the catalog items using
> ListChildren. I want to be able to extract out the contents of the htm file.
> It is stored as a text/html MIMEType. I don't know if this is the correct
> forum for this question or not but any assistance is appreciated.
> James

Items that are filled in

Hello,

AND b.fired_day LIKE (CASE WHEN @.type = 'b' THEN '%' WHEN @.type= 'l' THEN '-1' WHEN @.type= 'o' THEN '0' END)

I want the following thing:

When type= b, then show everything

When type=l, then show the items that aren't filled in

When type=o, then show the items that are filled in

What's wrong with my code? (b.fired_day is a date)

I am assuming the '-1' and '0' clauses are coming from ACCESS.

I think to achieve what you want in SQL server you either need to write a dynamic SQL query or an if-then-else statement like below:

IF @.type = 'b'
BEGIN
select * from TABLE
END

ELSE IF (@.type = 'l')
BEGIN
select * from TABLE
where fired_day IS NULL
END

ELSE
BEGIN
select * from TABLE
where fired_day IS NOT NULL
END

woeter wrote:

Hello,

AND b.fired_day LIKE (CASE WHEN @.type = 'b' THEN '%' WHEN @.type= 'l' THEN '-1' WHEN @.type= 'o' THEN '0' END)

I want the following thing:

When type= b, then show everything

When type=l, then show the items that aren't filled in

When type=o, then show the items that are filled in

What's wrong with my code? (b.fired_day is a date)

|||Thanks for the answer,but isn't there a possibility to wright this in one line? I must use it a couple of times!|||

Well this is the closest to your request I could come.... Let me know if you work out something better:

select * from tblName
where ISNULL(CAST(fired_day as varchar),'-1') LIKE (CASE WHEN @.type = 'b' THEN '%' WHEN @.type = 'l' THEN '-1' END)
OR fired_day LIKE (CASE WHEN @.type = 'o' THEN '%' END)

woeter wrote:

Thanks for the answer,but isn't there a possibility to wright this in one line? I must use it a couple of times!

|||But when the type=b then everything should be shown, also the null fields.|||

Have you tried the code....

the code accomplishes all req...

woeter wrote:

But when the type=b then everything should be shown, also the null fields.

items in list A that dont appear in list B (was "Simple Query...I think")

Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true

In pseudo-SQL, I want to write a clause like this

Code:

IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True


Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.

I may have to achieve this with some kind of logical loop but I don't know how to do that.

Can anyone help?OK so it wasnt' so simple...at least MY solution isn't:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC sp_get_trolley_inconsistencies
@.grower CHAR(2),
@.load_id INT
AS

DECLARE @.ID int,
@.TrolleyList varchar(300),
@.Date DATETIME
--Get the relevant date from the DB Table
SELECT @.Date = (SELECT CONVERT(DATETIME, rl_eta, 102) FROM requi_load WHERE rl_id = @.load_id)

SET @.TrolleyList = ''
--Get the list of values into a comma delimited string
DECLARE crs_Trolleys CURSOR
FOR SELECT DISTINCT ldl_trolley
FROM load_detail_lines
WHERE ldl_requi_load_id = @.load_id

OPEN crs_Trolleys
FETCH NEXT FROM crs_Trolleys INTO @.ID

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.TrolleyList = @.TrolleyList+CAST(@.ID AS varchar(5))+ ', '
FETCH NEXT FROM crs_Trolleys INTO @.ID
END

SET @.TrolleyList = SUBSTRING(@.TrolleyList,1,DATALENGTH(@.TrolleyList)-2)

CLOSE crs_Trolleys
DEALLOCATE crs_Trolleys

--Parse the string and run the 'IN' statement on each of the Parsed Values

DECLARE @.parsingList VARCHAR(300)
DECLARE @.find_comma INT
DECLARE @.trolleytocheck VARCHAR(4)

SELECT @.parsingList = @.TrolleyList

WHILE @.parsingList IS NOT NULL

BEGIN
SELECT @.find_comma = PATINDEX('%,%',@.parsingList)
IF @.find_comma <> 0
BEGIN
SELECT @.trolleytocheck = SUBSTRING(@.parsingList,1,(@.find_comma-1))
SELECT @.parsingList = LTRIM(SUBSTRING(@.parsingList,(@.find_comma+1),300))
PRINT @.trolleytocheck
IF @.trolleytocheck IN(SELECT distinct ldl_trolley
FROM load_detail_lines, requi_load
WHERE ldl_requi_load_id = rl_id
AND ldl_requi_load_id NOT LIKE @.load_id
AND rl_status = 1
AND DAY(rl_eta) = DAY(@.Date)
AND MONTH(rl_eta) = MONTH(@.Date)
AND YEAR(rl_eta) = YEAR(@.Date))
BEGIN
RAISERROR 50001 'Error! You screwed up, trolley '
END
CONTINUE
END
ELSE
BEGIN
--this block finds the last value in the trolley list
SELECT @.trolleytocheck = @.parsingList
SELECT @.parsingList = null
PRINT @.trolleytocheck
BREAK
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If anyone can suggest anything more elegant...please let me know.|||Why not join the tables and count the rows that match?|||Doh!

*Commits ritual suicide*

item master and office stock details (was "Query Problem")

Hi,

I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:

ITEM MASTER
-----
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E

STOCKDETAILS
-----------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3

I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1

--------------
FOR OFFICE - 1
--------------
ITEMCODE ITEMNAME OFFICEID
--------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL

i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.

Thanks in advanceWelcome to the Forum.
Check this...

SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = '1') AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1|||Hi Rudra !

how to get such all result in view and then i will query for officeid to get my desired output.|||Hi r937 !

I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?|||Hi r937 !

I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?
Why don't you use stored proc and pass parameter using OFFICEID ? I think that would be a better way to deal with your problem.But I am not sure what your requirement is...|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1

hmm,always ahead...;)|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?

if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?

my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?

if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?

my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?

I suggest you to use stored proc,its always good to use stored proc
in your case.Just write this...

CREATE PROCEDURE dbo.StockView(
@.officeid VARCHAR(20)

AS

--Use mine or Rudy's one
--this is mine
SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = @.officeid) AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME

--OR use Rudy's one

select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = @.officeid

Go

And check BOL to use Stored proc in dataset .Its very easy man and better to use in many respect
Hope this will help you.sql

Item count in collapsed region

Initially I have collapsed regions on my report with drill-down possibility.
How can I show the number of items that are directly beneath that region?
Something like:
+ Beverages (4)
+ Cigarettes (6)
...I didnt try this, but maybe something like:
=Fields!beverages.Value & " (" &
count(Fields!sub-beverages.Value,"Group-sub-beverages") & ")"
"Marcus Phass" wrote:
> Initially I have collapsed regions on my report with drill-down possibility.
> How can I show the number of items that are directly beneath that region?
> Something like:
> + Beverages (4)
> + Cigarettes (6)
> ...
>
>|||It always says that "the scope parameter must be set to name of containing
group..." error. If I omit the scope, it counts the total number of items
below "Beverages", but I want to display only the number of items in the
level directly below (not all levels below). Countrows gives the same error.
"NI" <NI@.discussions.microsoft.com> wrote in message
news:699E0462-CDC7-4FFA-8C61-2C631672A5F4@.microsoft.com...
> I didnt try this, but maybe something like:
> =Fields!beverages.Value & " (" &
> count(Fields!sub-beverages.Value,"Group-sub-beverages") & ")"
>
> "Marcus Phass" wrote:
> > Initially I have collapsed regions on my report with drill-down
possibility.
> > How can I show the number of items that are directly beneath that
region?
> >
> > Something like:
> >
> > + Beverages (4)
> > + Cigarettes (6)
> > ...
> >
> >
> >|||Try CountDistinct. It worked I tried.
= Fields!Beverages.Value & "(" & CountDistinct(Fields!sub-beverages.Value) &
")"
"Marcus Phass" wrote:
> It always says that "the scope parameter must be set to name of containing
> group..." error. If I omit the scope, it counts the total number of items
> below "Beverages", but I want to display only the number of items in the
> level directly below (not all levels below). Countrows gives the same error.
>
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:699E0462-CDC7-4FFA-8C61-2C631672A5F4@.microsoft.com...
> > I didnt try this, but maybe something like:
> >
> > =Fields!beverages.Value & " (" &
> > count(Fields!sub-beverages.Value,"Group-sub-beverages") & ")"
> >
> >
> >
> > "Marcus Phass" wrote:
> >
> > > Initially I have collapsed regions on my report with drill-down
> possibility.
> > > How can I show the number of items that are directly beneath that
> region?
> > >
> > > Something like:
> > >
> > > + Beverages (4)
> > > + Cigarettes (6)
> > > ...
> > >
> > >
> > >
>
>|||Yep, it works! Thanks!
"NI" <NI@.discussions.microsoft.com> wrote in message
news:23233751-33C6-4AB8-9905-66251FBD7640@.microsoft.com...
> Try CountDistinct. It worked I tried.
> = Fields!Beverages.Value & "(" & CountDistinct(Fields!sub-beverages.Value)
&
> ")"
> "Marcus Phass" wrote:
> > It always says that "the scope parameter must be set to name of
containing
> > group..." error. If I omit the scope, it counts the total number of
items
> > below "Beverages", but I want to display only the number of items in the
> > level directly below (not all levels below). Countrows gives the same
error.
> >
> >
> > "NI" <NI@.discussions.microsoft.com> wrote in message
> > news:699E0462-CDC7-4FFA-8C61-2C631672A5F4@.microsoft.com...
> > > I didnt try this, but maybe something like:
> > >
> > > =Fields!beverages.Value & " (" &
> > > count(Fields!sub-beverages.Value,"Group-sub-beverages") & ")"
> > >
> > >
> > >
> > > "Marcus Phass" wrote:
> > >
> > > > Initially I have collapsed regions on my report with drill-down
> > possibility.
> > > > How can I show the number of items that are directly beneath that
> > region?
> > > >
> > > > Something like:
> > > >
> > > > + Beverages (4)
> > > > + Cigarettes (6)
> > > > ...
> > > >
> > > >
> > > >
> >
> >
> >

Issue with full-text query (containstable)..

I've got a full-text index on a table 'items' with an image field
'item' into which I've imported word and pdf documents. I've only got
~10 docs imported currently, for testing. I know that each of these
documents, except for one, includes the term 'ontology' or
'ontologies'.
I've got the following query, which gives back only two of these
documents (1 word, 1 pdf):
select i.*, ct.rank as incl_ctr
from items i inner join
containstable(items, item,
' ("class*" and "info*") or "ontology" '
) ct on i.id = ct.[key]
where (ct.rank > 1) and i.incl_ext in ('.doc','.pdf')
order by ct.rank desc;
I know this is incomplete, based on the content of each document, which
is an issue I'd like to work out; but prior to that, if I write the
query with "onto*" as a prefix term instead of "ontology", it only
gives back one of the two docs (the pdf):
select i.*, ct.rank as incl_ctr
from items i inner join
containstable(items, item,
' ("class*" and "info*") or "onto*" '
) ct on i.id = ct.[key]
where (ct.rank > 1) and i.incl_ext in ('.doc','.pdf')
order by ct.rank desc;
I can't identify why this is. I'd be very interested in whatever input
I could get on this..
Installed evaluation version: 2000 - 8.00.194 on Windows XP.
Installing the pdf ifilter improves the circumstance described in the
initial post; yet I think it's odd that onto* yields fewer docs in that
query than ontology, which continues to be the case..