SQL DB2 SET SERVER SAMPLE
SQL DB2
-SET &BY1 = IF &SORT1 EQ '' THEN ''' ''' ELSE IF &SORT1 EQ 'CARBRAND' THEN 'CARBRAND , CARNAME ' ELSE IF &SORT1 EQ 'CARMODEL' THEN 'CARMODEL , CARNAME' ELSE &SORT1;
SELECT * FROM CAR
ORDER BY &BY1;
TABLE FILE SQLOUT
PRINT
&BY1
CARTYPE
END
THIS IS THE SAMPLE QUERY I AM USING IN MY REPORT WHERE I AM DOING THE SORTING AT THE SQL SIDE AND THEN PASSING THE VALUES TO THE WEBFOCUS SIDEFOR THE OUTPUT.
1)THE PROBLEM IS WHEN I AM GIVING THE VALUE FOR &SORT1 AS NULL THEN IT IS DISPLAYING ONLY CARTYPE BUT ALSO GIVING ME AN ADDITIONAL COLUMN TABPAGENO IS THERE ANY WAY TO AVOID IT FROM PRINTING THE ADDITIONAL COLUMN THAT IS TABPAGENO WHEN I GIVE &SORT1 AS NULL.
2) WHEN I AM GIVING THE VALUE FOR &SORT1 AS CARBRAND THEN I AM GETTING THE OUTPUT ERROR AS
UNRECOGNISED COLUMN :,
TABLE FILE SQLOUT
PRINT
CARBRAND , CARNAME
CARTYPE
END
IS THERE ANY WAY TO OVERLOOK THE COMMA IN WEBFOCUS OR REPLACE THE COMMA WITH SPACE. AND ALSO IS THERE ANY OTHER EASY METHOD OF SORTING THE DATA AT DATABASE SIDE AND DISPLAYING IT IN THE WEBFOCUS SIDE.
THANKS FOR UR TIME AND UR SUGGESTIONS ARE HIGHLY APPRECIATED
THANKS
URSMADDY

Turn nulls into blank. Remove the commas.


SQL DB2 SET SERVER SAMPLE
SQL DB2
-SET &BY1 = IF &SORT1 EQ '' THEN ' ' ELSE IF &SORT1 EQ 'CARBRAND' THEN 'CARBRAND CARNAME ' ELSE IF &SORT1 EQ 'CARMODEL' THEN 'CARMODEL CARNAME' ELSE &SORT1;
SELECT * FROM CAR
ORDER BY &BY1;
TABLE FILE SQLOUT
PRINT
&BY1
CARTYPE
END

THANKS DWF,
I TRIED UR SOLUTION BUT THEN ITS GIVING ME ERROR AT THE ORDER BY CLAUSE BECOZ ORDERBY FIELDNAMES SHOULD BE SEPERATED BY COMMAS AND IF I KEEP COMMAS THEN ITS WORKING FINE AT THE SQL SIDE BUT GIVING ME ERROR AT THE WEBFOCUS SIDE
THANKS
MADDY

Sorry, Maddy. Seems I forgot to pack a brain today. Maybe something like this:


SQL DB2 SET SERVER SAMPLE
SQL DB2

-SET &BY1 = IF &SORT1 EQ '' THEN ''' ''' ELSE IF &SORT1 EQ 'CARBRAND' THEN 'CARBRAND , CARNAME ' ELSE IF &SORT1 EQ 'CARMODEL' THEN 'CARMODEL , CARNAME' ELSE &SORT1;

-SET &BY2 = IF &SORT1 EQ '' THEN ' ' ELSE IF &SORT1 EQ 'CARBRAND' THEN 'BY CARBRAND BY CARNAME ' ELSE IF &SORT1 EQ 'CARMODEL' THEN 'BY CARMODEL BY CARNAME' ELSE &SORT1;


SELECT * FROM CAR
ORDER BY &BY1;
TABLE FILE SQLOUT
PRINT
&BY2
CARTYPE
END

THANKS DWF
ITS WORKING NOW
THANKS FOR UR HELP
MADDY