|
Hi all
How do i read html content into a sql server report. Or if possible may you please help me with links on where to do this.
Thank you.
|
|
|
|
|
good morning sir/madam,
iam working on app in vb6.0 with msaccess ihave to convert that in oracle db..
here i have msaccess select statement..in which i am autogenerating codenumber..here iam trying to convert to oracle format..
but iam receiving Error:invalid relational operator
rsDetail.Open "SELECT max(to_number(cnor_cnee_id,3,13)) FROM cnor_cnee_master where (cnor_cnee_id,1,2)=" & intBranchPrefix, DBConnection, adOpenStatic, adLockReadOnly
so please suggest the require corrections...
thanx inadvance..
prem...
|
|
|
|
|
premprakashbhati wrote: where (cnor_cnee_id,1,2)=" & intBranchPrefix
What is the condition you're trying use? Field cnor_cnee_id should be what?
premprakashbhati wrote: max(to_number(cnor_cnee_id,3,13))
Also what do you want to do with cnor_cnee_id here?
|
|
|
|
|
Hi,
I have below select statement using MS Access.
SELECT Sum(IIf(Sku_Statistics_File.ExcessSA=0
And Sku_Statistics_File.ExcessSOH=0
And Sku_Statistics_File.StockOutFirst6wks=0
And Sku_Statistics_File.StockOutNext6wks=0
And Sku_Statistics_File.PastDue=0,1,0)) AS BALANCED
FROM SELECTEDPRODUCTS1
INNER JOIN Sku_Statistics_File ON (SELECTEDPRODUCTS1.LOCATION=Sku_Statistics_File.Location) AND (SELECTEDPRODUCTS1.PRODUCT=Sku_Statistics_File.Product);
How to convert to SQL CE? It seems i need to use CASE to replace the IIf, but i donno how to convert. Please help.
Thx.
|
|
|
|
|
Just replace IIF with
CASE WHEN x=1 and a=z ... THEN d=1 ELSE d=2 END
For further details see the BOL help on CASE
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thx for reply.
Is this the way should be done?
I get hitting error at "Sku_Statistics_File.PastDue = 0,1,0 " saying "," is invalid
SELECT Sum(CASE WHEN Sku_Statistics_File.ExcessSA=0
AND Sku_Statistics_File.ExcessSOH=0
AND Sku_Statistics_File.StockOutFirst6wks=0
AND Sku_Statistics_File.StockOutNext6wks=0
AND Sku_Statistics_File.PastDue=0,1,0 THEN 0 ELSE 0 END) AS BALANCED
FROM SELECTEDPRODUCTS1
INNER JOIN Sku_Statistics_File ON (SELECTEDPRODUCTS1.LOCATION=Sku_Statistics_File.Location) AND (SELECTEDPRODUCTS1.PRODUCT=Sku_Statistics_File.Product);
Pls help. Thx.
|
|
|
|
|
No, you have missed the point a bit.
SELECT Sum(CASE WHEN Sku_Statistics_File.ExcessSA=0
AND Sku_Statistics_File.ExcessSOH=0
AND Sku_Statistics_File.StockOutFirst6wks=0
AND Sku_Statistics_File.StockOutNext6wks=0
AND Sku_Statistics_File.PastDue=0) THEN 1 ELSE 0 END) AS BALANCED
FROM SELECTEDPRODUCTS1
INNER JOIN Sku_Statistics_File ON (SELECTEDPRODUCTS1.LOCATION=Sku_Statistics_File.Location) AND (SELECTEDPRODUCTS1.PRODUCT=Sku_Statistics_File.Product);
the THEN is the first condition of your IIF (true), the ELSE is the second condition (false)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hello All,
I have a sql script that pulls certain records by my company's fiscal year which ends 8/31/yyyy. As I am trying to pull records based on the year (yyyy) I have written a condition as such a.THRU_DATE = '8/31/' + Year(GetDate()), however I receive the following syntax error Syntax error converting the varchar value '8/31/' to a column of data type int. Of the course the Thru_Date column type is datetime. Can someone please assist with the proper syntax for such a condition. Thanks in advance to all that reply
|
|
|
|
|
I found the answer to my own question using the CAST function. FYI, a.THRU_DATE = CAST('8/31/' + @year AS DateTime).
|
|
|
|
|
I have to use aggregate function sum() on a table having more than 4 millions of records. It is a history table. Are there any tips to be followed. the queries are a follows
SELECT @TransferIn = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr
and trnstloc = @route and docdate between @FromDate and @ToDate
SELECT @TransferOut = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr
and trxloctn = @route and docdate between @FromDate and @ToDate
SELECT @Sales = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype=6 and itemnmbr = @itemnmbr and
trxloctn = @route
In the above query @route,@FromDate and @ToDate are user inputs
|
|
|
|
|
Dear Uma,
Please give me the table structure with the available indexes.
Senthil
|
|
|
|
|
Dear Senthil,
The table structure is as follows
[TRXSORCE] [char](13)NOT NULL,
[DOCTYPE] [smallint] NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[HSTMODUL] [char](3) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[LNSEQNBR] [numeric](19, 5) NOT NULL,
[UOFM] [char](9) NOT NULL,
[TRXQTY] [numeric](19, 5) NOT NULL,
[UNITCOST] [numeric](19, 5) NOT NULL,
[EXTDCOST] [numeric](19, 5) NOT NULL,
[TRXLOCTN] [char](11) NOT NULL,
[TRNSTLOC] [char](11) NOT NULL,
[TRFQTYTY] [smallint] NOT NULL,
[TRTQTYTY] [smallint] NOT NULL,
[IVIVINDX] [int] NOT NULL,
[IVIVOFIX] [int] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[QTYBSUOM] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] NOT NULL
There are no indexes on this table. I cannot create any indexes as it a table used by ERP named Microsoft Dynamics GP
|
|
|
|
|
Uma Kameswari wrote: I cannot create any indexes as it a table
Then you cant really optimize the table, can you?
|
|
|
|
|
Since you don't have any indexes, you cannot create them and you don't have joins, there's not much you can do. The only thing that comes in mind is that yu copy the data to another table (perhapes in another database), index it and execute the query there. However this isn't a good solution if you need the data to be up-to-date.
|
|
|
|
|
How to transfer database from one system to other system?
|
|
|
|
|
Simply make a database backup and restore.
|
|
|
|
|
you can do one way of these:
1-Backup from source database and Restore on destination, for this option your database server must be same
2-using Import/export tool from Integration service of Sqlserver, for this option database server doesn't matter usually.
|
|
|
|
|
Detach the Database files (.mdf and .ldf).
Move the files physically.
Attach the files to the database.
If previous log is required attach both .mdf and .ldf or else only .mdf can be attached.
|
|
|
|
|
|
Karthik,
Detaching means, removing database files from the server and files will remain in the same place without connecting to server.
Check
sp_detach_db
sp_attach_db
sp_attach_single_file_db
Senthil
|
|
|
|
|
i have 3 tables like this
t1 --->table1
t1Id t1Name
1 jj
2 qq
t2----->table2
t2Id t2Name
1 rr
2 uu
t3---table 3
t3Id tableName
1 t1
2 t2
i need the foolwing result
name
jj
uu
|
|
|
|
|
select t1.t1Name,t2.t2Name<br />
from t1,t2,t3<br />
where t1.t1id=t2.t2id <br />
and t1.t1id=t3.t3id and t2.t2id=t3.t3id
Hope it will hep you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Please give more details about the relationship between those 3 tables.
|
|
|
|
|
Good Morning Guys
I have the Following statement, that works well when i test it alone
declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] 'MasekoTS','MasterDatabase', @Results OUT
But now, i want to include this code in a Stored Procedure and i tried to do it like this
SELECT @sql1 = 'Declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] ''' + @DB + ''', ''' + @Filename + '''@Results OUT'''
execute ( @sql1 )
and i get the Following Error
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'sp_RestoreDatabase_O_Bookin'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_RestoreDatabase_O_Bookin'.
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Instead of doing execute ( @sql1 ) try Print @sql1 to see where its missing a quote - thats my usual method.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|