hmm,

You have a row of data set up like this a,b,c,d these are the columns. The
values are a=3, b=1, c=5, d=4 and I need these put in descending order but
this is one row and I need it to come back looking like multiple rows. How
would you suggest I go about this?
Shawntry..
select a as Result from table
union all
select b from table
union all
select c from table
union all
select d from table
order by Result
might be a better way to do this, anyone?
"Shawn Mason" wrote:

> You have a row of data set up like this a,b,c,d these are the columns. Th
e
> values are a=3, b=1, c=5, d=4 and I need these put in descending order but
> this is one row and I need it to come back looking like multiple rows. Ho
w
> would you suggest I go about this?
> Shawn
>
>|||So this is the reverse of the ubiquitous post "How do I combine multiple
rows into one row?":
select a as x from mytable
union all
select b as x from mytable
union all
select c as x from mytable
union all
select d as x from mytable
order by x desc
"Shawn Mason" <shawn@.issda.com> wrote in message
news:OQTuhkTuFHA.3528@.TK2MSFTNGP15.phx.gbl...
> You have a row of data set up like this a,b,c,d these are the columns.
> The values are a=3, b=1, c=5, d=4 and I need these put in descending order
> but this is one row and I need it to come back looking like multiple rows.
> How would you suggest I go about this?
> Shawn
>
>|||Try this:
select a as Result
union
select b
union
select c
union
select d
order by Result
ML|||Sorry, correction:
select a as Result
from <table_name>
union
select b
from <table_name>
union
select c
from <table_name>
union
select d
from <table_name>
order by Result desc
ML|||Have you looked up UNION ALL in BOL?
"Shawn Mason" <shawn@.issda.com> wrote in message
news:OQTuhkTuFHA.3528@.TK2MSFTNGP15.phx.gbl...
> You have a row of data set up like this a,b,c,d these are the columns.
The
> values are a=3, b=1, c=5, d=4 and I need these put in descending order but
> this is one row and I need it to come back looking like multiple rows.
How
> would you suggest I go about this?
> Shawn
>
>|||Please post DDL, sample data, and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006)
Ideally, if each column is the same type of "thing", you would normalize the
table design such that these values *are* in multiple rows.
Barring that, you can do some kind of kludge like this:
SELECT BadColumn FROM
(SELECT ColumnA AS BadColumn FROM BadTable
UNION ALL
SELECT ColumnB AS BadColumn FROM BadTable
UNION ALL
SELECT ColumnC AS BadColumn FROM BadTable
UNION ALL
SELECT ColumnD AS BadColumn FROM BadTable) ReallyBadTable
ORDER BY BadColumn DESC
"Shawn Mason" <shawn@.issda.com> wrote in message
news:OQTuhkTuFHA.3528@.TK2MSFTNGP15.phx.gbl...
> You have a row of data set up like this a,b,c,d these are the columns.
> The values are a=3, b=1, c=5, d=4 and I need these put in descending order
> but this is one row and I need it to come back looking like multiple rows.
> How would you suggest I go about this?
> Shawn
>
>

highly appreciated !

You are the DBA for a busy order entry database. You want to provide a
method for automatically backing up the transaction log for you database in
case it becomes more than 90 percent full prior to the next regularly
scheduled transaction log backup. What is the best way to accomplish this?
1.. Create an alert in Performance Monitor to call an OSQL script to
backup log the transaction log when it becomes more than 90% full.
2.. Create al alert in SQL Enterprise Manager that will call a job to
backup the transaction log when it becomes more than 90% full.
3.. Create an alert in SQL Enterpise Manager that will call an OSQL script
to backup the transaction log when it becomes more than 90% full.
4.. Create an alert in Performance Monitor that will call SQLALRTR.EXE to
trigger a SQL Server alert to backup the transaction log when it becomes
more than 90% full.
5.. Create a schedule transaction log backup for every 10 minutes.
6.. You cannot do this.
"Roy" schrieb:

> You are the DBA for a busy order entry database. You want to provide a
> method for automatically backing up the transaction log for you database in
> case it becomes more than 90 percent full prior to the next regularly
> scheduled transaction log backup. What is the best way to accomplish this?
> 1.. Create an alert in Performance Monitor to call an OSQL script to
> backup log the transaction log when it becomes more than 90% full.
> 2.. Create al alert in SQL Enterprise Manager that will call a job to
> backup the transaction log when it becomes more than 90% full.
> 3.. Create an alert in SQL Enterpise Manager that will call an OSQL script
> to backup the transaction log when it becomes more than 90% full.
> 4.. Create an alert in Performance Monitor that will call SQLALRTR.EXE to
> trigger a SQL Server alert to backup the transaction log when it becomes
> more than 90% full.
> 5.. Create a schedule transaction log backup for every 10 minutes.
> 6.. You cannot do this.
6 is out of question!!!
2 sounds reasonable. Attention: That will not be an event alert (there is no
event / error code for 'log 90% full') but a performance condition alert!

Hierarchial Queries - Order of the data

Hi

I am using SQL Server 2005 Developer edition(Sep 05). I had an oracle hierarchial query the equivalent of which I had written in SQL Server. The problem is the order of the data is different in SQL Server.

To put in proper context :

I need

1. The root node
2. The root’s children and the children of roolt's children and so on and so forth

I get

1. The root node
2. The root’s immediate children
3. The children of the root’s immediate children
4. And so forth.

Find below the DDL, Insert script, the Hierarchial Query, the data expected to return and the actual data returned.

