Challenge revisisted

Hi,
I'm trying to port som old access code to asp.net and the access solution ha
s a couple of queries to sql server that I'm not sure work as expected. I t
hink the idea of the query is to identify if the same event (slaugher of rei
ndeer) is registered in two
different places (field 5 in my last challenge), but two duplicate rows diff
ering only by place does not get selected by the query.
The query is as follows:
SELECT ImportID FROM Temp t WHERE ( EXISTS (
SELECT Year, Code1, Code2, Date FROM (
SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
WHERE ( t.Year = SUBQUERY.Year )
AND ( t.Code1 = SUBQUERY.Code1 )
AND ( t.Code2 = SUBQUERY.Code2 )
AND ( t.Date = SUBQUERY.Date)
GROUP BY Year, Code1, Code2, Date
HAVING (COUNT(*) > 1) ))
Can anyone see if this query does anything useful?, or perhaps give me a que
ry that selects rows with same Year,Code1,Code2,Date, but with different Pla
ce.
The other query is essentially the same, using Zone instead of Place
Morten WennevikOn Mon, 14 Nov 2005 08:51:43 +0100, Morten Wennevik wrote:

>Hi,
>I'm trying to port som old access code to asp.net and the access solution has a cou
ple of queries to sql server that I'm not sure work as expected. I think the idea o
f the query is to identify if the same event (slaugher of reindeer) is registered in
tw
o different places (field 5 in my last challenge), but two duplicate rows differing only by
place does not get selected by the query.
>The query is as follows:
>SELECT ImportID FROM Temp t WHERE ( EXISTS (
> SELECT Year, Code1, Code2, Date FROM (
> SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
> WHERE ( t.Year = SUBQUERY.Year )
> AND ( t.Code1 = SUBQUERY.Code1 )
> AND ( t.Code2 = SUBQUERY.Code2 )
> AND ( t.Date = SUBQUERY.Date)
> GROUP BY Year, Code1, Code2, Date
> HAVING (COUNT(*) > 1) ))
>Can anyone see if this query does anything useful?, or perhaps give me a qu
ery that selects rows with same Year,Code1,Code2,Date, but with different Pl
ace.
>The other query is essentially the same, using Zone instead of Place
>Morten Wennevik
Hi Morten,
I'm not sure what you want eactly. If you want to find year / code1 /
code2 / date combinations for which more than one row exist, use:
SELECT Year, Code1, Code2, Date, COUNT(*)
FROM Temp
GROUP BY Year, Code1, Code2, Date
HAVING COUNT(*) > 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Certain numeric fields not read from the Excel file when using a Excel file source.

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

Try doing a data conversion of this column in the transformation phase using the Derived Column Transformation or the Data Conversion Transformation. Hope that works for you.|||

It has to do with a registry setting. I got a response from some other forum and I tried it and it worked.

|||I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.|||

Al C. wrote:

I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.

Can you set up the Excel connector to import all fields as text fields, and then later convert them to numerics using a derived column transformation?|||

Phil,

TFYR. I verified in the Excel Source (Advanced Editor) that it is using DT_WSTR in both the External columns and Output columns. When I click the Preview button it shows NULL in the first two rows. If I add a DataViewer it also shows nulls. Evidently the first two rows are considered text even though the values are numeric. I can see the difference when I use the formula auditing tool and the first two rows align to the left (indicating text) and the others align to the right (numeric). So what I have gathered is that Excel samples the first nn rows to determine the datatype and since most values are numeric it deems the datatype of the column to be numeric and so it replaces the first two values with nulls. If I modify the Excel connection string and add 'IMEX=1' (import mode=1) to the extended properties, then it works OK.

Certain numeric fields not read from the Excel file when using a Excel file source.

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

Try doing a data conversion of this column in the transformation phase using the Derived Column Transformation or the Data Conversion Transformation. Hope that works for you.|||

It has to do with a registry setting. I got a response from some other forum and I tried it and it worked.

|||I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.|||

Al C. wrote:

I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.

Can you set up the Excel connector to import all fields as text fields, and then later convert them to numerics using a derived column transformation?|||

Phil,

TFYR. I verified in the Excel Source (Advanced Editor) that it is using DT_WSTR in both the External columns and Output columns. When I click the Preview button it shows NULL in the first two rows. If I add a DataViewer it also shows nulls. Evidently the first two rows are considered text even though the values are numeric. I can see the difference when I use the formula auditing tool and the first two rows align to the left (indicating text) and the others align to the right (numeric). So what I have gathered is that Excel samples the first nn rows to determine the datatype and since most values are numeric it deems the datatype of the column to be numeric and so it replaces the first two values with nulls. If I modify the Excel connection string and add 'IMEX=1' (import mode=1) to the extended properties, then it works OK.

Casting...

I've got an ftp task that will be downloading a couple of files each night. today they're called

blah20060830blah

the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.

Thank you

It'd help if you posted your expression and the error that you're getting.

-Jamie

|||Sorry

the expression
(DT_STR) GETDATE()

generates this:
Attempt to parse the expression "(DT_STR) GETDATE()" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

I'm no genius when it comes to making these expressions, and was unable to find a source on the msn site

Thanks again|||

All the source you need is in BOL. And in this case the syntax for the cast is given in the expression editor.

You need:

(DT_STR, 100, 1252)GETDATE()

-Jamie

|||What is BOL?

I put your suggestion into the expression builder, but got the following error

Expression cannot be evaluated.

The expression "(DT_STR, 100, 1252)GETDATE()" has a result type of "DT_STR", which cannot be converted to a supported type.

Do I need to put more in the expression builder?|||

killerless wrote:

What is BOL?

Sorry. Books Online. i.e. The help pages that you get with SQL Server.

killerless wrote:

I put your suggestion into the expression builder, but got the following error

Expression cannot be evaluated.

The expression "(DT_STR, 100, 1252)GETDATE()" has a result type of "DT_STR", which cannot be converted to a supported type.

Do I need to put more in the expression builder?

Hmmm...are you trying to add this as anew column or replace an existing one? If the latter, change it so that you are adding it as a new column.

-Jamie

|||Maybe I'm completely missing the boat here.

in an ftp task, there is a property called "Remote Path" As I understand, this designates what the path and filename of the remote file to be downloaded.

Here's my leap of understanding. I want that value to change daily, I'm assuming that I can define this using an expression. I will concat the parts of the string in the expression builder that don't change daily, to the part that i'm trying to generate right now (which is based on the date)

I am double clicking on the ftp task, setting appropriate properties, then going to the expression section and trying to set the remote path w/ an expression using the expression builder. I'm literally cutting and pasting what you typed in to the expression text area and trying to see if it will evaluate the expression as a string.

As far as I know there are no columns involved. (I will check out the BOL)

Thanks|||

Oh OK. Sorry, I thought you were in a Derived Column Component (which you are not).

Your approach is the corect one. its jsut going to take some debugging that's all.

The Expression Editor itself has some really useful visual aids as to the syntax of all the functions etc... Loom in the box in the top-right of the expressoin editor.

-Jamie

|||I think i'm getting there i will post when i figure this out...|||There's got to be a better way!!!!

DATEPART("weekday", GETDATE()) == 2 ?
"positions_01_CLARENROAD_" + (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -3, getdate() ) ) ) +
(
DatePart("mm", DATEADD( "day", -3, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) )
) +
(
DatePart("dd", DATEADD( "day", -3, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) )
)
:
"positions_01_CLARENROAD_" + (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -1, getdate() ) ) ) +
(
DatePart("mm", DATEADD( "day", -1, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )
) +
(
DatePart("dd", DATEADD( "day", -1, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) )
)
+ "_today.gz.file.asc"

will successfully determine the last day of trading, and afix it to the middle of the string. Can you make temporary variables in these expressions that would probably cut this in half
|||How can I set this value to a variable, so that I can use it in other expressions?|||

killerless wrote:

How can I set this value to a variable, so that I can use it in other expressions?

Good idea!

There is a property of the variable called EvaluateAsExpression. Set this proeprty to false and then paste your expression into the Expression property.

I talk about this a bit here:

Using variables to store expressions
(http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx)

Evaluating variables as expressions
(http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx)

-Jamie

|||AWESOME, thanks a lot, i'm looking forward to the day when the variable expression can be created w/ the expression builder. (I'd settle for a text area a this point)

