Using the FLEX application to pass variables to Teradata SQL

Database

Using the FLEX application to pass variables to Teradata SQL

I am attempting to use the FLEX application as a front end user interface to pass variables to Teradata SQL so only the desired records are returned. I am getting an error that I have been able to duplicate in SQL Assistant that indicates the variables are being passed without single quotes around them. Another complication is the value of one of the variabales is ALL, a reserved word for Teradata.

Below is the SQL we are using. The {?SOR} value is the value of the variable being passed.

(SELECT
(CASE WHEN 'ALL' IN ({?SOR}) THEN ALL_CD.SOR_CD
ELSE PART.SOR_CD END)
FROM
(SELECT sor1.SOR_CD FROM micap_nsn_av.actl_niin_sor_dm_ev sor1
GROUP BY 1) ALL_CD,
(SELECT sor2.SOR_CD FROM micap_nsn_av.actl_niin_sor_dm_ev sor2
WHERE sor2.SOR_CD LIKE {?SOR} || '%'
GROUP BY 1 ) PART
GROUP BY 1
)

When I replace the {?SOR} variables with a value of 'ALL' or 'O' in SQL Assistant it runs fine.

(SELECT
(CASE WHEN 'ALL' IN ('ALL') THEN ALL_CD.SOR_CD
ELSE PART.SOR_CD END)
FROM
(SELECT sor1.SOR_CD FROM micap_nsn_av.actl_niin_sor_dm_ev sor1
GROUP BY 1) ALL_CD,
(SELECT sor2.SOR_CD FROM micap_nsn_av.actl_niin_sor_dm_ev sor2
WHERE sor2.SOR_CD LIKE 'ALL' || '%'
GROUP BY 1 ) PART
GROUP BY 1
)

When I put it in the reporting environment I get errors I can duplicate in SLQ Assistant by removing the single quotes as below.

(SELECT
(CASE WHEN 'ALL' IN (ALL) THEN ALL_CD.SOR_CD
ELSE PART.SOR_CD END)
FROM
(SELECT sor1.SOR_CD FROM micap_nsn_av.actl_niin_sor_dm_ev sor1
GROUP BY 1) ALL_CD,
(SELECT sor2.SOR_CD FROM micap_nsn_av.actl_niin_sor_dm_ev sor2
WHERE sor2.SOR_CD LIKE 'ALL || '%'
GROUP BY 1 ) PART
GROUP BY 1
)

Any ideas on how to fix the problem? I have tried several things but no fixes yet.

Pat

1 REPLY
Junior Contributor

Re: Using the FLEX application to pass variables to Teradata SQL

Hi Pat,
you should be glad that it doesn't work, because that query is (how shall i put this? ahem...) totally stupid:
It's a CROSS JOIN between two potentially large answer sets, followed by a GROUP BY to get rid of the duplicates.
Check the Explain.

I don't know FLEX, but if {?SOR} is just replaced by the actual value you have to add the single quotes in the query:
WHERE sor2.SOR_CD LIKE '{?SOR}' || '%'

"WHEN 'ALL' IN ({?SOR}) "
Might {?SOR} be multiple values, comma delimited?
Then the LIKE part will never work.
If not then simply use
WHEN 'ALL' = ({?SOR})

Better rewrite it:
SELECT SOR_CD FROM micap_nsn_av.actl_niin_sor_dm_ev sor1
WHERE {?SOR} = 'ALL'
OR SOR_CD LIKE '{?SOR}' || '%'
GROUP BY 1

Btw, do you really need that GROUP BY?
Then you probably don't access the right table.

Dieter