Results 1 to 5 of 5

Thread: SQL Logic Q

  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: SQL Logic Q

    Hi all. My situation is this...

    I have a query that selects 'requester names' from a table from first day of the month to the last day of the month...Something like:

    Code:
    SELECT DISTINCT cReqName, cReqID 
    FROM tbl_OpenWindowRequests 
    WHERE dSubmit between ('01-JAN-2004') and ('31-JAN-2004') 
    OR dApprRej between ('01-JAN-2004') and ('31-JAN-2004')
    Which with those particular dates returns me 1359 rows.

    This is the problem; for each of those results, I need to check for 4 different circumstances. In other words, I have to count how many requests have been: Submitted, Rejected, Approved or are still pending. Each of these conditions is indicated by WHERE/AND clauses on multiple columns for each condition. For example, to check approved I have the following:

    Code:
    		SELECT DISTINCT CREQNAME, COUNT (CreqName) Approved
    		FROM tbl_OpenWindowRequests
    		WHERE dSubmit between ('01-JAN-2004') and ('31-JAN-2004') 
    		OR dApprRej between ('01-JAN-2004') and ('31-JAN-2004') 
    		AND (iApprStatus=-1) 
    		AND ((iAppr2Status IS NULL) OR (iAppr2Status=-1)) 
    		AND ((iBatch Is Null) OR (iBatch > 0))
    		AND tbl_OpenWindowRequests.cRegion = 'UP' 
    		AND tbl_OpenWindowRequests.cChannel = '2'
    Here is where the problem is....I could easily have 4 queries to do this with, Each one checking 1349 rows for conditions such as the ones above. However, this is all being displayed on an ASP page - so that just isn't cutting it in the performance department. Is there a way for me to do this all within the query? I'm not sure if this is possible, but logically I guess it would be something like a for each loop(?)

    For each Requester
    in table_x
    where dateSubmitted between (01-JAN-04) AND (31-JAN-04)
    --Check Approved
    Count how many requesters meet condition 1 AND condition 2, etc
    --Check Rejected
    Count how many requesters meet condition 1 AND conditon 2, etc
    --Check Submitted
    Count how many requesters meet condition 1 AND condition 2, etc
    --Check Pending
    Count how many requesters meet condition 1 AND condition 2, etc

    of course thats just some pseudo code. Basically, I'd need to do this in the most efficient way possible. I could put all this logic into a stored procedure if I could just nail the SQL syntax down - but first things first. Just for the record (get it, record?) I'm using Oracle 9i. Thank you for all your help

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    use case statements in your select clause to produce the "status"

    will all results be only one of: Submitted, Rejected, Approved or are still pending?

    ie: you cannot be Submitted AND StillPending?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    You can in fact be submited and still pending. Brief overview; users submit requests, more than one, on a monthly basis...

    IE, of the 50 requests user_a submits, 20 can be approved, 10 pending, and the remaining 20 rejected.

    Can i still use case statements?

  4. #4
    Join Date
    Dec 2003
    Posts
    148
    I think I'm on the right path with this...but not sure where i'm going wrong....could someone point me in the right direction?


    Code:
    SQL> SELECT CREQNAME,
      2   SUM(CASE WHEN (dSubmit between ('01-JAN-2004') and ('31-JAN-2004')
      3       OR dApprRej between ('01-JAN-2004') and ('31-JAN-2004'))
      4       AND (iApprStatus Is Null)
      5       OR ((iApprStatus=-1)
      6       AND (length(cAppr2ID)>0)
      7       AND (iAppr2Status Is Null))
      8   THEN 1 ELSE 0 END) Pending
      9   SUM(CASE WHEN (dSubmit between ('01-JAN-2004') and ('31-JAN-2004')
     10       OR dApprRej between ('01-JAN-2004') and ('31-JAN-2004'))
     11       AND iApprStatus=-1
     12       AND (iAppr2Status IS NULL OR iAppr2Status=-1)
     13       AND (iBatch Is Null OR iBatch > 0)
     14   from tbl_OpenWindowRequests
     15    THEN 1 ELSE 0 END) Approved
     16   from tbl_OpenWindowRequests;
     SUM(CASE WHEN (dSubmit between ('01-JAN-2004') and ('31-JAN-2004')
     *
    ERROR at line 9:
    ORA-00923: FROM keyword not found where expected
    
    
    SQL>

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    You only need to post the tables you are querying from once. Try this instead :

    Code:
    SELECT CREQNAME,
      2   SUM(CASE WHEN (dSubmit between ('01-JAN-2004') and ('31-JAN-2004')
      3       OR dApprRej between ('01-JAN-2004') and ('31-JAN-2004'))
      4       AND (iApprStatus Is Null)
      5       OR ((iApprStatus=-1)
      6       AND (length(cAppr2ID)>0)
      7       AND (iAppr2Status Is Null))
      8   THEN 1 ELSE 0 END) Pending,
      9   SUM(CASE WHEN (dSubmit between ('01-JAN-2004') and ('31-JAN-2004')
     10       OR dApprRej between ('01-JAN-2004') and ('31-JAN-2004'))
     11       AND iApprStatus=-1
     12       AND (iAppr2Status IS NULL OR iAppr2Status=-1)
     13       AND (iBatch Is Null OR iBatch > 0)
     15    THEN 1 ELSE 0 END) Approved
     16   from tbl_OpenWindowRequests
    Keep in mind that I did not check the open/closed parentheses to ensure they line up - you just listed the table twice, and did not have a comma after the first sum( ) statement.
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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