Is it possible to call a stored procedure? From a expression?

Thanks :)|||so...if the remote variable is now being set w/ an expression, why can't I leave the field blank? Aside from making lost of extra variables is there any advantage to putting these strings in variable derived from my date variable?

Thanks|||

killerless wrote:

AWESOME, thanks a lot, i'm looking forward to the day when the variable expression can be created w/ the expression builder. (I'd settle for a text area a this point)

Install Sp1 and you'll be able to

killerless wrote:

Is it possible to call a stored procedure? From a expression?

No! Sorry.

-Jamie

Casting...

I've got an ftp task that will be downloading a couple of files each night. today they're called

blah20060830blah

the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.

Thank you

It'd help if you posted your expression and the error that you're getting.

-Jamie

|||Sorry

the expression
(DT_STR) GETDATE()

generates this:
Attempt to parse the expression "(DT_STR) GETDATE()" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

I'm no genius when it comes to making these expressions, and was unable to find a source on the msn site

Thanks again|||

All the source you need is in BOL. And in this case the syntax for the cast is given in the expression editor.

You need:

(DT_STR, 100, 1252)GETDATE()

-Jamie

|||What is BOL?

I put your suggestion into the expression builder, but got the following error

Expression cannot be evaluated.

The expression "(DT_STR, 100, 1252)GETDATE()" has a result type of "DT_STR", which cannot be converted to a supported type.

