RE: SQL Question

[<SRamanujan@xxxxxxxxxxxxxxxxxx>]

I have seen this too.  I have had to pad the host variable field  with *all(%) 
for LIKe keyword and we are in v5r1.
Thanks,
Sudha

-----Original Message-----
From: Smith, Graham [mailto:GSmith2@scj.com]
Sent: Thursday, January 02, 2003 11:53 AM
To: 'midrange-l@midrange.com'
Subject: RE: SQL Question


Rick,

I wish you were right but I've bitter experience behind me in this case.

Stumbled across this years back and found that in a Host variable the single
% didn't work correctly but padding full length of host variable worked
fine.

Go figure!

Cheers
Graham

-----Original Message-----
From: Chevalier, Rick [mailto:Rick.Chevalier@americredit.com]
Sent: 02 January 2003 17:45
To: 'midrange-l@midrange.com'
Subject: RE: SQL Question


Graham,

I think you are confusing the '%' and '_' pattern functions.  You should
only need to code one '%' in a LIKE because it refers to multiple positions
in the field.  The '_' would need to be coded repeatedly because it only
refers to the exact position it occupies.  Refer to Andy's post for a
description from the manual.

Rick

 -----Original Message-----
From:   Smith, Graham [mailto:GSmith2@scj.com]
Sent:   Thursday, January 02, 2003 11:29 AM
To:     'midrange-l@midrange.com'
Subject:        RE: SQL Question


Cyndi,


In SQL you need to use the LIKE clause and the % sign eg.

Select * from File1 where FIELD1 like 'Gra%'

The above statement would return all records where FIELD1 contains the
string 'Gra' starting in position 1
eg.
Graham Smith
Gradual Decline
Grateful Dead

but not

The Great Graham is an egotistical SOB
Not So Gradual Decline

To find records where the letter 'Gra' apppears somewhere in the string use

Select * from File1 where FIELD1 like '%Gra%'

In the above example this should return only

Graham Smith
Gradual Decline
Grateful Dead
The Great Graham is an egotistical SOB
Not So Gradual Decline

Can be used in embedded SQL but should be noted that when using a
substitution variable (ie :Var1) that the variable should be padded with %
signs to fill out its length.

Eg if Var1 is 30,A

Select * from File1 where FIELD1 like :Var1

works as in the first example if
Var1 = 'Gra%%%%%%%%%%%%%%%%%%%%%%%%%%%'
but doesn't work if
Var1 = 'Gra%                          '
because then the SQL is looking for strings where the first 3 chars are
'Gra' the 4th char is anything and the last 26 chars are blank.


Regards
Graham
-----Original Message-----
From: Cyndi Bradberry [mailto:CyndiB@IHFA.ORG]
Sent: 02 January 2003 17:08
To: 'MIDRANGE-L@midrange. Com' (E-mail)
Subject: SQL Question


A co-worker has just asked what to use as a variable replacement character
in a select statement.  I can't find anything in any manual. Is there such a
thing ?

For example in dos you could type DEL *.* and it would delete everything.
What do you use in SQL to imitate the * in the previous dos statement ?

Thanks,

Cyndi B.
Boise, ID
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.