|
Figured it out myself, I cast the operands themselves to decimals and the product was decimal.
Sorry about that - turned out to be loud thinking than a genuine question.
Shreekar
|
|
|
|
|
It's like buses. You stand around waiting for an answer, and then three come along all at the same time.
|
|
|
|
|
declare @n integer
declare @d decimal(10,2)
select @n = 859
select @d = CAST(@n AS decimal(10,2)) / 5
select @d
Returns 171.80
|
|
|
|
|
in most programming languages, when all numbers are integer, division will truncate. You can promote a (sub)expression to floating-point in several ways: by inserting a floating constant, by having at least one floating variable, by casting, by converting.
As a simple experiment, I would prefix 1.0* to whatever expression you are having.
Warning: this rule also applies to subexpressions, so 2/5 will be zero, whereas 1.0*2/5 would not!
|
|
|
|
|
Multiplying by 1.0 is definitely easier on the eyes than multiple nested cast statements and produces the same output - at least in my case because there is only one operation.
So thanks for that.
Earlier, I had read posts alluding to this technique but I had missed the point - now I get it.
Shreekar
|
|
|
|
|
you're welcome.
|
|
|
|
|
One word of warning with this technique, the following give different results:
859 / 5 * 1.0
1.0 * 859 / 5
If you go down this route, you will need to understand how SQL Server applies its rules for implicit datatype conversion.
|
|
|
|
|
I have a legacy MS Access "application" that uses MS Flex Grid. I've had to create some new forms - copies of existing forms (basically identical except against different tables - same structure different names) but the new forms throw errors on anything to do with the flexgrid. The errors are generally of the form "The expression <<egmouse move="">>you entered as the event property setting produced the following error: There was an error loading an ActiveX control on one of your forms or reports". It only happens on the new copies of forms that contain flex grid. Forms that already existed with flex grid on them still work.
I've unregistered and re-registered flex grid. I've checked through the registry to see if anything is amiss - nothing obvious there. When I look in the Toolbox in Access there are two Microsoft FlexGrid Control V6.0 SP6 shown ... I can't work out why - there aren't two shown in references.
I've tried it on 2 different PCs. I'm using MS Access 2003.
Can anybody help here please? Thanks in advance.
|
|
|
|
|
Don't know whether it applies to MS-Access, but in VB6 you'd have to declare it "WithEvents", somewhat similar to below;
Private WithEvents myWobblyGrid As MSFlexGrid
I are Troll 
|
|
|
|
|
Thanks for your reply ... I don't think that is the issue because the original form (that I copied for the new form) doesn't have a problem. MS Access is just evil ...
|
|
|
|
|
I found the issue - the FlexGrid version on my PC(s) was incorrect. I installed VB6 and patched to SP6 then downloaded the Microsoft Visual Basic 6.0 Service Pack 6 Cumulative Update (see http://support.microsoft.com/kb/957924[^]) Only then did I get the latest version (from March 2009) of FlexGrid.
|
|
|
|
|
Having a strange problem on a legacy system.
In one of the records if the user tries to update it they get the following error
"This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
Options are Save Record (greyed out), Copy to Clipboard, and Drop Changes."
We've tried the follow
1. updating the record manual in SQL Server Management Studio - This works
2. Updating the record in Access, through the link table and we get the above error
if we drop the linked table and rebuild we get same error if we update the table in Access or on the form.
any other suggestions?
Simon
As barmey as a sack of badgers
|
|
|
|
|
Declare @idOrder as nvarchar(10)
set @idOrder = '144'
Declare @sInvoice as nvarchar(50)
set @sInvoice = 'UK_B_IN000051'
Declare @DateRange as nvarchar(100)
set @DateRange = '28Jul2010'
Declare @Country as nvarchar(10)
set @Country = substring(@sInvoice, 1, 2)
Declare @TableName as nvarchar(500)
set @TableName = 'splashdb..Comersus_' + @Country + '_Orders_' + @DateRange
what i want is: select * from @TableName, but sql server returns me error. If someone in here can shed me a light would be really appreciated, or there is an alternative way to achieve the same goal?
|
|
|
|
|
You have to construct a query by combining required variables and pass that to EXEC[^] TSQL function.
Best wishes,
Navaneeth
|
|
|
|
|
Thank very much, i knew the one that you mentioned, but this way is not what i want, coz it is not feasible for me since i have a very long select statement.
|
|
|
|
|
It look to me that your database is a total mess. Why don't you have separate columns for region and date in one and the same table.
Then you could make the query as simple as
Select * from Comersus where sInvoice = 'B_IN000051' and Country = 'UK' and Date = '28Jul2010'
where Date naturally shouldn't be a string but an actual Date
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Mate, first of all, thanks for your reply, but if i can go the way that you mentioned, i wouldn't jump on internet, coz be realistic, a perfect database is a db that has no table. Hope you got my drift.
|
|
|
|
|
Well, I am curious why you need to have your database modeled that way.
The best reason I can think of at the moment is: "It's always been like that and now it's to much work to change"
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
No matter how you work it you are not going to get what you want.
Select * from @TableName
The sames as this will not work
Select @ColumnName from Table
You are going to have to construct a string and execute it, it is called dynamic SQL and this is one of the few reason it is a valid solution.
BTW
I agree that your db looks ugly (I presume you are aware of that). Have you looked into partitioning, as I assume you have split the orders by country/date for performance reasons.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks All for your help, and i will find an alternative way to do it then.
|
|
|
|
|
hello
you can use the exec function.
like this:
exec('select * from ' + @TableName)
that execute a query and return the result set
good luck
|
|
|
|
|
your alternatives are limited
1. create seperate SQL queries for all possibilities
2. Create an external application to overcome it
3. use dynamic SQL, Erlands Dynamic SQL article
others might chip in other alternatives for you
As barmey as a sack of badgers
|
|
|
|
|
You will have to use dynamic queries or a series of if/else blocks to do this. Dynamic queries are not a good way to do things. If/else, will look ugly if you have even 10 of them. So, either live with dynamic queries or redesign things.
|
|
|
|
|
Please look into below MDX query it is resulting in system.outofmemoryexception when i try to run in MDX query analyzer
Details
MRCODE has 1797 rows
DIV has 6 rows
STKCD has 4434 rows
Product Name has 507 rows
when crossjoin this .....it is giving problems..
Please let me know in case any modifications on it i cannot try NONEMPTYCROSSJOIN on it
SELECT ALL ( Hierarchize(
CrossJoin([Dim SM].[MRCODE].[MRCODE].members,
CrossJoin([Dim SM].[DIV].[DIV].members,
CrossJoin(
[Dim SM].[STKCD].[STKCD].members,
[D Im Prod].[Product Name].members
)))))
ON ROWS,[Measures].[QTYCHRG]ON COLUMNS from [ABCDW2]
thanks in advance
|
|
|
|
|
Hi All,
I'm trying to create a table that has one primary key with multiple foreign keys which reference different tables. The following is how I declared my table:
CREATE TABLE COURSE_SECTION
(
Csecid NUMBER(8),
Secnum NUMBER(2) NOT NULL,
Maxenrl NUMBER(4) NOT NULL,
Currenrl NUMBER(4) NOT NULL,
Cid NUMBER(6) NOT NULL,
Termid NUMBER(5) NOT NULL,
Day VARCHAR2(10),
Locid NUMBER(5),
Fid NUMBER(4),
primary Key (Csecid),
foreign Key (Cid) references COURSE(Cid),
foreign Key (Termid) references TERM(Termid),
foreign Key (Locid) references LOCATION(Locid),
foreign Key (Fid) references FACULTY(Fid)
);
I've receive the error message "unknown command beginning ........ - rest of line ignored" for many of the lines when I execute the above script in Oracle SQL Plus. I have tried many different things like rewording the lines or capitalize the letters of the attributes but my attempts were futile. Can you please point out what I have done wrong? Thank you in advance for your help.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
