sql query and cfoutput

I have a database as follows

ID      acccode          title                               set
1           A                 Header                           T
2           A1               sub heading                    T
3           A1333         company1                        T
4           A1335         company2                        Z

i need to output

HEADER

SUB HEADING

      company 1

I do not output company 2 as its code is Z


so far:
SELECT ACCNT_CODE , ACCNT_NAME
FROM  dbo.dbo_SSRFACC
WHERE SUSPEND <> 'Z'
ORDER BY ACCNT_CODE

doesnt work because of the WHERE clause for some reason, its a varchar

I can use multiple sql statements and loop over the sub departments and companies, can you please help

LVL 1
Ken-dohAsked:
Who is Participating?
 
3ggCommented:
This should get you started in the right direction. I don't have a debugger here to check this SQL, but you should be able to do this with MS-SQL Server 2000, mySQL and PostgreSQL. Oracle changes the function to SUBSTR()

<cfquery name="q" datasource="yourdatasource">
   SELECT SUBSTRING(acccode,1,1) AS groupA,
                SUBSTRING(acccode,1,2) AS groupB,
                title
   FROM dbo.dbo_SSRFACC
   WHERE suspend <> 'Z'
   GROUP BY acccode, title
   ORDER BY groupA, groupB
</cfquery>

<cfoutput query="q" group="groupA">
 <p>#q.groupA#</p>
 <cfoutput group="groupB">
  <p>#q.groupB#</p>
  <cfoutput>
   #q.title#<br />
  </cfoutput>
 </cfoutput>
</cfoutput>
0
 
sandy12879Commented:
select  title from tablename  where set ='t'
0
 
hartCommented:
how is that u have the main heading, sub heading and companies under these in the same table...

if u want just the first three records then what sandy wrote is fine just change
select  title from tablename  where set ='t'

to

select  title from tablename  where set ='T'
set is a database key word i think ur fieldname is SUSPEND  so it would be suspend = 'T'

or if u want to show the heading first then the sub heading and then list all the companies that come under these sub heading

Q1. will the acccode  for a heading be always 'A'
Q2. will the accode for a subheading be always 'A1'

see i would have kept a dept / heading table [with headingid and heading]
a sub department / sub heading table [with subheadingid and subheading ]

and in the other table i would have stored data as
headingid
subheadingid
company

this way by using joins i would have got the heading, sub heading and companies in one query..

Regards
Hart

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.