help exporting data to Oracle

hi,
i'm trying to export data from Sql Server to Oracle, when i try i get
a message saying that the values in a column are too large, in sql
server the column is of type ntext length 16, in Oracle its long, is
there something i can do? if i need to change Oracle's long to
something else, what should i make it blob?
Thanks.I am not an Oracle guy, but the name 'long' sounds like a numeric datatype,
not textual. I think BLOB will be more appropriate for large text data.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"soni29" <soni29@.hotmail.com> wrote in message
news:cad7a075.0402181427.31bcffc4@.posting.google.com...
hi,
i'm trying to export data from Sql Server to Oracle, when i try i get
a message saying that the values in a column are too large, in sql
server the column is of type ntext length 16, in Oracle its long, is
there something i can do? if i need to change Oracle's long to
something else, what should i make it blob?
Thanks.

help exporting data to Oracle

hi,
i'm trying to export data from Sql Server to Oracle, when i try i get
a message saying that the values in a column are too large, in sql
server the column is of type ntext length 16, in Oracle its long, is
there something i can do? if i need to change Oracle's long to
something else, what should i make it blob?
Thanks.I am not an Oracle guy, but the name 'long' sounds like a numeric datatype,
not textual. I think BLOB will be more appropriate for large text data.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"soni29" <soni29@.hotmail.com> wrote in message
news:cad7a075.0402181427.31bcffc4@.posting.google.com...
hi,
i'm trying to export data from Sql Server to Oracle, when i try i get
a message saying that the values in a column are too large, in sql
server the column is of type ntext length 16, in Oracle its long, is
there something i can do? if i need to change Oracle's long to
something else, what should i make it blob?
Thanks.sql

HELP : empty string to Null conversion by SQL

Hi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHNHi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHN

Dunno how you're going to do it without naming column names.

UPDATE MyTable
SET Col1 = Null, Col2 = Null, Col3 = Null
WHERE [insert where clause here]

Note that this will set Col1, Col2 and Col3 to Null; not just where the are equal to an empty string, but where there are values as well.

You may consider the CASE statement to selectively change blank values to NULL.

Regards,

hmscott|||As an alternate consideration, you might try:

