Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Carbon
Starting Member
2 Posts |
 Posted - 2008-04-16 : 10:38:31
|
| I'm new to SQL, so I apologize for the easier question compared to some of the stuff I was reading on here. I work for a construction company and the accounting software they use, uses SQL in the report writer it looks like. So I've been trying to learn the basics on the fly for a while now. Anways, on to the question...Basically there are two different tables of data I'm trying to pull into one report. I'll give my code for it and then describe the outputs.**********************************[SELECT bdglin.hrsbdg FROM bdglin WHERE bdglin.recnum = {jobcst.jobnum}] + [SELECT sbcgln.chghrs FROM sbcgln, sbcgln INNER JOIN prmchg ON sbcgln.recnum = prmchg.recnum WHERE prmchg.jobnum = {jobcst.jobnum} and prmchg.status = 1]**********************************I'm starting to think it's something to do with the brackets in there. If I leave the code like that, it's printing the bdglin.hrsbdg out if there is a numeric value (which is good), but it is multiplying it by some number that I can't figure out where it is coming from. So if I'm expecting to see "5" it is either outputting "5" or some whole number multiple of it.If I add these { } brackets around the whole block of code. The outputs are correct but display like: " {5 + 0} " So 5, the correct budget number and 0 if there is nothing in the change order screen. Basically the multiples are gone, but it's not adding together the budget and change order numbers.I spent a fair amount of time tinkering with it yesterday and today a bit with a clear head, and read as much as I could online about SQL to see I needed to ask someone knowledgable if I was going to make headway. Thank you very much in advance for your time and any help you can lend. |
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-04-16 : 11:00:59
|
| Can you give the entire SQL code that your report is using?Prakash.PThe secret to creativity is knowing how to hide your sources! |
 |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-04-16 : 13:37:15
|
| Also, table DDL and sample data would help us to help you. Why are you referencing {jobcst.jobnum} when the table, jobcst, isn't part of the selects??Terry |
|
|
|
Carbon
Starting Member
2 Posts |
Posted - 2008-04-16 : 16:41:00
|
| I'm pretty new to this stuff, so your point about referencing the jobcst table could be part of the problem. Here is the jist of the report along with some outputs.****************************************Job #______Job Name______Total Hours______Budget Hours_______Start Date______Finish Datejobcst.jobnum___actrec.jobnme_____jobcst.csthrs_______jobcst^bughrs___________actrec.sttdte________actrec.cmpdte*****************************************The way this report writer is set up, I need to make a calculated field to pull in the budgeted hours since the budget tables are not part of the jobcost tables. The calculated field jobcst^bughrs is the part that is giving me the problems. That is the code form my original post that I have been trying to make work.Here are outputs that I keep seeing.If I leave that calcuted field (jobcst^bughrs ) as is, this is what I get...Job #_____Job Name_____Total Hours_____Budget Hours_____Start Date_____Finish Date100_______LTF Tops_____8.25____________132.5____________02/04/08_____03/01/085794______Jack Res.____17.5____________698.34___________10/04/07______02/15/086236______Haslow________536.5__________48,891.00_______02/11/08_______02/25/08If I changed added these brackets {} around the code from that calculated field (jobcst^bughrs) I get this:Job #_____Job Name_____Total Hours_____Budget Hours_____Start Date_____Finish Date100_______LTF Tops_____8.25____________{62.25 + 4}_______02/04/08_____03/01/085794______Jack Res.____17.5____________{232.78 + 0}________10/04/07______02/15/086236______Haslow________536.5__________{568.5 + 0}_______02/11/08_______02/25/08Sorry for the underscores, I couldn't get the columns to line up without. Since this report is just for me, I can live with the second output, but if I could figure out why it's doing what it's doing, it would help me understand more about these reports so I can be more help if someone needed to adjust other reports in the future. |
|
|
|
|
|
|
|
|
SELECT question for SQL beginner