Substitution variables was Oracle Hints

[Pietersz Mark x8938 <mpieters@CITIPOWER.COM.AU>]

Richard,

I changed
FROM [$_msTempTLeafTable] TL, PS_product_TBL J,PS_LEDGER_BUDG L,PSTREELEAF
LG,PSTREENODE TN [$_Sort_Type_Recname]

to
FROM PS_LEDGER_BUDG L, PS_product_TBL J,[$_msTempTLeafTable] TL,PSTREELEAF
LG,PSTREENODE TN [$_Sort_Type_Recname]

I had to have the tables in this order because I am using the oracle ordered
hint.

When I did this all the tables after PS_product_TBL J disappeared from the
'FROM' section, that is SQR did not see [$_msTempTLeafTable] TL,PSTREELEAF
LG,PSTREENODE TN [$_Sort_Type_Recname] so my select went totally up the
creek.
I had to concatenate all the table names into a single string variable and
then use

FROM [$TableNames] instead

I have spoken to a couple of other consultants in the meantime who have
confirmed this behaviour.

(We think it 'mazing)

Regards

Mark Pietersz


-----Original Message-----
From: richard.mitchell@GSA.GOV [mailto:richard.mitchell@GSA.GOV]
Sent: Tuesday, 4 April 2000 1:15
Subject: Re: Oracle Hints


Mark,

This may have been mentioned previously but I don't have the thread.

When I use substitution variables, I often get lost in the detail of what
is getting sent in the Select. What I do is add a capital X to the end of
the Select to see how SQR is putting it together. Usually I get ......where
LOCATION = :1 X    when I actually want .....where LOCATION = 'MY LOCATION'
X  which means I left the [  ] off the substitution variable again. I put
the [ ] in again and run the program with the X on the end until I see the
Select I want, then remove it to run the code.

Sure would be nice if SQR would give a 'debug' option to view the Select.
Hint, hint...

Hope this helps,

Rick



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This email message has been swept for the presence of computer viruses.

CitiPower Pty ACN 064 651 056
**********************************************************************