|
I have create a SQL there r some calculation involve, but i got this error:Divide by zero error encountered.
But there is some which work when i select some others DATE but with some other date which no result came out becasue "Divide by zero error encountered." how can i make the SQL will just continue show those value out even is 0.01... something??
select bizdate,concesscode,con.<br />
<br />
[description],totaltrans,totalqty,totalwgt,totalsales,taxper,<br />
<br />
(totalsales*taxper/100) as TaxAmt,baseamt,level3per as APPLY ,<br />
<br />
(level3per*(totalsales-Taxper/100))as LeaseAmt,(totalsales-baseamt-<br />
<br />
(totalsales*taxper/100)-(level3per*(totalsales-taxper/100)))as <br />
<br />
AmountDue<br />
,discountamt, returnamt,(totalsales-totalcost)*100/totalsales as GP <br />
from tot_item ti, concess con <br />
where con.code=ti.concesscode and ti.bizdate='2006-04-11' and<br />
<br />
concesscode>='A' and concesscode<='D'
|
|
|
|
|
Try adding
" and totalsales<>0"
to your where clause
Chandra
|
|
|
|
|
All,
I have the following table
CREATE TABLE TEST
(
TEST1 binary(32)
)
go
The TEST1 column is a binary with 256 bits.
How can I write T-SQL code to turn an arbitrary bit in TEST1 on or off, or check its state?
You obviously get arithmetic overflow if you try something like:
INSERT INTO TEST VALUES(0)
go
UPDATE TEST SET TEST1 = TEST1 | POWER(2, 137) /* Try to set bit 138 to "on" */
go
"............ Arithmetic overflow occurred ......."
Any ideas?
cheers,
Neil
|
|
|
|
|
It's really hard. If you want to support yes/no type values in SQL, use a bit column. The database - well, SQL Server, at least - stores bit columns by bit manipulation in a field that's just long enough to store all the bit columns in a row. That is, if you have 8 bit columns in a particular table, they will only take one byte of space per row.
|
|
|
|
|
Many thanks for your post.
I thought of using the bit type, but then I'd have to have to 256 columns!
Not ideal, and it would be hard to correlate them as well. And how would I update column 178 in code without some kind of array?
I am looking into using a combination of
SUBSTRING(), STUFF(), CAST(), CONVERT() and ASCII() .. I'm sure it's possible with some work!
I thought someone must have done this .. don't want to reinvent the wheel..
cheers,
Neil
|
|
|
|
|
I have worked out how to do this, so I thought I should post the answer here in case anyone is interested:
INSERT INTO TEST VALUES(0)
/* Turn bit 137 on */
UPDATE TEST SET TEST1 = CONVERT(binary(32), STUFF(TEST1, 137 / 8 + 1, 1, CONVERT(binary(1), SUBSTRING(TEST1, 137 / 8 + 1, 1) | POWER(2, 137 % 8))))
/* Test bit 137 value */
SELECT SUBSTRING(TEST1, 137 / 8 + 1, 1) & POWER(2, 137 % 8) FROM TEST
/* Turn bit 137 off */
UPDATE TEST SET TEST1 = CONVERT(binary(32), STUFF(TEST1, 137 / 8 + 1, 1, CONVERT(binary(1), SUBSTRING(TEST1, 137 / 8 + 1, 1) & ~POWER(2, 137 % 8))))
cheers,
Neil
|
|
|
|
|
The int datatype is 4 bytes.
CREATE TABLE BitTesting<br />
(<br />
TestField int<br />
)<br />
insert into BitTesting (TestField) values (0)<br />
UPDATE BitTesting SET TestField = TestField | 138
| is the bitwise OR operator.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Many thanks for your post.
That idea is fine, but an int has only 4 bytes. That is just 32 bits ... I need a much longer array of bits (256). And I need to be able to set on/off 1 bit at a time... your example of 138 is actually turning on 3 bits:
138 = 128 + 8 + 2 = 10001010
So I am basically running into problems with large numbers....
cheers,
Neil
|
|
|
|
|
Oh...guess I didn't read your initial post carefully enough. Maybe you could just have a separate table with the following columns:
<br />
FKeyID int<br />
<br />
BitLoc tinyint<br />
<br />
BitVal bit
This table could have 256 records for each record in the parent table (BitLoc would be 0 to 255).
Just another idea.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks for that.
That's a neat idea, and one I hadn't thought of. It's more tables to maintain, but it would certainly work. I may just do that .. if I can't find a simple way to hack at a binary(32) directly 
cheers,
Neil
|
|
|
|
|
hai frndz,
i am extracting CSV file.This csv file generated from Informix DB. Do u know the equivalent datatype of Lvarchar(500) in MSSQL2000 and above
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
crazysanker wrote: i am extracting CSV file.This csv file generated from Informix DB. Do u know the equivalent datatype of Lvarchar(500) in MSSQL2000 and above
Well, a quick search on the internet (and I'm curious why you didn't try this yourself) revealed:
The LVARCHAR data type is an SQL data type that you can use to create a column of variable-length character data types that are potentially larger than 255 bytes.
This would suggest a VARCHAR would probably be acceptable. If you need to go larger than 8000 chars then you need to use a TEXT column.
|
|
|
|
|
I have two XML files with two different schemas coming from two different sources that have a common personid field. Is there an easy way to read in the XML sources into something like a DataSet and then display data from each source in one data grid.
Alot of the examples of merging tables etc on the web deal with data being drawn from a database and this C#/ADO.NET noob can't seem to figure out an easier way than creating a third table and populating it with the relevant elements from the original two.
Thanks,
Kevin
|
|
|
|
|
Hi,
I am facing problems with the Sub Query parameter injecting problem. Let me give u the code so tat u can understand the prob
String strSQL = “select d.deptno,e.empname from dept d, (select empname from emp where hdate = : inputDate and jdate= : inputDate) e”;
OralcConnection con = new OracleConnection(conString);
OracleCommand cmd = new OracleCommand(strSQL,con);
OracleDataReader dr;
cmd.CommandType = CommandType.Text;
cmd.Parameter.Add(“:inputDate”,”01/01/2006”);
dr = cmd.ExecuteReader();
but when ever i run this code its saying tat ORA-01008: not all variables bound.
If I add other parameter to the CommandObject with same name and value its working fine. But I need to add only one parameter to this CommandObject.
Please help out
sri
sri
|
|
|
|
|
Try adding the parameter without the colon ...
cmd.Parameter.Add(“inputDate”,”01/01/2006”);
Also, I don't know if it is a formatting issue with your post, but in your query, you appear to have a space between the colon and the variable name.
|
|
|
|
|
Hi Jim,
thanks for your response, i traied that process also but still i its giving the same error. is there any thing i am missing in my code. please help me out.
sri
|
|
|
|
|
Hi,
I got the solution we have to just mention the binding type
dbCmd.BindByName = true;
then it will take only two parameters
sri
|
|
|
|
|
I am working on an application that requires the identification
of items that are selected from a list box on a form so that it
could pass the parameters to a report to be generated on the fly.
The application is in development in MS Access. I have created the
form with a list box that is populated from a table. There is a
command button on the form that submits the form to the report.
The report criteria in the WHERE clause is supposed to have a
parameter that passes the results to the report before it is
generated.
I have successfully been able to retrieve the selected items
from the listbox, but these results are in the command button
on the form. How do I pass these parameters from the control
to the report before the report is generated.
The code in the click even of the command button on the form
with the list box is as follows:
Private Sub Preview_Click()
If IsNull([DueDate]) Then
MsgBox "You must enter a specified Due Date."
DoCmd.GoToControl "DueDate"
Else
Dim frm As Form, ctrl As Control, varItm As Variant
Dim strFormName As String
Set frm = Forms![Report for Dates Enhanced]
strFormName = frm.Name
Set ctrl = frm!lstExcludeViaCodes
For Each varItm In ctrl.ItemsSelected
Debug.Print ctrl.ItemData(varItm)
Next varItm
Me.Visible = False
End If
End Sub
I would like to be able to retrieve the value on the report.
I have tried to use the following code in the criteria for
the WHERE clause in the underlying query that feeds the report:
In ([Forms]![Report for Dates Enhanced]![lstExcludeViaCodes].[ColumnHidden])
The SQL generated then is:
SELECT [Sales-PO Query].[Vendor Name],,,,, [Sales-PO Query].[PO Number]
FROM [Sales-PO Query]
WHERE (([Sales-PO Query].[Sale Deliver Via]) In ([Forms]![Report for Dates Enhanced]![lstExcludeViaCodes].[ColumnHidden]))
ORDER BY [Sales-PO Query].[Profit Center], [Sales-PO Query].[Vendor Name], [Sales-PO Query].[Sale Delivery Date];
The idea is to generate the result for the criteria in the WHERE clause as follows:
WHERE (([Sales-PO Query].[Sale Deliver Via]) In ('SaleDeliverVia1', 'SaleDeliverVia2', 'SaleDeliverVia3')
A really good way to retrieve the selected items from the
listbox on the form might be something like:
Forms![Report for Dates Enhanced]![lstExcludeViaCodes].SelectedItems
... but this property is not available. The only property that is
remotely close is:
Forms![Report for Dates Enhanced]![lstExcludeViaCodes].InSelection
Could somebody who is knowledgeable about MS Access reports
and forms that pass parameters kindly provide some additional
input as to how I could get the listbox selection results
to pass to the query that feeds the report?
To quickly see a sample database as a sample basepoint,
click the menu item > File > New and then select from the
Templates task panel > "On My Computer", and then click the
"Databases" tab. Finally, let Microsoft's wizard create
a database such as "Inventory Control".
Any assistance would be greatly appreciated.
|
|
|
|
|
Hi,
Im not 100% if this will work for you, but when ever I send parameters to Access reports I use report.filter ""
Hope this will work for you
|
|
|
|
|
how to use logical expressions like 'AND' or 'OR' in mdx query? this is
basicly what I´m trying to do:
SELECT CustomerName, ManagerName, ProjectNumber
FROM Cube
WHERE ProjectManager = 'xyz' OR AccountManager = 'xyz' OR Division =
'xxx' OR Division = 'yyy'
can anyone help me with this? I was trying to use the filter condition,
but I don´t really know how to use it correctly, are there any
examples or tutorials about this topic?
cellardoor
|
|
|
|
|
Dear CPians,
I am designing an application that is data centric and will require alot of data migration daily.
In detail, I have a table in SQL Server 2005 that will have to be emptied and then populated with 500 million records twice a day!
Note that the data is provided in TEXT files every 12 hours.
My question is, when should I start considering table clustering? are there any techniques that should I be aware of?
Thanking you in advance.
|
|
|
|
|
Hi,
My problem is that, due to large data in SQL server, my Inventry software is getting to slow while loading data from the server, is there any solution from the database side to increase the performance, or i have to change the entire code of the software, which is going to be the worst thing to do, as i have written the code in VB, I have used distributed Database Access Layer to access the data from the database
|
|
|
|
|
|
Sunil Lanke wrote: my Inventry software is getting to slow while loading data from the server
The first thing to do is check the indexes on your tables.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
By large data do you mean "many records" or "large binary blobs"?
The "many records" issue can often be helped by better indexing, and query reorganization.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
