INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Question - from Business Objects

SQL Question - from Business Objects

(OP)
I have the following SQL I've copied from a data provider query in Business Objects, and I'm trying to run it in Toad, can anyone help?

Specifically it's the following things that give the newbie in me the most trouble:
1) Prompts
2) Multiple From, Where, Group By clauses


SELECT   
  SF_TRIALS.TRIAL_ID,
  SF_ITEMS.ITEM_ID,
  SF_UTILIZATION_DEMAND.MONTH,
  ITEM_ALLOCATION.ALLOC_MONTH,
  Sum(SF_UTILIZATION_DEMAND.QTY),
  SUM(CASE
   WHEN ITEM_ALLOCATION.STATUS_CD = 75 THEN ITEM_ALLOCATION.ALLOCATED_QTY
END),
  SUM(CASE
   WHEN ITEM_ALLOCATION.STATUS_CD = 180 THEN ITEM_ALLOCATION.ALLOCATED_QTY
END),
  @Prompt('2. Outlook Threshold (In Months)', 'N',,,),
  @Prompt('3. Danger Threshold (In Months)', 'N',,,),
  sysdate,
  SF_ITEMS.NAME,
  SF_TRIALS.NAME,
  SF_PROGRAMS.PROGRAM_ID,
  To_Date(@Prompt('1. Start Date','D',,,))
FROM
  SF_ITEMS INNER JOIN SF_UTILIZATION_DEMAND ON (SF_ITEMS.ITEM_SEQ=SF_UTILIZATION_DEMAND.ITEM_SEQ)
   INNER JOIN SF_TRIALS ON (SF_UTILIZATION_DEMAND.TRIAL_SEQ=SF_TRIALS.TRIAL_SEQ)
   INNER JOIN SF_PROGRAMS ON (SF_PROGRAMS.PROGRAM_SEQ=SF_TRIALS.PROGRAM_SEQ)
   LEFT OUTER JOIN ( SELECT SF_TRIAL_LOTS.TRIAL_SEQ, SF_LOTS.ITEM_SEQ, ADD_MONTHS(TRUNC(SF_LOTS.PLANNED_PRODUCTION_DATE,'mon'), SF_LOTS.EXP_LAG_MONTHS) As ALLOC_MONTH, SF_LOTS.STATUS_CD, Sum(SF_TRIAL_LOTS.ALLOCATED_QTY) As ALLOCATED_QTY


FROM SF_LOTS, SF_TRIAL_LOTS

WHERE SF_TRIAL_LOTS.LOT_SEQ = SF_LOTS.LOT_SEQ AND
SF_TRIAL_LOTS.ALLOCATED_QTY > 0 AND
SF_TRIAL_LOTS.ALLOCATED_QTY Is Not Null

GROUP BY
  SF_TRIAL_LOTS.TRIAL_SEQ,
  SF_LOTS.ITEM_SEQ,
  ADD_MONTHS(TRUNC(SF_LOTS.PLANNED_PRODUCTION_DATE,'mon'), SF_LOTS.EXP_LAG_MONTHS),
  SF_LOTS.STATUS_CD


) ITEM_ALLOCATION ON (ITEM_ALLOCATION.TRIAL_SEQ=SF_UTILIZATION_DEMAND.TRIAL_SEQ and ITEM_ALLOCATION.ITEM_SEQ=SF_UTILIZATION_DEMAND.ITEM_SEQ and ITEM_ALLOCATION.ALLOC_MONTH=SF_UTILIZATION_DEMAND.MONTH)
  
WHERE
  (
  ( ( SF_PROGRAMS.PROGRAM_ID ) In @Prompt('4. Program ID','A','Trial LOV\Program Id (All)',multi,)
OR
'- All -' In @Prompt('4. Program ID','A','Trial LOV\Program Id (All)',multi,)  )
  AND  ( ( SF_TRIALS.TRIAL_ID ) In @Prompt('5. Trial ID','A','Trial LOV\Trial Id (All)',multi,)
OR
'- All -' In @Prompt('5. Trial ID','A','Trial LOV\Trial Id (All)',multi,)  )
  AND  ( ( SF_ITEMS.ITEM_ID ) In @Prompt('6. Item ID','A','Items (Common)\Item Id',multi,)
OR
'- All -' In @Prompt('6. Item ID','A','Items (Common)\Item Id',multi,)  )
  )
GROUP BY
  SF_TRIALS.TRIAL_ID,
  SF_ITEMS.ITEM_ID,
  SF_UTILIZATION_DEMAND.MONTH,
  ITEM_ALLOCATION.ALLOC_MONTH,
  @Prompt('2. Outlook Threshold (In Months)', 'N',,,),
  @Prompt('3. Danger Threshold (In Months)', 'N',,,),
  sysdate,
  SF_ITEMS.NAME,
  SF_TRIALS.NAME,
  SF_PROGRAMS.PROGRAM_ID,
  To_Date(@Prompt('1. Start Date','D',,,))
ORDER BY
  1,
  2,
  3,
  4

RE: SQL Question - from Business Objects

The "@Prompt()" syntax is specific to BOE - it prompts the user for an entry.  If you're running this in Oracle, the syntax for a prompt/parameter is ":param_name" where param_name is the name of your parameter.  If you have multiple entries with the same param name, you'll only be asked for the value once.  Toad will give you the option to set the type of the parameter, so you can specify whether it's a date, string, number, etc.

You should be able to leave the rest of your SQL the same with the exception of the "To_Date()" in the final Group By.  The easiest way to handle that is to remove the To_Date() function, use a parameter, and set the type of the parameter to Date.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close