[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

How would I build this query based on 4 columns of dropdowns and 5 possible rows?

I have a search form that has 4 columns with 5 possible rows. The first two columns are dropdowns and the last two input fields.

This is UNIBasic but the SQL should be pretty much the same with the exception that "WHERE" is called "WITH". So, as long as it is done in basic programming it should work or be adaptable.

The problem I am having is that the user can choose the Subject in the first column of the first row and ignore everything else and then select the Subject and Course Level in the second row and enter a Section Number in the third row and then do their search.

So my query should be like this:

SELECT COURSE.SECTIONS WITH SEC.TERM = '2010/FA' AND SEC.SUBJECT = 'ACCT' AND SEC.NO = '08' AND SEC.COURSE.LEVELS = '100' OR SEC.SUBJECT = 'ECON' AND SEC.COURSE.LEVELS = '200' AND SEC.COURSE.NO = '55' AND SEC.CURRENT.STATUS = 'A' BY COURSE.SECTIONS.ID

Basically, I need to add an OR if the user has selected or entered something in the other rows. And I need to build this query each time.

Now, it just occurred to me that I am requiring the user to select a term or date range on the form so the WITH is already always going to be there.

But, this is what I have so far:

FOR XX = 1 TO 5

* SEARCH BY SUBJECT **********************************

IF LEN(XL.SEC.SUBJECT<1,XX>) GT 0 THEN
   IF (X.WITH) THEN
      X.STMT := " AND SEC.SUBJECT = '":XL.SEC.SUBJECT<1,XX>:"'"
   END ELSE
      X.STMT := " WITH SEC.SUBJECT = '":XL.SEC.SUBJECT<1,XX>:"'"
      X.WITH = 1
   END
END

* SEARCH BY COURSE LEVELS **********************************

IF LEN(XL.SEC.COURSE.LEVELS<1,XX>) GT 0 THEN
   IF (X.WITH) THEN
      X.STMT := " AND SEC.COURSE.LEVELS = '":XL.SEC.COURSE.LEVELS<1,XX>:"'"
   END ELSE
      X.STMT := " WITH SEC.COURSE.LEVELS = '":XL.SEC.COURSE.LEVELS<1,XX>:"'"
      X.WITH = 1
   END
END

* SEARCH BY COURSE NUMBER **********************************

IF TRIM(LEN(XL.SEC.COURSE.NO<1,XX>)) GT 0 THEN
   IF (X.WITH) THEN
      X.STMT := " AND SEC.COURSE.NO = '":XL.SEC.COURSE.NO<1,XX>:"'"
   END ELSE
      X.STMT := " WITH SEC.COURSE.NO = '":XL.SEC.COURSE.NO<1,XX>:"'"
      X.WITH = 1
   END
END

* SEARCH BY COURSE SECTION **********************************

IF TRIM(LEN(XL.SEC.NO<1,XX>)) GT 0 THEN
   IF (X.WITH) THEN
      X.STMT := " AND SEC.NO = '":XL.SEC.NO<1,XX>:"'"
   END ELSE
      X.STMT := " WITH SEC.NO = '":XL.SEC.NO<1,XX>:"'"
      X.WITH = 1
   END
END

NEXT XX

any help would be appreciated
0
Donnie Walker
Asked:
Donnie Walker
  • 2
1 Solution
 
Alpha AuCommented:
For AND case, i suggest to add
with 1=1
at the beginning,

all condition after can use and directly (without verification)
and SEC.COURSE.NO = '":XL.SEC.COURSE.NO<1,XX>:"'"

and for the OR case. you'd have to carefully design which come 1st, OR/AND
0
 
Donnie WalkerAuthor Commented:
I need a coding example.
0
 
Donnie WalkerAuthor Commented:
thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now