SQL Statement Question - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

SQL Statement Question

How do I write an SQL Statement Properly

hhope810

2:13 pm on Oct 14, 2009 (gmt 0)

10+ Year Member

I am working in a program called UltraPIPE and they have a custom report builder section. It relies on SQL Statements and I cannot get the results I'm looking for.

Here is the current statement:

SELECT
KBMASTER.GROUP, KBMASTER.EQ_CIRC_ID, KBVINFO.SERVICE,
KBVINFO.BNCHINTRVL, KBMASTER.SECTION_NAME, KBMASTER.MAST_DESC,
KBMASTER.EQ_TYPE, KBVTEST.TESTDATE, KBVTEST.COMMENT1, KBVTEST.COMMENT2,
KBVTEST.COMMENT3, KBVTEST.COMMENT4, KBVTEST.COMMENT5, KBVTEST.COMMENT6

FROM
(KBMASTER INNER JOIN KBVINFO ON
KBMASTER.GROUP = KBVINFO.GROUP AND
KBMASTER.EQ_CIRC_ID = KBVINFO.EQ_CIRC_ID)
INNER JOIN KBVTEST ON
KBVINFO.GROUP = KBVTEST.GROUP AND
KBVINFO.EQ_CIRC_ID = KBVTEST.EQ_CIRC_ID

WHERE
KBMASTER.GROUP LIKE '<P01>' AND KBMASTER.EQ_CIRC_ID LIKE '<P02>' AND
KBVINFO.SERVICE LIKE '<P03>' AND KBMASTER.SECTION_NAME LIKE '<P04>' AND
KBMASTER.EQ_TYPE LIKE '<P05>' AND
KBVTEST.TESTDATE >= #<DS># AND KBVTEST.TESTDATE <= #<DE># AND
KBVTEST.COMMENT1 LIKE '<P06>' OR KBVTEST.COMMENT2 LIKE '<P07>' OR
KBVTEST.COMMENT3 LIKE '<P08>' OR KBVTEST.COMMENT4 LIKE '<P09>' OR
KBVTEST.COMMENT5 LIKE '<P10>' OR KBVTEST.COMMENT6 LIKE '<P11>'

ORDER BY
KBMASTER.GROUP ASC,
KBMASTER.EQ_CIRC_ID ASC

In the WHERE section at the end it had "AND" in place of "OR". There are pulldown boxes for each of the "COMMENT#" and I want to be able to search all of the comments boxes at one time, not have to select each box one at a time. I put in the "OR" thinking that would do the trick. Any suggestions or do you need more information?

hhope810

3:08 pm on Oct 14, 2009 (gmt 0)

10+ Year Member

I have altered my statement as below:

SELECT
KBMASTER.GROUP, KBMASTER.EQ_CIRC_ID, KBVINFO.SERVICE,
KBVINFO.BNCHINTRVL, KBMASTER.SECTION_NAME, KBMASTER.MAST_DESC,
KBMASTER.EQ_TYPE, KBVTEST.TESTDATE, KBVTEST.COMMENT1, KBVTEST.COMMENT2,
KBVTEST.COMMENT3, KBVTEST.COMMENT4, KBVTEST.COMMENT5, KBVTEST.COMMENT6

FROM
(KBMASTER INNER JOIN KBVINFO ON
KBMASTER.GROUP = KBVINFO.GROUP AND
KBMASTER.EQ_CIRC_ID = KBVINFO.EQ_CIRC_ID)
INNER JOIN KBVTEST ON
KBVINFO.GROUP = KBVTEST.GROUP AND
KBVINFO.EQ_CIRC_ID = KBVTEST.EQ_CIRC_ID

WHERE
KBMASTER.GROUP LIKE '<P01>' AND KBMASTER.EQ_CIRC_ID LIKE '<P02>' AND
KBVINFO.SERVICE LIKE '<P03>' AND KBMASTER.SECTION_NAME LIKE '<P04>' AND
KBMASTER.EQ_TYPE LIKE '<P05>' AND
KBVTEST.COMMENT1 = 'VALVE STUCK' OR
KBVTEST.COMMENT2 = 'VALVE STUCK' OR
KBVTEST.COMMENT3 = 'VALVE STUCK' OR
KBVTEST.COMMENT4 = 'VALVE STUCK' OR
KBVTEST.COMMENT5 = 'VALVE STUCK' OR
KBVTEST.COMMENT6 = 'VALVE STUCK' OR
KBVTEST.COMMENT7 = 'VALVE STUCK' OR
KBVTEST.COMMENT8 = 'VALVE STUCK' AND
KBVTEST.TESTDATE >= #<DS># AND KBVTEST.TESTDATE <= #<DE>#

ORDER BY
KBMASTER.GROUP ASC,
KBMASTER.EQ_CIRC_ID ASC
That seems to have done the trick. I wanted to post the resolution so if anyone has better ideas they could reply.

hhope810

3:09 pm on Oct 14, 2009 (gmt 0)

10+ Year Member

Just a note on the above resolution, this only allows me to search for one specific problem per report. At this time I am planning to build several reports and just title them by what they look for . . .

hhope810

1:40 pm on Oct 19, 2009 (gmt 0)

10+ Year Member

I have worked up another solution to my own problem, but here it is.

SELECT
KBMASTER.GROUP, KBMASTER.EQ_CIRC_ID, KBVINFO.SERVICE,
KBVINFO.BNCHINTRVL, KBMASTER.SECTION_NAME, KBMASTER.MAST_DESC,
KBMASTER.EQ_TYPE, KBVTEST.TESTDATE, KBVTEST.COMMENT1, KBVTEST.COMMENT2,
KBVTEST.COMMENT3, KBVTEST.COMMENT4, KBVTEST.COMMENT5, KBVTEST.COMMENT6
FROM
(KBMASTER INNER JOIN KBVINFO ON
KBMASTER.GROUP = KBVINFO.GROUP AND
KBMASTER.EQ_CIRC_ID = KBVINFO.EQ_CIRC_ID)
INNER JOIN KBVTEST ON
KBVINFO.GROUP = KBVTEST.GROUP AND
KBVINFO.EQ_CIRC_ID = KBVTEST.EQ_CIRC_ID
WHERE
KBMASTER.GROUP LIKE '<P01>' AND KBMASTER.EQ_CIRC_ID LIKE '<P02>' AND
KBVINFO.SERVICE LIKE '<P03>' AND KBMASTER.SECTION_NAME LIKE '<P04>' AND
KBMASTER.EQ_TYPE LIKE '<P05>' AND
KBVTEST.COMMENT1 LIKE '<P06>' or
KBVTEST.COMMENT2 LIKE '<P06>' or
KBVTEST.COMMENT3 LIKE '<P06>' or
KBVTEST.COMMENT4 LIKE '<P06>' or
KBVTEST.COMMENT5 LIKE '<P06>' or
KBVTEST.COMMENT6 LIKE '<P06>' or
KBVTEST.COMMENT7 LIKE '<P06>' or
KBVTEST.COMMENT8 LIKE '<P06>' or
KBVTEST.COMMENT9 LIKE '<P06>' AND
KBVTEST.TESTDATE >= #<DS># AND KBVTEST.TESTDATE <= #<DE>#

ORDER BY
KBMASTER.GROUP ASC,
KBMASTER.EQ_CIRC_ID ASC

I have allowed the search box for KBVTEST.COMMENT1, but changed the title to "Search Term". Then I equated all of the comment boxes to the singular Pick Box #6.

Still trying to figure out how to return only the latest test date on this.