SELECT
'UPDATE [' + TABLE_NAME + '] SET [' + COLUMN_NAME + '] = NULL
WHERE [' + COLUMN_NAME + '] = '''
FROM INFORMATION_SCHEMA.columns
WHERE DATA_TYPE IN ('char','nchar','varchar','nvarchar')

This will give you a bunch of individualized UPDATE statements. You would then have to run each statement seperately.

Regards,

hmscott|||Ok, but please use:
set [YourColumn] = NULLIF([YourColumn], '')
...or you will wipe out all your data.

HELP ... select statment

Below is the query

It returnes the amount of products, the serial no and quanity

what i need to display in the results is alias column 'qty' only to display items

greater than 0

select name
, serialno
, sum(saleslead_product.quantity) as qty

from company
inner join saleslead on company.id = saleslead.company_id
inner join saleslead_product on saleslead.id = saleslead_product.saleslead_id
inner join product on saleslead_product.product_id = product.id

where name like 'test'

group by name
, serialno

You could add "HAVING sum(saleslead_product.quantity) > 0" at the end.|||

thanks.... been staring at the screen to long!!

Help ! How do i refer to the deleted table into a trigger statement ?

Hi there,

Could somebody post some simple example how one can refer to
a column in the 'deleted' temporary table within a trigger definition ?
Should the 'deleted' and 'inserted' temp tables be declared, or they are
'implied' ?

I keep having this msg when trying to create a trigger
"The column prefix 'deleted' does not match with a table name or alias name
used in the query"

Thanks for your inputs,
Eddy.[posted and mailed, please reply in news]

Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Could somebody post some simple example how one can refer to
> a column in the 'deleted' temporary table within a trigger definition ?
> Should the 'deleted' and 'inserted' temp tables be declared, or they are
> 'implied' ?

They are implied. You just use them.

> I keep having this msg when trying to create a trigger
> "The column prefix 'deleted' does not match with a table name or alias
> name used in the query"

May I guess that you are using dynamic SQL? Code executed in EXEC() or
sp_executesql is not part of the trigger, but constitutes a scope on
its own.

A practical workaround is to say:

SELECT * INTO #deleted FROM deleted
SELECT * INTO #inserted FROM inserted

first in the trigger.

This can even be useful in a trigger that does not use dynamic SQL,
particular if multi-row operations are common, because you can get
better performance. The virtual tables inserted and deleted are
not very efficient when they get some size.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Thanks Erland,
> I was trying to refer to a column in the deleted temp table with
> 'deleted.column_name'...
> My sql server does not like this !

Since you never posted the code to the trigger that gave you the
error message, I cannot say what your problem really was. Judging from
the code you post now, may you tried with:

> UPDATE DemographicData SET Lastupdate = getdate()
> WHERE ID = deleted.ID

which is not legal, but that has nothing to do with inserted/deleted,
but with the fact that you must name a table in a query in a FROM
clause or after INSERT/DELETE/UPDATE.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help - Unable to Add Column to Report

I have inherited a report that appears to be too big for the VS designer.
The query is 664 lines long and every time I move to the Data tab, Reporting
Services locks up and stops responding. (I am using the generic query
designer)
The report runs fine, my problem is I need to add another data column to the
report. I did this by editing the RDL in a text editor but when I reload in
RS and got to the Layout tab to add the column to the report, it is not
listed in the Fields list. I tried to manually add it but I then get the
'Index out of bounds error'. So it seems the field list needs to be updated
by accessing the Data tab and then moving to the Layout or Preview tab but
every time I try to do this it locks up.
I thought I might try to temporarily remove part of the query so the field
list could be updated but the report has the columns scattered throughout so
I can't locate a large enough block of contiguous query data that does NOT
affect the fields list.
Is there any work around for this?
Many thanksHi Mike,
I'm not sure what the preview issue is, but if you are ok with editing the
RDL, you should be able to do it.
When you added the field to the RDL, did you add it to both the query and to
the fields collection?
The fields collection maps results returned from the query to fields you can
put on the report. If you added a column to the query in the RDL, makes
sure you add a field element (under datasets/fields) that matches the column
name. If you did both of those and still have the problem then I don't
know.
I hope that helps!
Chris
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OO$4Noz3FHA.4080@.tk2msftngp13.phx.gbl...
>I have inherited a report that appears to be too big for the VS designer.
>The query is 664 lines long and every time I move to the Data tab,
>Reporting Services locks up and stops responding. (I am using the generic
>query designer)
> The report runs fine, my problem is I need to add another data column to
> the report. I did this by editing the RDL in a text editor but when I
> reload in RS and got to the Layout tab to add the column to the report, it
> is not listed in the Fields list. I tried to manually add it but I then
> get the 'Index out of bounds error'. So it seems the field list needs to
> be updated by accessing the Data tab and then moving to the Layout or
> Preview tab but every time I try to do this it locks up.
> I thought I might try to temporarily remove part of the query so the field
> list could be updated but the report has the columns scattered throughout
> so I can't locate a large enough block of contiguous query data that does
> NOT affect the fields list.
> Is there any work around for this?
> Many thanks
>

HELP - need a function like MID in access

I am looking for a function that behaves like the MID function in access. I need to return all values in a column that has a specific charater in a specific location within the string. Example: I need to return all rows that have the number 2 in the fourth position of column TEXT.

Quote:

Originally Posted by tingirl76

I am looking for a function that behaves like the MID function in access. I need to return all values in a column that has a specific charater in a specific location within the string. Example: I need to return all rows that have the number 2 in the fourth position of column TEXT.

Nevermind I got it...SUBSTRING(expression, startposition, length)....thank you anyway!!!

HELP - I NEED EXTRA MATRIX COLUMNS

I need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.

I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?

This is an example of how I want my report to look (I can already achieve the bits highlighted bold)

Daily Data Commission (GroupData cell 1) Interest (GroupData CELL 2) GroupData etc … SUBTOTAL (DTD) MTD TOTAL YTD TOTAL Book Currency =sum(Fields!DTD.VALUE) ........ =sum(Fields!MTD.VALUE) =sum(Fields!YTD.VALUE)

and this is an example of the data being returned by my Sproc

Book CCY GroupedData DTD MTD YTD ABC GBP Commission ￡0.01 ￡0.09 -￡0.10 ABC GBP Interest ￡0.02 ￡0.29 ￡0.11 ABC GBP Brokerage ￡0.12 ￡0.06 ￡0.20

When I use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.

Daily Data Grouped Data CELL 1 Grouped Data CELL 2 Book Name Currency DTD TOTAL MTD TOTAL YTD TOTAL DTD TOTAL MTD TOTAL YTD TOTAL =sum(Fields!DTD.VALUE)

Does anyone out there know how to do it?

This SUCKS.... I cant believe no one knows this and there are no Microsoft developers monitoring this thread to answer the questions.

|||

Hi there,

Did you try doing subtotals and totals within SQL Server instead of in the matrix? This should get the extra columns you would need.

Alternatively you could create a matrix next to the matrix that may work.

Or see this posting.

http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx

cheers,

Andrew

|||

Thanks Andrew -

For anyone who is reading this thread that isn't sure where the Inscope expression goes (like me) it is in the

"Data" bit of the matrix (where you have your =Sum(field!...) bit)

The only thing now is - how do I sort the positioning out. My design layout looks like below - I have a DTD BreakDown and Total (I have used the <position> element in the XML code behind to add "DTD Total" position to the front) - I can get the MTD to be on the same level as the DTD Parent Cell as needed - however The YTD subtotal is one level higher (I tried messing about with alignment - which sorts the problem temp - but on expanding the DTD breakdown the alignment doesnt work)

How can I make the YTD (Text) appear on the same level as DTD and MTD?

YTD DTD MTD Fields!DTDBreakDown DTD Total Field!Book.Value Field!CCY.Value iif(InScope("YTD"),
iif(InScope("MTD"),
Sum(Fields!AmountCCY_DTD.Value),
sum(Fields!AmountCCY_MTD.Value)),
sum(Fields!AmountCCY_YTD.Value))

e.g of how my report looks - I need the YTD to be on the same level as DTD and MTD

YTD (+)DTD MTD DTD Total Book 1 CCY 0 0 0

|||This is not possible in the Matrix report. You need to return you column from the SP or TSQL that you are using and instead of using Matrix create this report from using table report.|||

Are you sure? According to the example given in the URL on Andrew's Post(http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx) says you can. However, its just doesn't explain how.

I need to use a matrix as the DTD breakdown is dynamic - and I'm almost there, the problem I have is cosmetic.

|||If you want to create the report as given in above URL then you can do it. Are you want to do like that ? and steps are clearly added there. Any other problem in that then let me know.

help

Can anybody please help me with this problem
1. I have a table with the following column :
code Date Time vol exptime elapse
4 20010424 104232 945 40165
My problem is none of these column can be my primary key so my question is
How can I assign another column say 'transact_id' which will have an incremental and unique number? In other word my objective are:
a. I would like to have the data sorted according to code, date, time
b. I would like to assign a column which consist of unique and incremental number e.g. for code 1, first date and first time the 'transact_id' column will be = 1; for code 1, first date and second time the 'transact_id' column will be = 2;and so on

2. I would like to calculate the column elapse as = 'exptime(t) - exptime (t-1)'. In other words, the elapse is the difference between exptime of the current collumn with the previous one? Can you create a query to calculate this?Can the date and time together be your primary key? SQL Server will store them as a single column anyway, so this might make sense.

-PatP|||Can the date and time together be your primary key? SQL Server will store them as a single column anyway, so this might make sense.

-PatP|||Yes I think I can add another column which is the combination of date and time so each column will look like this yyyymmddhhmmss--> e.g. 20010101102010 which is 1 Jan 2001 10:20:10|||It would really help if you could post the DDL (probably the CREATE TABLE statement) for your table. Otherwise we need to guess at too much. Yes, it is quite possible to create a query like what you want.

-PatP|||I have attached the sample file
for you convenience I have selected random sample of less than 100 observation
thanks in advance for your help|||a. I would like to have the data sorted according to code, date, time you don't need a primary key to do that

b. I would like to assign a column which consist of unique and incremental number e.g. for code 1, first date and first time the 'transact_id' column will be = 1; for code 1, first date and second time the 'transact_id' column will be = 2;and so on you could add an IDENTITY column to the table, but you don't need to

2. I would like to calculate the column elapse as = 'exptime(t) - exptime (t-1)'. In other words, the elapse is the difference between exptime of the current collumn with the previous one? Can you create a query to calculate this?yes, i can

by "previous" you mean the row with the highest date and time that is less than the current row, for the same code, right? or do you regard all codes identically when it comes to sequencing by date and time?

clarifying exactly what you want is important to the eventual sql

by the way, if you can, you should replace the date and time columns with one datetime column|||by the way, if you can, you should replace the date and time columns with one datetime column
Yes I have done that see this attachment

by "previous" you mean the row with the highest date and time that is less than the current row, for the same code, right? or do you regard all codes identically when it comes to sequencing by date and time?

I'm not really sure whether i understand your question however, here's my explanation: The code is a stock code so I arrange the data to be sorted according to code, date and time and I would like to calculate the elapse
as the difference between exptime for the current row and the previous row using macro in excel the langguange will look like this
R1C1 = "=RC[-1]-R[-1]C[-1]"

please tell me if you think you need more explanation|||by the way, if you can, you should replace the date and time columns with one datetime column
Yes I have done that see this attachmentif you meant to attach a new description, you forgot ;)

i will use your first description:select t1.code
, t1.[Date]
, t1.[Time]
, t1.vol
, t1.exptime
, t1.exptime
-t2.exptime as diff
from currprev as t1
left outer
join currprev as t2
on t1.code = t2.code
and cast(t2.[Date] as char(8))
+cast(t2.[Time] as char(6))
= (
select max(
cast([Date] as char(8))
+cast([Time] as char(6))
)
from currprev
where (
[Date] < t1.[Date]
or [Date] = t1.[Date]
and [Time] < t1.[Time]
)
)
order by 1,2,3
the results of this are: code Date Time exptime elapse
5 20010102 100921 36561
5 20010102 104046 38446 1885
5 20010102 132221 48141 9695
5 20010102 132518 48318 177
5 20010103 102123 37283 -11035
5 20010103 120312 43392 6109
5 20010103 122434 44674 1282
5 20010103 150953 54593 9919
5 20010103 150953 54593 9919
5 20010103 151918 55158 565
5 20010103 151918 55158 565
5 20010104 101123 36683 -18475
5 20010104 121213 43933 7250
5 20010104 144338 53018 9085
5 20010104 145634 53794 776
5 20010104 153809 56289 2495
5 20010105 123717 45437 -10852
5 20010105 132814 48494 3057
5 20010125 112752 41272 -7222
5 20010125 113146 41506 234
5 20010125 113146 41506 234
5 20010125 113146 41506 234
5 20010125 113653 41813 307
5 20010125 113653 41813 307
5 20010125 113653 41813 307
5 20010125 114443 42283 470
5 20010125 114550 42350 67
5 20010125 114756 42476 126
5 20010125 114756 42476 126
5 20010125 114905 42545 69
5 20010125 114905 42545 69
5 20010125 115235 42755 210
5 20010125 121430 44070 1315
5 20010125 123000 45000 930
329 20010424 104232 38552
329 20010424 104806 38886 334
329 20010424 104806 38886 334
329 20010424 104806 38886 334
329 20010424 104940 38980 94
329 20010424 104940 38980 94
329 20010424 104940 38980 94
329 20010424 110925 40165 1185
329 20010424 110925 40165 1185
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112216 40936 20
329 20010424 112216 40936 20
329 20010424 112216 40936 20
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120627 43587 4
329 20010424 121024 43824 237
329 20010521 104239 38559 -5265
et cetera
having an IDENTITY primary key would have helped immensely

your data has dupes in it, and consequently the output reflects this, because the dupes are cross-joined within groups of the same code/[Date]/[Time]

i strongly urge you to clean up the data before proceeding|||Thanks very much, (sorry about the attachment)
However I can not delete the duplicates since it is a vaild observations
Do you have any suggestion about creating an IDENTITY primary key ?

help

I have two temp table name #table1 and #table
when I run the following update, I get an error
" Cannot use the column prefix 't'. This must match the object in the UPDATE clause '#test1'.
update #test
set t.name=tt.nam
from #test1 t, #test2 t
where t.last=tt.las
This in SQL 2000. Why would this happend
Thanks
Jasontry this
update t
set t.name=tt.name
from #test1 t, #test2 tt
where t.last=tt.last
>--Original Message--
>I have two temp table name #table1 and #table2
>when I run the following update, I get an error
>" Cannot use the column prefix 't'. This must match the
object in the UPDATE clause '#test1'. "
>update #test1
>set t.name=tt.name
>from #test1 t, #test2 tt
>where t.last=tt.last
>
>This in SQL 2000. Why would this happend.
>Thanks,
>Jason
>.
>|||Or, better still,
UPDATE
t
SET
t.name = tt.name
FROM
#test1 AS t
INNER JOIN #test2 AS tt ON
tt.last = t.last
James Hokes
> update t
> set t.name=tt.name
> from #test1 t, #test2 tt
> where t.last=tt.last
"Scott Dexter" <anonymous@.discussions.microsoft.com> wrote in message
news:033501c3c5ad$e9b73d20$a401280a@.phx.gbl...
> try this
> update t
> set t.name=tt.name
> from #test1 t, #test2 tt
> where t.last=tt.last
>
>
> >--Original Message--
> >I have two temp table name #table1 and #table2
> >
> >when I run the following update, I get an error
> >" Cannot use the column prefix 't'. This must match the
> object in the UPDATE clause '#test1'. "
> >
> >update #test1
> >set t.name=tt.name
> >from #test1 t, #test2 tt
> >where t.last=tt.last
> >
> >
> >This in SQL 2000. Why would this happend.
> >
> >Thanks,
> >Jason
> >
> >.
> >