|
Ok that is very straight forward, pick your primary table A or B as they have a single value for the CID. Use inner joins from A to B and A to C and select the fields you need to display.
If there are missing records in your secondary tables then use left outer joins.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I tried all I could, but still failed to see anyway to directly generate Table C from Table A and B.
I have to generate a new Table D (which looks like Table C - Table A) from my based database tables. Using Table A Union Table D now gives me Table C.
Thanks a lot for your support.
|
|
|
|
|
My bad, I thought table C existed with CID and Credential.
Where do you expect to get the credential for Guarantor from it is not in table A or B in your examples.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
"guarantor" entries under CREDENTIAL column in Table C is just a string input ( like select 'guarantor' as CREDENTIAL) or (isnull(CREDENTIAL, 'guarantor')) to make me identify that entry as details of a particular guarantor.
Since I have gotten a work around for this particular challenge, how do I mark my question as 'Closed' or 'Answered'?
|
|
|
|
|
paul4everyone wrote: how do I mark my question as 'Closed' or 'Answered'?
You don't. Generally if you work it out it is nice to post the solution.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hello !
I have a sql server database that I use with entity framework.
The database have 2 tables :
Customers : ID , Name , city
Orders : ID , sender , receiver
Both sender and receiver are Customers. So I should have 2 relationship for orders with the same table Customers.
Is it safe to use this with sql server and entity framework ?
|
|
|
|
|
Yes but your table design sucks try.
Customer
CustomerID
Name
City
Order
OrderID
CustomerID
Sender
Receiver
This assumes that the customer only ever lives in 1 city and never moves.
Take a look at some of these database structures and see if one will work for you.
http://www.databaseanswers.org/data_models/[^]
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
At least look for an example before asking the question.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hi
when I execute the code in 2012 SQL Server we are getting following error:
Could not find stored procedure 'sp_set_session_context'. Automation Exception: Could not find stored procedure 'sp_set_session_context'
We tested in 2016 server it is working fine but when we moved to 2012 this is failing. when I searched online I come to know that 'sp_set_session_context' it is new future introduced in 2016, I need relevant commands to implement same in 2012 server..
|
|
|
|
|
GaneshVidiyala wrote: I need relevant commands to implement same in 2012 server..
The stored procedure itself uses something that doesn't exist in the older version. So that isn't going to help you.
You are not going to easily replace that.
1. It is just a name/value store HOWEVER it is tied to the transaction.
2. So each transaction you would need to 'create' a corresponding store
3. For each transaction you would need to destroy the corresponding store. If you fail to do this you will eventually have a data problem.
4. Then you can use the store, maybe.
I suspect it is easier to just re-write the functionality. I suspect someone just used it so they wouldn't need to pass data around. So pass the data. And/or untie it from the transaction - just tie it to the processes that use it.
|
|
|
|
|
Hi all
I installed ssrs 2013 and made a report and deploy it inro ssrs server,my source database which I used is oracle and I also have oracle client and .net data providet in the same server that ssrs in installed,
When I want to view published reports on the server the following error is rasied:
An error has occurred during report processing. (rsProcessingAborted)
An attempt has been made to use a data extension 'ORACLE' that is either not registered for this report server or is not supported in this edition of Reporting Services. (rsDataExtensionNotFound)
could anybody help me out of this?
|
|
|
|
|
more than likely your Oracle hosts file is messed up on the server where the oracle client is installed. Check out your tnsnames.ora file and the path variable to make sure it is included in there. Hopefully you already have this resolved before this message.
To err is human to really mess up you need a computer
|
|
|
|
|
The following short Python program is supposed to read data from a SQLite datatable, convert some very large integer values in one field to Doubles, and write the data to an appropriately configured Access Table.
It runs happily with no error, but no data appears in the Access Table. What am I missing?
import sqlite3
import pyodbc
conn1 = sqlite3.connect('data.db')
cur1 = conn1.cursor()
conn2 = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=G:\My_Data_Folder\NewEnergy.accdb;')
cur2 = conn2.cursor()
data=cur2.execute('SELECT * FROM RawValues')
print (cur2.fetchall())
data=cur1.execute('SELECT * FROM RTPValues ORDER BY TimeSerial ASC')
rows=cur1.fetchall()
for row in rows:
cur2.execute('INSERT INTO RawValues (Millitime, PowerCost) VALUES (?,?)',(float(row[0]),row[1]))
conn2.commit
conn2.close
conn1.close
[EDIT]The error, as pointed out by Richard MacCutchan at the end of the thread, is that the conn2.commit call (as well as the subsequent .close calls) are not function calls at all, because they are missing the terminal empty parentheses that are required for Python to treat them as such. The last three lines of the code should be:
conn2.commit()
conn2.close()
conn1.close() As it stands, the program does what it is supposed to do, but the data is never written to the file.[/EDIT]
modified 8-Dec-18 14:51pm.
|
|
|
|
|
The float built-in only takes a single parameter, you are passing two, so that code will fail. Also you have specified two values in your insert statement but you are trying to pass only one.Victor pointed out my mistake.
[edit]
Having looked at the documentation, I guess that the extra parentheses round your values may be the problem. The example is:
cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
So perhaps your code should be:
cur2.execute('INSERT INTO RawValues (Millitime, PowerCost) VALUES (?,?)', float(row[0]), row[1])
[/edit]
modified 6-Dec-18 7:54am.
|
|
|
|
|
Richard MacCutchan wrote: The float built-in only takes a single parameter, you are passing two, so that code will fail. Also you have specified two values in your insert statement but you are trying to pass only one.
Hmmm...
It seems to me there are two values here:
Quote:
cur2.execute('INSERT INTO RawValues (Millitime, PowerCost) VALUES (?,?)',(float(row[0]),row[1]))
The first is Quote: float(row[0])
, the second - Quote: row[1]
|
|
|
|
|
Thanks, my eyesight missed that.
|
|
|
|
|
Richard MacCutchan wrote: Having looked at the documentation, I guess that the extra parentheses round your values may be the problem. The example is:
cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
So perhaps your code should be:
cur2.execute('INSERT INTO RawValues (Millitime, PowerCost) VALUES (?,?)', float(row[0]), row[1])
No, the 'extra' parentheses turn the two values to be passed into a single (2-element) Python Tuple, which is what the call wants passed. If this were the problem, Python should be throwing an error. I have written a fair bit of other code, adding data to SQLite tables, which is exactly parallel and works as it should.
|
|
|
|
|
You will have to do some debugging as it is impossible to guess what is happening on your system. Does the execute command return a value, such as the number of rows inserted?
|
|
|
|
|
It returns a cursor. Fetching the contents of that gets the following:
pyodbc.ProgrammingError: No results. Previous SQL was not a query. That does make a certain amount of sense, given that nothing is happening  , but doesn't really help, particularly since the syntax I am using appears valid and works with Sqlite. I was hoping that someone else had run into and solved this specific problem, since I am out of ideas.
[EDIT] Further research suggests that this 'error' is even less helpful, and may well be the normal result when executing something that is not a SELECT query. [/EDIT]
modified 6-Dec-18 11:41am.
|
|
|
|
|
Sorry, I am out of ideas, and the documentation is not a lot of help. Although a number of similar questions suggest that it should return a row count.
|
|
|
|
|
I have been playing with pyodbc and have successfully added records to my pre-built access database. Unfortunately that does not give any clues as to why your code fails.
[edit]
Created a RawValues table and successfully added a record with 2 values.
[/edit]
modified 8-Dec-18 11:41am.
|
|
|
|
|
Can you post your working Python code, and what version/'bitness' of Access are you running?
|
|
|
|
|
No need, I just discovered I am blind (and maybe you are). The following calls are missing the parentheses that make them into function calls:
conn2.commit
conn2.close
conn1.close
conn2.commit()
conn2.close()
conn1.close()
|
|
|
|
|
|
 I see it happens to the best of us.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
