Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Question (Delphi 3)

Posted on 1999-06-28
8
Medium Priority
?
126 Views
Last Modified: 2010-04-06
I have a query (see below) which works fine EXCEPT it does not include records in its result set where the ORDER BY field (CWU) is null ('').

When I delete the WHERE clause, the null records show up, but with the WHERE clause, the null records go away (even when CWUParam set to '%%'),

Is this a quirk of SQL???

***********************
SELECT  CWU, UNITS, HOURS
FROM "c:\download\hours\plan.dbf"
WHERE CWU LIKE :CWUParam
ORDER BY CWU
***********************
0
Comment
Question by:wkhays
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 3

Expert Comment

by:philipleighs
ID: 1385017
How about something like

...
WHERE (CWU LIKE :CWUParam) OR (CWU = '')
...

Cheers,
Phil
0
 
LVL 4

Author Comment

by:wkhays
ID: 1385018
Phil,

I can't work out why what you suggested wouldn't work... But it didn't.

I tried a slight variation of your idea, however, and it looks promising... I'll have to check it out tomorrow AM, since the actual code is a bit more involved.  Certainly, if it works, I'll give you the 50 points...

Cheers,
Keith

**************

Here's the variation (I'm surprised it runs!!):

SELECT  CWU, UNITS, HOURS
FROM "c:\download\hours\plan.dbf"
WHERE ( (CWU+" " = ' ') OR (CWU LIKE :CWUParam) )
ORDER BY CWU

**************

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1385019
hi wkhays,

if your variation not work then try this

SELECT  CWU, UNITS, HOURS
FROM "c:\download\hours\plan.dbf"
WHERE ( (CWU is null) OR (CWU LIKE :CWUParam) )
ORDER BY CWU

meikl
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:wkhays
ID: 1385020
My variation from Philipleighs was close, but isn't ideal since by adding the space to CWU (WHERE CWU+' ' LIKE :CWUParam) doesn't really give me an accurate search.... My dynamic search parameter is not really searching on the key field, but rather a slight variation of it.

Kretzschmar's idea of "WHERE (CWU IS null) ... " seems close to the mark, but there is a problem in that when I type in a dynamic search parameter of, say, "P010" then I not only get all of the P010 entries, but also the nulls...  I suppose that on my ONCHANGE event for my edit string, I could change the SQL code between:
   WHERE ((CWU IS null) OR (CWU LIKE :CWUParam))
and:
   WHERE (CWU LIKE :CWUParam)
depending on whether the edit string has any characters or not.

This may be the most elegant answer, but I hope not.

I'll hold out for the rest of the day for a more elegant answer ... :)

Thanks,
Keith



0
 
LVL 4

Author Comment

by:wkhays
ID: 1385021
My variation from Philipleighs was close, but isn't ideal since by adding the space to CWU (WHERE CWU+' ' LIKE :CWUParam) doesn't really give me an accurate search.... My dynamic search parameter is not really searching on the key field, but rather a slight variation of it.

Kretzschmar's idea of "WHERE (CWU IS null) ... " seems close to the mark, but there is a problem in that when I type in a dynamic search parameter of, say, "P010" then I not only get all of the P010 entries, but also the nulls...  I suppose that on my ONCHANGE event for my edit string, I could toggle the SQL code from:
   WHERE ((CWU IS null) OR (CWU LIKE :CWUParam))
to:
   WHERE (CWU LIKE :CWUParam)
This may be the most elegant answer, but I hope not.



0
 
LVL 4

Author Comment

by:wkhays
ID: 1385022
Once I coded the OnChange code, things work just as I had hoped...

Kretzschmar: Post something so I can give you credit for the question...

Phil - Thanks for your help as well!

Keith
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 150 total points
ID: 1385023
hi again wkhays,

glad that i helped you,
and thanx for the points ;-)

good luck

meikl
0
 
LVL 4

Author Comment

by:wkhays
ID: 1385024
Thanks - My app is really coming along now!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question