how to control the number of row?

i want to contorl the output number of row with a certain number when select data from table that not exceed the number with my setting.

For example,if the result is exceed the 500 rows, then i just require 200 rows, that what is the command of this function?

Thanks for any help...

You can try something like:

SELECT top 500
...
from TableName

another more archaic form is to execute

SET ROWCOUNT 500

However, this will also limit the number of records update or inserted, deleted etc. Turn off the limit by:

SET ROWCOUNT 0

The TOP option is in general the better option. Look the two up in books online.

sql

how to concatenate variables for RAW output filename i.e.@path + @filename?

Doesn't appear you can do this.

Am I wrong?

Please tell me I am.you can of course use a third variable (FullFileName), set the property evaluateasexpression to true and set the expression to [User:ath] + [User::filename], then use that

[User::FullFileName] as the variable for the output file name...

|||If you right click on the Raw File Destination and select properties, in the Custom Properties section, change the AccessMode to "File name from variable."

Then, build a third variable that uses expressions to concatenate @.path & @.filename. Then use that variable in the Raw File Destination's FileNameVariable property.|||I can't get this working

@.var1 = c:\temp
@.var2 = output.raw
@.var3 = @.var1 + @.var2 [evaluate as expression set to true]

it won't let me select the raw output filenameVariable to be @.var3 in the raw editor

it says "error at.. the file name property is not valid. the filename is a device or contains invalid characters"

I have also tried @.var1 = c:\\temp

no dice

(and yes, c:\temp does exist)|||

Make sure you set the Value of @.var3 to be a valid fil path. The real value is used for validation purposes. The expression result is only really used at runtime. Any help?

|||The file will not exist until runtime, it is a raw file that gets created.

The file path "C:\temp\" is valid

Bizarrely, if I hardcode the value of c:\temp\filename.raw

It works. (not evaluating the var)

It doesn't seem to like evaluating the concatenation of the the vars.|||

adolf garlic wrote:

I can't get this working

@.var1 = c:\temp
@.var2 = output.raw
@.var3 = @.var1 + @.var2 [evaluate as expression set to true]

it won't let me select the raw output filenameVariable to be @.var3 in the raw editor

it says "error at.. the file name property is not valid. the filename is a device or contains invalid characters"

I have also tried @.var1 = c:\\temp

no dice

(and yes, c:\temp does exist)

@.var1 should be c:\temp\, not c:\temp. Either that, or in your concatenation you must contain the slash there.

You did tell the Raw File adapter to use a filename from a variable, correct?

|||It is C:\temp\

The RFA has access mode set to "File name from variable" which then allows me to select the variable from the drop down by "fileNameVariable"

I notice from another example on the web that it should be evaluating the expression and displaying the result in the 'value' property.

This is not happening for me.

a-ha!

The reason it is not working is because I was putting the @...+@... in the 'value' in the variables window.

I have discovered in the properties window for the variables window (shurely a window on a window too far, yesh?) a field called 'expression' in which I have to put the @.+@. bit.

This doesn't make sense.

Either have a flag called "evaluate as expression" OR have an "expression" property.

What is the point in having both?|||

At first, this seemed counter intuitive to me as well.. but, if you think about it a bit more it makes sense. The expression property will open up the window where you define the expression to evaluate. When you evaluate the expression it's result will go into the value field (try clicking the evaluate expression, it populates the other field). The two are properties are logically not the same and therefore should be seperate.

Are you having any problems with this now? It sounds like you have gotten it working again...

|||Yes, thankyou it is working!

My point is: if there is a value in the 'expression' property, it must be able to detect that it should evaluate the expression.

The reporting services model is much more intuitive. For starters, anything can have an expression which is based on vb code.

So if this was in RS, under the Raw.filename property, you would click on "..." to go straight to the expressions editor where you could pick from @.var1 etc.

Why have "expression","value","evaluate as expression" when you can just have value. (make everything an expression just like RS)

This is another example of something that is not intuitive and overly complex. I have once again wasted a lot of my and other people's time with something in SSIS.

how to concatenate variables for RAW output filename i.e.@path + @filename?

Doesn't appear you can do this.

Am I wrong?

Please tell me I am.you can of course use a third variable (FullFileName), set the property evaluateasexpression to true and set the expression to [User:ath] + [User::filename], then use that

[User::FullFileName] as the variable for the output file name...

|||If you right click on the Raw File Destination and select properties, in the Custom Properties section, change the AccessMode to "File name from variable."

Then, build a third variable that uses expressions to concatenate @.path & @.filename. Then use that variable in the Raw File Destination's FileNameVariable property.|||I can't get this working

@.var1 = c:\temp
@.var2 = output.raw
@.var3 = @.var1 + @.var2 [evaluate as expression set to true]

it won't let me select the raw output filenameVariable to be @.var3 in the raw editor

it says "error at.. the file name property is not valid. the filename is a device or contains invalid characters"

I have also tried @.var1 = c:\\temp

no dice

(and yes, c:\temp does exist)|||

Make sure you set the Value of @.var3 to be a valid fil path. The real value is used for validation purposes. The expression result is only really used at runtime. Any help?

|||The file will not exist until runtime, it is a raw file that gets created.

The file path "C:\temp\" is valid

Bizarrely, if I hardcode the value of c:\temp\filename.raw

It works. (not evaluating the var)

It doesn't seem to like evaluating the concatenation of the the vars.|||

adolf garlic wrote:

I can't get this working

@.var1 = c:\temp
@.var2 = output.raw
@.var3 = @.var1 + @.var2 [evaluate as expression set to true]

it won't let me select the raw output filenameVariable to be @.var3 in the raw editor

