|
I have a Query
select sup_ref_no,mpr_no from qotemst where sup_ref_no ='103006'
Getting O/P like..... sup_ref_no mpr_no
103006 32
103006 32
Good. No problem. Also I have a Query
select mpr_no,mpr_date from mprmst where mpr_no ='32'
Getting O/P like..... mpr_no mpr_date
32 2009-10-15 00:00:00:000
32 2009-10-15 00:00:00:000
Good No problem. Also I have a Query
select sup_ref_no,mpr_no from qotemst where mpr_no ='32'
Getting O/P like..... sup_ref_no mpr_no
1551 32
1551 32
103006 32
103006 32
Good No problem. Also I have a Query
Select q.sup_ref_no,q.item_code,q.mpr_no,m.mpr_date from qotemst q,mprmst m where q.sup_ref_no='103006' and m.mpr_no=q.mpr_no
or
Select q.sup_ref_no,q.item_code,m.mpr_no,m.mpr_date from qotemst q,mprmst m where q.sup_ref_no='103006' and m.mpr_no=q.mpr_no
Getting O/P like.....sup_ref_no item_code mpr_no mpr_date
103006 SQUAWAS1 32 2009-10-15 00:00:00.000
103006 SQUAWAS2 32 2009-10-15 00:00:00.000
103006 SQUAWAS1 32 2009-10-15 00:00:00.000
103006 SQUAWAS2 32 2009-10-15 00:00:00.000
Doubling Problem, So how to solve it...
Any Ideas...Thanks 
|
|
|
|
|
Try the queries like this
a) select distinct(sup_ref_no),mpr_no from qotemst where sup_ref_no ='103006'
b) select distinct(mpr_no),mpr_date from mprmst where mpr_no ='32'
c) select distinct(sup_ref_no),mpr_no from qotemst where mpr_no ='32'
then try the last
Hope it works
Niladri Biswas
|
|
|
|
|
|
You can nest the character replacements. It works by pattern matching so you can only use 1 character at a time. I have not tested with variable for keyword and replacechars but this works
Declare
@Var VARCHAR(100)
SET @Var = 'QWERTYUIOP'
--Replace(@Var, 'Q', 'X') Use this to replace @Var when nesting
SELECT Replace(Replace(Replace(@Var, 'Y', '_'), 'P', 'X'), 'Q', 'X')
|
|
|
|
|
Thanks for the answer but I made the solution using recursive CTE
But a 5 for u always
Niladri Biswas
|
|
|
|
|
please don't delete messages, we now have an answer without the question it belongs to. 
Luc Pattyn [Forum Guidelines] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Sounds like fun. A sort of Code Project Jeopardy.
|
|
|
|
|
hi
i try to install Oracle 10g on Windows 7 - but in the installation i get error:
"the procedure entry point GetProcessImageFileNameW could not be located in the dynamic library PSAPI.DLL. "
thank's for any help
|
|
|
|
|
|
Hi all,
I've started designing a document management system based on SQL Server and vb .net. I find this an interesting project, why? Document management systems have grown to be too complex. What are the main subjects to cover? I want to control the document flow through a product development project, then to the production cycle and maintenance. This means avoiding a catalog structure and always looking for the latest versions of CAD data, bill of materials and so on.
It would be nice to have some feedback, is this a good idea?
Best regards,
OAT
Norway
|
|
|
|
|
Wrong forum, you may get a more attentive audience here[^]. and possibly a more reasonable answer.
|
|
|
|
|
|
Hi i want export a table into database to a excel file
i search s command in Sql Server That help me Export a Table in Excel File without create file manully
please guide me
|
|
|
|
|
hi, the easiest way is
Right Click on the result pane - >Save Result as.. You can save the result in excel.(I am sure for sql server 2005+ but unsure for 2000). Please check that if u r using 2000 version
Niladri Biswas
|
|
|
|
|
|
Look into XP_CmdShell. That would help you achieving this thing. Make sure you enable that from Surface Area configuration.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
|
you can use
1> DTS Export/Import utility for that please go thru
http://support.microsoft.com/kb/319951[^]
2>exec master..xp_cmdshell but for that you need to enable from surface area configuration
3> BCP utility
4> you can use linked server for excel file to sql table.
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\servername\e$\inetpub\wwwroot\game\myexcelfile.xls;',
'SELECT * FROM [scores]') select * from LinkedServerName.DBname.tbl_Stats
5> SSIS package to transfer data from a table in a SQL Server 2005 database to a Microsoft Excel spreadsheet
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
hi every body!
I use Oracle and for connection management I use OLEDB, the problem is where I want to do Update. the Update command is generated by an OleDBDataAdapter as the code below:
Public Sub UpdateData(ByVal oiDataSet As DataSet, ByVal siSQL As String, Optional ByVal siTableName As String = Nothing)
Dim oDataAdapter As New OleDbDataAdapter
Try
If mbPiDisposed = True Then
Throw New ObjectDisposedException(msPiModuleName, "This object has already been disposed. You cannot reuse it.")
End If
oDataAdapter.SelectCommand = New OleDbCommand(siSQL, mcnPiConnection, mtrPiTransaction)
Dim oCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(oDataAdapter)
If siTableName = Nothing Then
oDataAdapter.Update(oiDataSet)
Else
oDataAdapter.Update(oiDataSet, siTableName)
End If
Catch ex As Exception
Throw New Exception(msPiExceptionMessage, ex)
Finally
End Try
End Sub
when I call this Sub (I pass a simple "SELECT * FROM TABLE_NAME" and Updated data as "oiDataSet" to the OleDBDataAdapter as shown above) this error is shown: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"
I searched the Internet But every one said that you may have not define a primary key, but my table has one!!
I should say the INSERT commands are OK, the problem is with UPDATE and DELETE commands.
every suggestion would be appreciated
|
|
|
|
|
At a guess I would say that your select statement does not include the primary key for the table.
As a general recommendation I suggest you learn about Data Access Layer (DAL) and not rely on the adapter auto generation tools, you will be a better developer faster if you understand the tools you are using.
|
|
|
|
|
thank you for reply,
yes I have not the Where Clause in my statement. if it was my choice, yes! as you said, it's the worst way to let the tools do the job!! but this code was a part of an old, big and critical project which my boss prefers not to change the code unless it's necessary!!! so I want to be sure there is not any other way except using "Where" and pass primary keys!! so I can content the boss to change the code!! 
by the way thanks for your kind recommendation,
ANOTHER HELP EQUEST: do you know any other way??;) (my boss is so hard!)
|
|
|
|
|
Sorry, I would look at adding the primary key to the select statement as the minimal intervention required.
Alternatively I would look if there is a combination of existing fields that could be used as a primary key and write my own update method.
How can it be "old and critical" if your update does not work?
|
|
|
|
|
thanks,
about your question: we are moving the data base from SQL Server to Oracle. every thing goes OK by SQL, but this problem is by Oracle 
|
|
|
|
|
Ok then it is the different databases, you may have not set a primary key on the table to be updated in Oracle
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I found where is the problem.
after all discussions, about how can sombody send an Update command to data base!!! the key is here:
Every thing was ok in code and table difinitions. just in connection string a property should be defined: "OLEDB.NET=True"
was easy?? 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