--
-- - DDL Script -
--

CREATE TABLE [sfmfg].[SFPL_MFG_BOM_TEST](

[ITEM_ID] [varchar](40) NOT NULL,

[MFG_BOM_CHG] [varchar](4) NOT NULL,

[PARENT_ITEM_ID] [varchar](40) NOT NULL,

[PARENT_MFG_BOM_CHG] [varchar](4) NOT NULL,

CONSTRAINT [SFPL_MFG_BOM_TEST_PK] PRIMARY KEY CLUSTERED

(

[ITEM_ID] ASC,

[MFG_BOM_CHG] ASC,

[PARENT_ITEM_ID] ASC,

[PARENT_MFG_BOM_CHG] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

--
-- - Insert Script --
--

INSERT INTO SFPL_MFG_BOM_TEST (ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'SE1','A', 'N/A', 'N/A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('MF1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'CYL1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('P1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('TB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BT1','A', 'MF1', 'A')

--
-- - Hierarchial Query -
--

WITH ParentBOM(item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,Level)

AS

(

SELECT item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,1 as Level

FROM sfpl_mfg_bom_test

WHERE item_id = 'SE1'

and mfg_bom_chg = 'A'

and parent_item_id = 'N/A'

and parent_mfg_bom_chg = 'N/A'

UNION ALL

SELECT c.item_id,c.mfg_bom_chg,c.parent_item_id,c.parent_mfg_bom_chg,Level+1

FROM sfpl_mfg_bom_test c INNER JOIN ParentBOM p

ON p.item_id = c.parent_item_id

AND p.mfg_bom_chg = c.parent_mfg_bom_chg

)

Select item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,level

from ParentBOM

--
-- - Expected Data -
--

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

- -- - --SE1 A N/A N/A 1

CYL1 A SE1 A 2

MF1 A SE1 A 2

BB1 A MF1 A 3

BT1 A MF1 A 3

TB1 A MF1 A 3

P1 A SE1 A 2

--
-- - Returned Data -
--

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

- -- - --SE1 A N/A N/A 1

CYL1 A SE1 A 2

MF1 A SE1 A 2

P1 A SE1 A 2

BB1 A MF1 A 3

BT1 A MF1 A 3

TB1 A MF1 A 3
Any help in this matter would be greatly appreciated.

Thanks & Regards
Imtiaz

Found solution...Here's how the query needs to look like....

WITH ParentBOM(item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,Level,SortKey)

AS

(

SELECT item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,1 as Level,

CAST(item_id as varchar(8000))+ CAST(mfg_bom_chg as varchar(8000)) as SortKey

FROM sfpl_mfg_bom_test

WHERE item_id = 'SE1'

and mfg_bom_chg = 'A'

and parent_item_id = 'N/A'

and parent_mfg_bom_chg = 'N/A'

UNION ALL

SELECT c.item_id,c.mfg_bom_chg,c.parent_item_id,c.parent_mfg_bom_chg,Level+1,

CAST(p.SortKey as varchar(8000)) + CAST(c.item_id as varchar(8000))+ CAST(c.mfg_bom_chg as varchar(8000) )

FROM sfpl_mfg_bom_test c INNER JOIN ParentBOM p

ON p.item_id = c.parent_item_id

AND p.mfg_bom_chg = c.parent_mfg_bom_chg

)

Select item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,level

from ParentBOM

order by SortKey

Hiding tables in a report

Hi,

Can anyone help me with the expression I need to use in order to hide a table with no rows? I have put my table inside a rectangle with the idea that I would just hide the rectangle if there weren't any rows but I can't find any examples of what expression I can use to specify the no rows condition.

Thanks!

Debi

Something like for the Hidden property of the rectangle:

=iif(RowCount("NameofDatasetPopulatingTheTabe") > 0, "false", "true")|||

Andy,

Thanks for the posting. RowCount doesn't work for me (unrecognized identifier). That may be because I am using the ReportViewer control in an ASP page and not actually using SQL Server Reporting Services. I probably should have included that in my original posting. Also, if it makes a difference this is a subreport that is using a filter based on a parameter passed from the main report. I'm not sure if that will makes a difference but the datasource will usually have records but a table may not have any rows due to the filter. I want to eliminate the header that gets displayed when there are no rows to show.

Thanks!

Debi

|||

I have made some progress on this. The correct syntax is:

=IIf(CountRows("table1") > 0, false, true)

I added this to the visibility of the table and it does hide the table header if there aren't any rows. The only problem is that it still leaves the white space that would have been occupied by the table header. I had read some tips about creating a rectangle and then moving the table into the rectangle and setting the visibility for the rectangle but I can't get it to work. If I use the same expression I get the following error:

Error 1 The Hidden expression for the rectangle ‘rectangle1’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

It seems that table1 is out of scope in this instance but I can't figure out how the scope should be defined. Any one have any suggestions?

Also, will this even accomplish what I want (eliminating the whitespace)? This rectangle/table is in a subreport if that makes any difference.

Thanks!

Debi

|||You can use the same logic for a rectangle, but your scope parameter needs to be the name of the dataset, not the name of the table.

So if your table uses a dataset like "MyDataSet", then use that instead of "table1". The Rectangle will be aware of that scope.|||

Just thought I would mention that there is also the ability to set a message when a table has no rows. This automatically hides the table and displays your message. Setting the "No Rows" property expression to = "" will also hide the table with no message.

Simone