Query Question how to get desired result.
Ok so I have a query that returns a bunch of fields and rows from a large table and I find that I need to calculate a value from another table and hopefully have that value display in the resulting recordset as a field.
To keep it simple let's say I have a query
Select recorid,description,dtstamp and several more fields from table1
From the second table I need to know if there are any rows with the matching recordid and a status='PS'
I could work with either a count of the records or just a true false based on if there is a match or not.
I am thinking maybe a sub query of some sort using count() but can't determine how to write it.
Keep in mind this table2 may have no records in it with a matching recordid or it may have lots of them and those that may be there may or may not have the status='PS' and I basically just need to know if there are any with matching recordid and status='PS'
Any hints?
Re: Query Question how to get desired result.
A subquery of:
Code:
select recordid, count(id) PSCount from yourTable where status='PS' group by recordid
Then you can left join on record ID ... then use a case or isNull on PSCount to either display PSCount, or 0 if it's null.
-tg
Re: Query Question how to get desired result.
Yep I'm still lost. Here is a query I was using but the requirement has changed.
Code:
Select dcorder,item,descrip,qtyreqd,a.qtystart,a.qtycomp,shipdate,ostatus,path2files,a.tdatetime,a.recordid,a.lineno,count(b.rollid) as jobs from vtmast03 a left outer join vttran03 b on a.recordid=b.recordid where (ostatus='' or ostatus='PS') group by dcorder,item,descrip,qtyreqd,a.qtystart,a.qtycomp,shipdate,ostatus,path2files,a.tdatetime,a.recordid,a.lineno order by shipdate asc;
Originally I needed to know how many jobs existed for each recordid, now I just need to know if any jobs have been started for that record id. Does not even matter how many just if it is 0 or >0
So far I have tried several different attempts but all have failed.
Re: Query Question how to get desired result.
Like tg said: LEFT JOIN is the secret
The way i would do it
Code:
Select T1.recID, T1.Field1,……… (Case When T2.recID Is Null Then 0 Else T2.PSCount) As PSCount
From Table1 As T1
Left Join (Select recID, Count(ID) As PSCount From Table2 Where status=‚PS‘ GROUP BY recID) As T2
On T2.recID=T2.recID
Re: Query Question how to get desired result.
Quote:
Originally Posted by DataMiser;[URL="tel:5586332"
5586332[/URL]]Yep I'm still lost. Here is a query I was using but the requirement has changed.
Code:
Select dcorder,item,descrip,qtyreqd,a.qtystart,a.qtycomp,shipdate,ostatus,path2files,a.tdatetime,a.recordid,a.lineno,count(b.rollid) as jobs from vtmast03 a left outer join vttran03 b on a.recordid=b.recordid where (ostatus='' or ostatus='PS') group by dcorder,item,descrip,qtyreqd,a.qtystart,a.qtycomp,shipdate,ostatus,path2files,a.tdatetime,a.recordid,a.lineno order by shipdate asc;
Originally I needed to know how many jobs existed for each recordid, now I just need to know if any jobs have been started for that record id. Does not even matter how many just if it is 0 or >0
So far I have tried several different attempts but all have failed.
left joining to t2 (vttran03 with b-alias)directly is in your case not a good idea.
since you need a yes/no result, you have to force T2 to return a single record per recID, and you can only achieve that with a subquery within the left join.
see my sample
EDIT: just saw your query.
you have aliased and not aliased fields in your query and where-clause.
might be confusing. Could you clean that up?
the requirement sounds simple enough to me.
Re: Query Question how to get desired result.
hmmm... still no joy.
Perhaps I should have mentioned that this is a Visual Fox Pro db
This is the most recent query I tried
Code:
Select T1.dcorder,T1.item,T1.descrip,T1.qtyreqd,T1.qtystart,T1.qtycomp,T1.shipdate,T1.ostatus,T1.tdatetime,T1.recordid,T1.lineno, (Case When T2.recordID Is Null Then 0 Else T2.PSCount) As PSCount from vtmast03 as T1 Left outter Join (Select recordID, Count(recordid) As PSCount From vttran03 Where status='PS‘ GROUP BY recordID) As T2 On t1.recordID=T2.recordID
result is
[Microsoft][ODBC Visual FoxPro Driver]Function name is missing ).
Am I doing something wrong or is this just not supported in VFP?
Re: Query Question how to get desired result.
Left outter join? Two „t“?
EDIT: i think vfp doesn‘t know Case When.
try IIF instead
Select ………. IIF(IsNull(T2.recID), 0, T2.PSCount) As …… From
EDIT2: Damn! I forgot the closing End in the Case When.
Case When condition Then val1 else val2 END
that‘s if vfp even supports Case When
sorry, my mistake
Re: Query Question how to get desired result.
I seem to remember having failures when not using the outter keyword in there with FP. but I did try it both ways, same error message.
I don't see the two,, but then my eyesight is not what it used to be.
I'll give the IIF a shot later. Thanks.
Re: Query Question how to get desired result.
I was just reading on the peculiarities of vfp.
Have you tried to send the subquery into a cursor?
and then join the cursor?
EDIT: oh dear, i think you have to read vfp‘s SQL-documentation carefully.
i just found a mention, Max-Function needing two arguments in vfp.
maybe it‘s something with the count-function
Re: Query Question how to get desired result.
Hmm well I tried the IIF and getting a different error now. It says I am missing a comma but I do not see where one is missing. I even shortened up the number of fields in the select but still get the error.
Select T1.dcorder,PSCount= IIF(T2.recordID Is Null ,0 ,T2.PSCount ) from vtmast03 as T1 Left Join (Select recordID, Count(recordid) As PSCount From vttran03 Where status='PS' GROUP BY recordID) As T2 On t1.recordID=T2.recordID
[Microsoft][ODBC Visual FoxPro Driver]Missing comma (,).
Re: Query Question how to get desired result.
I would like to solve this and get this to work but in the mean time I went ahead and created a second query that runs inside the loop that reads the results from the first one. This works of course but I would think it would be faster if it were all in one query.
If it were SQL Server I wouldn't be having these issues ;)
Re: Query Question how to get desired result.
Code:
Select T1.dcorder, IIF(T2.recordID Is Null ,0 ,T2.PSCount) As PSCount from vtmast03 as T1 Left Join (Select recordID, Count(recordid) As PSCount From vttran03 Where status='PS' GROUP BY recordID) As T2 On t1.recordID=T2.recordID
If it doesn't work, try the individual SELECT's separately
1) Select recordID, Count(recordid) As PSCount From vttran03 Where status='PS' GROUP BY recordID --> Does it work?
2) Select recordID, Count(recordid) As PSCount From vttran03 Where status='PS' GROUP BY recordID INTO CURSOR curTemp NOFILTER --> You should get a temp. File in your Folder
3)
Select recordID, Count(recordid) As PSCount From vttran03 Where status='PS' GROUP BY recordID INTO CURSOR curTemp NOFILTER; //(Note the semicolon)
Select T1.dcorder, IIF(IsNull(T2.recordID, 0, T2.PSCount) AS PSCount FROM vtmast03 As T1 LEFT JOIN curTemp As T2 ON t1.recordID=T2.recordID;
4)
Select T1.dcorder, NVL(Select T2.recordID, Count(T2.recordid) As PSCount From vttran03 As T2 Where T2.status='PS' AND T2.recordID=T1.recordID GROUP BY recordID), 0) As PSCount from vtmast03 as T1
Re: Query Question how to get desired result.
1: Works and returns a record set.
2: Also works and returns a recordset but did not find a file. Looked in current folder and the dbf folder
3: Error [Microsoft][ODBC Visual FoxPro Driver]Too many arguments.
4: Error [Microsoft][ODBC Visual FoxPro Driver]Function name is missing ).
And the one in the code block returns
[Microsoft][ODBC Visual FoxPro Driver]Missing comma (,).
Re: Query Question how to get desired result.
I may re-visit this later today and then if I still haven't gotten it working move on and use a different approach.
Re: Query Question how to get desired result.
Quote:
Originally Posted by
DataMiser
3: Error [Microsoft][ODBC Visual FoxPro Driver]Too many arguments.
OOps. My Bad
Select recordID, Count(recordid) As PSCount From vttran03 Where status='PS' GROUP BY recordID INTO CURSOR curTemp NOFILTER; //(Note the semicolon)
Select T1.dcorder, IIF(IsNull(T2.recordID), 0, T2.PSCount) AS PSCount FROM vtmast03 As T1 LEFT JOIN curTemp As T2 ON t1.recordID=T2.recordID;
I missed a closing paranthesis for the IsNull.
Try Again?
I was going by this instructions: http://www.yaldex.com/fox_pro_tutorial/html/c3875b75-5dc5-4eff-b33f-25b8686301ad.htm
EDIT for 4 (we're leaving out the "prettyfying" of the Result):
Select T1.dcorder, (Select Count(T2.recordid) As PSCount From vttran03 As T2 Where T2.status='PS' AND T2.recordID=T1.recordID GROUP BY T2.recordID) As PSCount from vtmast03 as T1
Re: Query Question how to get desired result.
The first one when entered as a whole with the //(Note the semicolon) removed returns a result but the return is recordid and count.
If I run just the first part I get the same result.
If I then try to run the second part I get curTemp.dbf does not exist
the edit version of 4 gives me an invalid use of sub query error message.
Thanks for the help, seems like maybe best to let this one go.
Re: Query Question how to get desired result.
Quote:
Originally Posted by
DataMiser
The first one when entered as a whole with the //(Note the semicolon) removed returns a result but the return is recordid and count.
If I run just the first part I get the same result.
If I then try to run the second part I get curTemp.dbf does not exist
the edit version of 4 gives me an invalid use of sub query error message.
Thanks for the help, seems like maybe best to let this one go.
Well, a last ditch effort might be to push the first one into a real (temp.) table instead of a cursor, and then join the second one to that dbf-file