it says "error at.. the file name property is not valid. the filename is a device or contains invalid characters"

I have also tried @.var1 = c:\\temp

no dice

(and yes, c:\temp does exist)

@.var1 should be c:\temp\, not c:\temp. Either that, or in your concatenation you must contain the slash there.

You did tell the Raw File adapter to use a filename from a variable, correct?

|||It is C:\temp\

The RFA has access mode set to "File name from variable" which then allows me to select the variable from the drop down by "fileNameVariable"

I notice from another example on the web that it should be evaluating the expression and displaying the result in the 'value' property.

This is not happening for me.

a-ha!

The reason it is not working is because I was putting the @...+@... in the 'value' in the variables window.

I have discovered in the properties window for the variables window (shurely a window on a window too far, yesh?) a field called 'expression' in which I have to put the @.+@. bit.

This doesn't make sense.

Either have a flag called "evaluate as expression" OR have an "expression" property.

What is the point in having both?|||

At first, this seemed counter intuitive to me as well.. but, if you think about it a bit more it makes sense. The expression property will open up the window where you define the expression to evaluate. When you evaluate the expression it's result will go into the value field (try clicking the evaluate expression, it populates the other field). The two are properties are logically not the same and therefore should be seperate.

Are you having any problems with this now? It sounds like you have gotten it working again...

|||Yes, thankyou it is working!

My point is: if there is a value in the 'expression' property, it must be able to detect that it should evaluate the expression.

The reporting services model is much more intuitive. For starters, anything can have an expression which is based on vb code.

So if this was in RS, under the Raw.filename property, you would click on "..." to go straight to the expressions editor where you could pick from @.var1 etc.

Why have "expression","value","evaluate as expression" when you can just have value. (make everything an expression just like RS)

This is another example of something that is not intuitive and overly complex. I have once again wasted a lot of my and other people's time with something in SSIS.

How to combine 3 SQL statements into 1?

I have the following 3 SQL statements that need to be combined, if
possible. The output of one feeds the input of the next. I need to view
all of the defined output fields (the output needs to be used in a
Crystal Report).

The SQL Follows:
/* Input is ISBN (vendor_part_number) */
QUERY_1 - returns 1 record
select p.product_id, v.name, m.description, p.author, p.title,
p.revision_number, p.copyright_edition, p.vendor_part_number,
p.conforming_flag,
m.code, mp.unit_price_product, mm.quota_price
from T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,
T_MULTILIST_PRODUCT mp, T_MULTILIST m,
T_MULTILIST_MEMBERSHIP mm where
/* p.vendor_part_number == input */
p.vendor_part_number = '0153364475' and p.medium_type =
'TEXTBOOK' and
p.product_id = pv.product_id and pv.type = 'CONTRACT' and
pv.vendor_id = v.id and
p.product_id = mp.product_id and
m.code = mp.multilist_code and m.proclamation_year =
mp.proclamation_year
and m.proclamation_seq_id = mp.proclamation_seq_id and
m.code = mm.multilist_code and m.proclamation_year =
mm.proclamation_year
and m.proclamation_seq_id = mm.proclamation_seq_id
/* The above should return a single record */

QUERY_2 - returns 2 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id =
90321
/* 90321 = result from above: pr.parent_product_id = p.product_id
*/

QUERY_3 - returns 18 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id in
(90322, 90323)
/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =
p.product_id */

Only 21 records are returned from these combined queries. I need access
to all of them even though there are 3 different resultsets, 2 of which
contain the same fields. Is there a way to simplify this into a stored
procedure or a view that can take 1 input parameter? It needs to be
used in a Crystal Report, which is limited in its handling of these
types of complex queries.Hi,

Cant you use Derived tables? I havent gone through the queries though.
But generally when I need to query on results from a query, I use a
derived table.

Its something like this...

select * from
(select * from Employee) A

Dont know whether it will work in your case...

Kart

MackTheKnife wrote:

Quote:

Originally Posted by

I have the following 3 SQL statements that need to be combined, if
possible. The output of one feeds the input of the next. I need to view
all of the defined output fields (the output needs to be used in a
Crystal Report).
>
The SQL Follows:
/* Input is ISBN (vendor_part_number) */
QUERY_1 - returns 1 record
select p.product_id, v.name, m.description, p.author, p.title,
p.revision_number, p.copyright_edition, p.vendor_part_number,
p.conforming_flag,
m.code, mp.unit_price_product, mm.quota_price
from T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,
T_MULTILIST_PRODUCT mp, T_MULTILIST m,
T_MULTILIST_MEMBERSHIP mm where
/* p.vendor_part_number == input */
p.vendor_part_number = '0153364475' and p.medium_type =
'TEXTBOOK' and
p.product_id = pv.product_id and pv.type = 'CONTRACT' and
pv.vendor_id = v.id and
p.product_id = mp.product_id and
m.code = mp.multilist_code and m.proclamation_year =
mp.proclamation_year
and m.proclamation_seq_id = mp.proclamation_seq_id and
m.code = mm.multilist_code and m.proclamation_year =
mm.proclamation_year
and m.proclamation_seq_id = mm.proclamation_seq_id
/* The above should return a single record */
>
QUERY_2 - returns 2 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id =
90321
/* 90321 = result from above: pr.parent_product_id = p.product_id
*/
>
QUERY_3 - returns 18 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id in
(90322, 90323)
/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =
p.product_id */
>
>
Only 21 records are returned from these combined queries. I need access
to all of them even though there are 3 different resultsets, 2 of which
contain the same fields. Is there a way to simplify this into a stored
procedure or a view that can take 1 input parameter? It needs to be
used in a Crystal Report, which is limited in its handling of these
types of complex queries.