|
Now the GlobalEntityID is a seperate table that holds the company demographics. So each company can have multiple license attached to company. They can also have multiple invoice attached to company.
My question is the end user wants a report that shows company info, license number and invoice attached to each license number. But It does not work cause the invoice is attache to the company not each individual license number.
Is it possible to create get data for each license number when invoices are related to the company table only and not the license number. The report needs to show license numbers with or with out an invoice? When I run the report in SQL Query I can see same invoices for all license numbers, but in the user application I can pull a license number up and see that there is no invoice attached to it. How is this possible when there is no relationship between one license to one invoice?
License Table Fields Invoice Table Fields
PK LICENSEID PK INVOICEID
LICENSENUMBER CASTATUSID
ISSUEDBY INVOICENUMBER
LICENSETYPEID GLOBALENTITYID
LICENSESTATUSID INVOICETOTAL
ISSUEDATE INVOICEDATE
EXPIRATIONDATE INVOICEDESCRIPTION
LASTCHANGEDON ROWVERSION
LASTCHANGEDBY LASTCHANGEDON
ROWVERSION LASTCHANGEDBY
GLOBALENTITYID INVOICEDUEDATE
LICNESEPARENTID
DISTRICTID
LICENSECLASSIFICATIONID
APPLIEDDATE
LASTRENEWALDATE
LICENSEYEAR
LICENSEGROUPID
DESCRIPTION
GLOBALENTITYACCOUNTID
CALENDARDUEDATE
ISAPPLIEDONLINE
IMPORTEDCUSTOMER
|
|
|
|
|
The short answer is: No it can't be done, unless you have some other data that allows you to backtrack the relations.
There should be a table for the invoice rows, what content does that have?
|
|
|
|
|
There are definitely other tables in the mix, as is clear from the License table. I suspect that you can walk a relationship between those. All of those ID fields point to something, chase them down. My suggested starting points are LICENSETYPEID, LICENSESTATUSID, and LICENSECLASSIFICATIONID.
Keep in mind that you may need to join on GLOBALENTITYID to get useful results.
|
|
|
|
|
I am creating one website for external exam duty allotment.I am using msaccess database and jsp to connect it,html and css.
I have 2 tables room and facutly details.
Attribute of room table are total_rooms_available, session and date. Attribute of faculty details are faculty_id,faculty_name experience, designation,mail_id and phone_no.
my question is how to fecth faculties randomly based on no of rooms available i.e (if total_rooms_available is 10 i have to fetch 10+3 faculties randomly from facutlydetails table)
|
|
|
|
|
I'm taking a database driven website course. The teacher wants us to build a database driven website. I haven't touched a database in a yr since taking a sql plus course and even then never built a database from the planning stage. My idea is to build a website of my favorite music artist with their record album, year, song title, label stored in a database. This website will also have a database that when you click on the artist, a bio of the artist will appear. I also want to be able for the visitor to click a link and play song from my mp3 collection database. I have a database model of what I have so far that I can post. The teacher has given us very little direction other than a book called web programming and internet technologies. Everyone in the course is still in the dark about how to go about doing this project. Any ideas would be approciated on helping me design this. Thank you in advance.
|
|
|
|
|
Well, as it is a school assignment you are expected to do the work. However, there are lots of samples of website projects that you can find by doing a Google search, or look at some of the articles referred to at Beginner's Walk - Web Development[^]. The CodeProject articles section also has many database articles.
|
|
|
|
|
Hello experts,
I hope this question falls under the Database category.
I have this query:
Select e.Dept, e.division, a.managerID,e.EmpName,e.empnum, e.Email,e.zip, e.SSN FROM Emp e, Angular a Where e.empID = a.managerid OR e.empID = @empid and password=@pass
What I am attempting to do is that if a user attempts to log in and that user is a manager, redirect him/her to manager screen.
Otherwise, redirect to employee screen.
The issue is that when I run that query, I expect to see records associated with that manager.
It shows ALL employees belonging to different managers.
How do I tweak this to work?
A bit more info:
There are two tables, Emp table with following relevant attributes:
EmpName,
EmpID
Dept
Division
Password
SSN
User logs in with EmpID as username and last digits of SSN as password
Then this other table called Angular with following relevant attributes:
EmpID - FK to EmpID from Emp table
ManagerID FK to EmpID from Emp table
Status (done or pending)
The logic we are trying to employ is that if empID (from Emp table) that the user is logging in with matches ManagerID (from Angular table) then this must be a manager, redirect him or her to manger page.
If empID from Emp table does not match managerID from Angular table, redirect to employee table.
What am I doing wrong?
The code for redirect is asp.net and no need to post it here because if the query is correct, that will work.
Your assistance is greatly appreciated.
modified 29-Sep-15 10:27am.
|
|
|
|
|
Something like this?
SELECT
e.Dept,
e.division,
a.managerID,
e.EmpName,
e.empnum,
e.Email,
e.zip,
e.SSN
FROM
Emp As e
LEFT JOIN Angular As a
ON a.managerid = e.empID
WHERE
e.empID = @empid
And
e.password = @pass
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ahhh!
Brilliant again Richard.
Thanks as always.
Very much appreciated.
modified 29-Sep-15 10:27am.
|
|
|
|
|
Hi,
I am using below code to display times as Pivot table grouped by date, this is basically for fingerprint attendance... I am getting what I want like this:
2012-06-03 10:23:30,10:23:32,10:24:05,10:24:07,10:24:24,10:24:26
How can I make the comma separated values displayed in columns instead of comma so it will be something like this
created_date - time1 - time2 - time3 - time4 --- etc
this is the code:
SELECT created_date, GROUP_CONCAT(created_time)
FROM fingerprint
GROUP BY created_date
Technology News @ www.JassimRahma.com
|
|
|
|
|
update sc_bill_details set dt_open_date=(select dt_bill_date from sc_bill where sc_bill.st_bill_no=sc_bill_details.st_bill_no and dt_bill_date>='1-7-2015')
when m writing above query in sql pane of postgresql database then m getting follwing error...though there is no null value..i have changed date datatype of above tables from timestamp to date.
ERROR: null value in column "dt_open_date" violates not-null constraint
DETAIL: Failing row contains (VC2012030000303, VC20100300266, null, 11, 5, RBS, 1, 40, 40, 0, 14, 0, 1, 0, 1, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0).
and when m writing above query taking different table as an example then am getting desired output...i dont understand whats wrong with it..Please get this issue resolved .
Thanks
|
|
|
|
|
Member 11919722 wrote: Please get this issue resolved .
How? We do not have access to your database so we have no way of correcting its content.
|
|
|
|
|
but atleast you can tell me the best possible solutions
|
|
|
|
|
The best possible solution is to learn all about database management. It looks like (but this is a guess since your question is not clear) that you have changed the schema of your database and now some of your records do not match the schema.
|
|
|
|
|
actaually as i said that if i write the query (which i posted in the forum) then i get the error....but if i write the same query using diffrent tables then it gives desired output
|
|
|
|
|
i have checked ...n both the tables are in same schema.
|
|
|
|
|
Then you need to investigate how you have records in one table that contain null values where they are not allowed.
|
|
|
|
|
that is what my issue that m getting null values though there is no column which has no records
|
|
|
|
|
Look at the error message you receive:
ERROR: null value in column "dt_open_date" violates not-null constraint
DETAIL: Failing row contains (VC2012030000303, VC20100300266, null, 11, 5, RBS, 1, 40, 40, 0, 14, 0, 1, 0, 1, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0).
It clearly explains, and shows, that you have a record with a null value where one is not allowed. You need to use your database management tools to find that record and fix its content.
|
|
|
|
|
ya i have checked it but ...there is no null vaue ....i just send u screen shot of my tables then hope u understand my issue ok.
|
|
|
|
|
Member 11919722 wrote: hope u understand my issue
Yes, I do understand your issue, but I do not think you do.
Either:
- You have a record in your database that contains a null value, or
- The
select dt_bill_date part of your update query does not find a record, so you are passing in a null value.
So, the solution is to find out why the select part of your update is returning a null value, or why that record contains null in the database.
|
|
|
|
|
thats wt how ,, i searched alot and tried solving but there is no null value though its giving
|
|
|
|
|
Run the select part of your query and see what value gets returned.
|
|
|
|
|
its returning the dates greater than 2015-07-01 without any null rrecords for dt_bill_date column of sc_bill table but i want same record to be displayed for dt_open_date column of sc_bill_detail ...thats what my requirement actually
|
|
|
|
|
I suggest you try modifying your code so it iterates through all the results doing the update for each found record.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