Do I need to put more in the expression builder?|||

killerless wrote:

What is BOL?

Sorry. Books Online. i.e. The help pages that you get with SQL Server.

killerless wrote:

I put your suggestion into the expression builder, but got the following error

Expression cannot be evaluated.

The expression "(DT_STR, 100, 1252)GETDATE()" has a result type of "DT_STR", which cannot be converted to a supported type.

Do I need to put more in the expression builder?

Hmmm...are you trying to add this as anew column or replace an existing one? If the latter, change it so that you are adding it as a new column.

-Jamie

|||Maybe I'm completely missing the boat here.

in an ftp task, there is a property called "Remote Path" As I understand, this designates what the path and filename of the remote file to be downloaded.

Here's my leap of understanding. I want that value to change daily, I'm assuming that I can define this using an expression. I will concat the parts of the string in the expression builder that don't change daily, to the part that i'm trying to generate right now (which is based on the date)

I am double clicking on the ftp task, setting appropriate properties, then going to the expression section and trying to set the remote path w/ an expression using the expression builder. I'm literally cutting and pasting what you typed in to the expression text area and trying to see if it will evaluate the expression as a string.

As far as I know there are no columns involved. (I will check out the BOL)

Thanks|||

Oh OK. Sorry, I thought you were in a Derived Column Component (which you are not).

Your approach is the corect one. its jsut going to take some debugging that's all.

The Expression Editor itself has some really useful visual aids as to the syntax of all the functions etc... Loom in the box in the top-right of the expressoin editor.

-Jamie

|||I think i'm getting there i will post when i figure this out...|||There's got to be a better way!!!!

DATEPART("weekday", GETDATE()) == 2 ?
"positions_01_CLARENROAD_" + (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -3, getdate() ) ) ) +
(
DatePart("mm", DATEADD( "day", -3, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) )
) +
(
DatePart("dd", DATEADD( "day", -3, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) )
)
:
"positions_01_CLARENROAD_" + (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -1, getdate() ) ) ) +
(
DatePart("mm", DATEADD( "day", -1, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )
) +
(
DatePart("dd", DATEADD( "day", -1, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) )
)
+ "_today.gz.file.asc"

will successfully determine the last day of trading, and afix it to the middle of the string. Can you make temporary variables in these expressions that would probably cut this in half
|||How can I set this value to a variable, so that I can use it in other expressions?|||

killerless wrote:

How can I set this value to a variable, so that I can use it in other expressions?

Good idea!

There is a property of the variable called EvaluateAsExpression. Set this proeprty to false and then paste your expression into the Expression property.

I talk about this a bit here:

Using variables to store expressions
(http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx)

Evaluating variables as expressions
(http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx)

-Jamie

|||AWESOME, thanks a lot, i'm looking forward to the day when the variable expression can be created w/ the expression builder. (I'd settle for a text area a this point)

Is it possible to call a stored procedure? From a expression?

Thanks :)|||so...if the remote variable is now being set w/ an expression, why can't I leave the field blank? Aside from making lost of extra variables is there any advantage to putting these strings in variable derived from my date variable?

Thanks|||

killerless wrote:

AWESOME, thanks a lot, i'm looking forward to the day when the variable expression can be created w/ the expression builder. (I'd settle for a text area a this point)

Install Sp1 and you'll be able to

killerless wrote:

Is it possible to call a stored procedure? From a expression?

No! Sorry.

-Jamie