Results 1 to 17 of 17

Thread: Query Question how to get desired result.

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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?

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    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.
    Last edited by Zvoni; Nov 16th, 2022 at 03:08 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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?

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    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
    Last edited by Zvoni; Nov 16th, 2022 at 04:46 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    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
    Last edited by Zvoni; Nov 16th, 2022 at 04:59 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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 (,).

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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 (,).

  14. #14

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Query Question how to get desired result.

    Quote Originally Posted by DataMiser View Post
    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
    Last edited by Zvoni; Nov 17th, 2022 at 09:35 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  16. #16

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Query Question how to get desired result.

    Quote Originally Posted by DataMiser View Post
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width